In Part 1 of this three-part article, we kicked off an examination of parameterization in Reporting Services in general, with a concentration on reports written to extract data from an Analysis Services data source. We opened and prepared a sample Report Server project in the Business Intelligence Development Studio, and positioned ourselves to add an OLAP report sample, from which to begin our introduction to parameterization within an Analysis Services report.
Add a Copy of the Sales Reason Comparisons Report to the Project
We will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons OLAP report, which we will open in the SQL Server 2008 / 2008 R2 Business Intelligence Development Studio and use for our practice exercise within the project we have opened above. (As we have noted earlier, no OLAP reports exist within the report samples set for Reporting Services 2008 R2 as of this writing.). We will download the associated .rdl file and then make a copy of that file, with which we will work within the practice session.
Creating a “clone” of the report means we can make changes to select contents (perhaps as a part of later exploration with our independent solution), while retaining the original sample in a pristine state for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, as a part of learning more about Reporting Services (particularly an OLAP report using an Analysis Services data source),and other components of the integrated Microsoft business intelligence solution in general.
To obtain a copy of the Reporting Services 2005 Sales Reason Comparisons OLAP report, take the following steps:
1. Access the .rdl file for the Sales Reason Comparisons sample report by clicking the following link:
Sales Reason Comparisons starter report
2. Download the .rdl file (or open it, and copy the code within).
NOTE: If you download the file ("Save Target As ..." when you right-click the link above), you can simply add the file to the Solution Explorer of the Report Designer directly, or copy and paste the code in the file to a .txt file, then save it as an .rdl file (which you can then add into the Solution Explorer) by taking the following steps:
3. Click the PC Start button, once again.
4. Navigate to, and click, Notepad, as appropriate to open a blank Notepad.
5. Completely cut and paste the code from the web to the blank Notepad.
6. In Notepad, select File --- Save As …, as depicted in Illustration 11.
Illustration 11: Select Save As … in Notebook
7. In the Save As dialog that appears next, navigate to a convenient location within which to save the report file.
8. Type the following into the File name box:
Sales Reason Comparisons.rdl
9. In the Save as type box just beneath the File name box, select All Files.
The Save As dialog appears, with our input, as shown in Illustration 12.
Illustration 12: The Save As Dialog with Our Input
10. Click the Save button to save the file as Sales Reason Comparisons.rdl, and to dismiss the dialog.
We are now positioned to add the new OLAP report sample to our Report Server project.
11. Return to the SQL Server Business Intelligence Development Studio.
From within the open Adventure Works 2008 Sample Reports project, once again, take the following steps:
12. Right-click the Reports folder underneath the Shared Data Sources folder, in the Solution Explorer.
13. Select Add --- Existing Item … from the cascading context menus that appear, as depicted in Illustration 13.
Illustration 13: Adding the Report to the Project …
The Add Existing Item dialog appears.
14. Navigate to the location to which you saved the Sales Reason Comparisons.rdl file above.
The Add Existing Item dialog, having been pointed to the folder on my machine which contains the Sales Reason Comparisons report file we seek, appears as shown in Illustration 14.
Illustration 14: Navigating to the Recently Created Sales Reason Comparisons Report.rdl File
15. Select the new Sales Reason Comparisons report file by clicking it.
16. Click Add on the dialog box to add the new report to the Adventure Works 2008 Reports Sample Reports project.
The Sales Reason Comparisons report appears in the Reports folder, within the Adventure Works 2008 Reports Sample Reports project tree in the Solution Explorer, as depicted in Illustration 15.
Illustration 15: The New Report Appears in Solution Explorer – Report Folder
A few adjustments remain to render the report fully functional within our current project.
17. Right-click Sales Reason Comparisons.rdl within the Solution Explorer.
18. Select Open from the context menu that appears, as shown in Illustration 16.
Illustration 16: Opening the New Report …
Sales Reason Comparisons.rdl opens on the Design tab, and appears as depicted in Illustration 17.
Illustration 17: Our Report Opens on the Design Tab …
Let’s align the report’s data source next (it will initially be pointing to the data source it had when it was created).
19. Expand the Data Sources folder in the Report Data pane, to the left of the report canvas on the Design tab, by clicking the “+” sign immediately to its left.
20. Right-click the existing Data Source in thefolder (named AdventureWorks AS).
21. Select Data Source Properties from the context menu that appears, as shown in Illustration 18.
Illustration 18: Accessing Data Source Properties …
The Data Source Properties dialog appears.
22. Change the entry in the Name box atop the dialog to the following:
23. Using the selector for the box labeled Use shared data source reference (in the lower half of the dialog), replace the preexisting setting of AdventureworksAS with AdventureWorks2008AS, as depicted in Illustration 19.
Illustration 19: Re-point the Data Source to the Project’s Shared Analysis Services Source
24. The Data Source Properties dialogappears, with our modifications, as shown in Illustration 20.
Illustration 20: The Data Source Properties Dialog with Our Changes
25. Click OK to accept the modification, and to dismiss the Data Source Properties dialog.
Let’s preview the report, so as to get a feel for its general operation prior to our work in the Practice session that follows.
26. Click the Preview tab to execute Sales Reason Comparisons.rdl.
Execution begins (the report initially executes with the default parameter setting). Once executed, the report appears as depicted in Illustration 21.
Illustration 21: The Report Appears with Default Parameter Selection
27. Click the Design tab.
28. Select File --- Save All from the main menu, to save our work, as shown in Illustration 22.
Illustration 22: Saving Our Work …
We now have a clone OLAP report file within our Reporting Services 2008 Project, with which we can proceed in the next section to make modifications for our subsequent practice session.
Preparation: Modify the OLAP Report for Use within Our Practice Session
We will next make a few modifications to prepare the report for our practice session. Our objective will be to begin the session with a simple OLAP report that contains no Parameters, and which is fitted with a simple filter. Let’s rejoin our report on the Design tab, where we left it in the immediately preceding section, and make the necessary settings to place it into a state upon which we can commence our practice steps.
We will start in the Report Data pane of the Report Designer (it appears by default to the left of the design canvas, as we noted earlier), where we will remove a handful of components that we do not need for our practice session. The components of the Report Data pane with which we are concerned at present are labeled in Illustration 23.
Illustration 23: The Report Data Pane of the Report Designer
1. Within the Report Data pane, expand the Datasets folder by clicking the “+” sign to its immediate left.
2. Right-click the ProductList dataset.
3. Select Delete from the context menu that appears, as depicted in Illustration 24.
Illustration 24: Delete the ProductList Dataset …
4. Click OK on the Microsoft Report Designer warning message dialog that appears next, as shown in Illustration 25.
Illustration 25: Confirm Intention to Delete …
The ProductList dataset is eliminated, leaving ProductData the sole dataset in the Report Data pane.
5. Right-click the ProductDatadataset.
6. Select Query… from the context menu that appears, as depicted in Illustration 26.
Illustration 26: Select Query … on the Context Menu
The Query Designer appears next. The sections of the Query Designer (with most panes empty, for clarity) appear as labeled in Illustration 27.
Illustration 27: The Sections of the Query Designer (Most Panes Empty)
7. Within the Query Designer, right-click the column heading for Internet Total Product Cost on the Data tab.
8. Select Delete Internet Total Product Cost from the context menu that appears, as shown in Illustration 28.
Illustration 28: Deleting an Unwanted Measure from the Dataset
9. Within the Calculated Members pane, right-click the sole Calculated Member, Profit.
10. Click Delete on the context menu that appears, as depicted in Illustration 29.
Illustration 28: Deleting the Calculated Member
11. Confirm the deletion by clicking OK on the message box that appears next.
12. Right-click the single entry in the Filter pane (directly atop the Data pane of the Query Designer).
13. Click Delete to eliminate the existing Product Category Filter from the Filter pane, as shown in Illustration 30.
Illustration 30: Deleting the Product Category Entry from the Filter Pane
We will make an addition to the Dataset next.
14. Within the Metadata pane, expand the Date dimension, by clicking the “+” sign to its immediate left.
15. Expand the Fiscal folder that appears within the Date dimension.
16. Expand the Fiscal hierarchy (Date.Fiscal) within the Fiscal folder.
17. Drag the Month level into the Data pane, dropping it to the left of the Sales Reason column, as depicted in Illustration 31.
Illustration 31: Adding Months to the Dataset
The new Month column appears, as desired.
We will now add in a simple filter, to make our report a bit more manageable for the practice session.
18. Click and drag the Fiscal hierarchy (again, within the Date dimension) into the leftmost box (currently containing the phrase “<Select dimension>”), of the bottom row of the Filter pane, as shown in Illustration 32.
Illustration 32: Adding the Date.Fiscal Hierarchy to the Filter Pane
19. Click the Filter Expression box within the same row of the Filter pane to enable the selector.
20. Click the downward arrow of the Filter Expression selector to expose the selection tree.
21. Expand All Periods by clicking the “+” sign to its immediate left.
22. Select FY 2008 and FY 2009 by checking the boxes to the left of each, as depicted in Illustration 33.
Illustration 33: Select FY 2008 and FY 2009 as the Filter Expression
23. Click OK to accept our settings, and to close the selector.
The newly added row in the Filter pane appears, as shown in Illustration 34.
Illustration 34: Our New Entry in the Filter Pane
24. Click the OK buttonon the Query Designer to accept our settings, and to close the selector.
Having made the necessary changes within the dataset, we are ready to conclude our preparatory modifications to the report file at our current position within the Design tab.
25. On theDesign tab, within the Tablix Data Region, select the value appearing underneath the Internet Total column heading.
26. Right-click the value in the text box (the value appears as [Sum(Internet_Total_Product_Cost)]).
27. Select Delete Columns from the context menu that appears,as presented in Illustration 35.
Illustration 35: Delete Column Containing Internet Total Product Cost
The right-most of the three columns disappears.
28. In the Column Groups section (in the lower half of the Design tab, underneath the report canvas) right-click
29. Select Group Properties… from the context menu that appears, as shown in Illustration 36.
Illustration 36: Editing the Matrix1_Sales_Territory_Group Column Group …
The Group Properties dialog appears, defaulted to its General page.
30.Atop the General page, change the existing Name to the following:
31.In the Group on box within the Group expressions section of the dialog, select [Month],replacing [Sales_Territory_Group] within the selector, as depicted in Illustration 37.
Illustration 37: Replacing the Existing Group Expression …
32. Click OK, to accept our modification, and to close the Group Properties dialog.
33. Once again on theDesigntab, within the Tablix Data Region, right-click the value appearing underneath the Internet Orders column heading (the leftmost of the two remaining value cells).
34. Select Text Box Properties on the context menu that appears, as shown in Illustration 38.
Illustration 38: Modifying Properties for the Count Value …
35. On the Textbox Properties dialog that next appears, select the Number page from the list on the left pane of the page.
36. Replace the existing Custom format code setting (in the right half of the Textbox Properties dialog – Number page) from “C0” to the following string:
Here we are simply changing the existing format to one more appropriate for a count value. This Custom format code now appears on the Number page of the Textbox Properties dialog as depicted in Illustration 39.
Illustration 39: Replacing the Existing Custom Format Code …
37. Click OK to accept our modifications, and to dismiss the Text Box Properties dialog.
We next need to change the column heading associated with the Group we modified earlier, so that it reflects Months,versus the previous grouping criteria of Sales Territory Group.
38. Right-click the text box containing the column label (immediately above the two measure values’ column labels) in the Design tab (the text box currently contains the expression [Sales_Territory_Group] ).
39. Select Expression… from the context menu that appears, as shown in Illustration 40.
Illustration 40: Modifying the Primary Column Label
The Expression Editor opens.
40. Replace the expression in the upper portion of the Editor with the following:
Recall that the intent is to label the column to reflect the nature of the Group we have put in place: Months instead of Sales Territory Group.
The Expression Editor appears, with our modification, as depicted in Illustration 41.
Illustration 41: The Expression Editor with Our Substitution in Place
41. Click OK to accept our modification, and to dismiss the Expression Editor.
42. Click the column heading for which we have just changed the expression, if necessary, simply to select the textbox.
43. Change the Font Size, in the Format toolbar atop the design environment, from 12pt to 10pt, to make the presentation more compact, as shown in Illustration 42.
Illustration 42: Change the Font to 10pt …
All that remains to complete our preparatory steps is to remove the Report Parameter that remains. Recall that one of our earlier steps was to remove the Product entry from the Filter pane (the sample report from which our report was cloned came equipped with a parameterized Product filter). This entry had been established with the Parameter checkbox selected, a common way to create a parameterized filter, as we shall see. Because parameterization was selected for the Product filter, it follows that a Report Parameter might well have beencreated at the time the entry was made. We will now remove the remaining Report Parameter.
44. Within the Report Data pane, once again, expand the Parametersfolder by clicking the “+” sign to its immediate left.
The single parameter, ProductCategory, appears within the expanded folder.
45. Right-click the ProductCategory Parameter.
46. Click Delete from the context menu that appears, as depicted in Illustration 43.
Illustration 43: Removing the Report Parameter …
47. Click OK on the Report Designer confirmation dialog that next appears, asking if we wish to delete the report parameter, as shown in Illustration 44.
Illustration 44: Confirm Deletion of the Report Parameter …
The ProductCategory parameter disappears from within the Report Data pane.
We will execute the report to ascertain that our modifications are complete, and that we have a working report for the practice session that follows.
48. Click the Preview tab, as depicted in Illustration 45.
Illustration 45: Click the Preview Tab to Execute the Report
The report generates, and then displays. The modified report appears as partially presented in Illustration 46.
Illustration 46: The Modified Report (Partial View)
Our report is now ready for the practice session, which we will begin in Part 3.