Lets pause from our preparation steps and take this opportunity to view some of the behind the scenes construction that occurs as we progress our report.
9. Click the Layout tab to transit to Layout view.
10. Select Report -> Report Parameters from the main menu.
We recall that a single Report Parameter for Product Category existed in the original report, before we began our preparation for the practice exercise. Yet we note, within the Report Parameters dialog that has appeared, the presence of a new Report Parameter, called DateCalendarYear. This Report Parameter was created automatically when we designated our new row within the Filter pane as a Parameter (via the enabling checkbox).
Another important observation we might make is that the From query setting within the Available values section of the dialog is active (the radio button is selected), and that the setting references a Dataset (and Value and Label fields therein) with the same name as the new Report Parameter - a Dataset, we will discover, that has also been automatically created. The purpose of this Dataset is to support the selection picklist for the new Report Parameter.
Finally, we note a couple of other settings that have been made for us: in the Prompt section (within the upper third of the dialog) of the Report Parameters dialog, the Multi-value setting is checked. While we might certainly leave the setting in place, we will disable it for the purposes of our practice session. In addition, in the Default values section (in the lower third of the dialog), we find that the radio button for Null has been preselected. For the time being, we will leave the latter preselected setting (Null) in place; the disablement of the Allow null value setting in the Prompt section effectively means that, even if the parameter selector defaults to blank at runtime, the information consumer will be forced to make a selection before he / she can proceed further with executing the report. This arrangement works well in many environments, particularly where default settings tend to be overlooked, at times, and the report run with unintended constraints. Obviously, settings should be adjusted to accommodate local requirements and realities, and, as we shall see in our practice session, we can accommodate intuitive defaults in conjunction with some modifications in this area.
11. Replace the default string that appears within the Prompt box, Date.Calendar Year, with the following, more user-friendly label:
12. Uncheck the checkbox labeled Multi-value.
13. In the Available Values section (the middle section of the Report Parameters dialog), in the bottom of the three settings, Label field, modify the setting from ParameterCaptionIndented to ParameterCaption.
The Report Parameters dialog appears, with our adjustments, as shown in Illustration 7.
Illustration 7: Our Settings for the Automatically Created Report Parameter
14. Click OK to accept settings, and to dismiss the Report Parameters dialog.
15. Click the Preview tab next, to execute the report.
The report runs, and then displays a drop-down parameter prompt labeled Calendar Year, to the right of the pre-existing Product Category prompt. The selector box for Calendar Year, by default, contains the placeholder <Select a Value>, and, when it is expanded, via the downward pointing arrow on its right side, appears as depicted in Illustration 8.
Illustration 8: The New Runtime Parameter Appears ...
16. Click the downward arrow to the right of the Calendar Year parameter selector to expose the picklist of Year choices.
17. Select CY 2003.
18. Click the View Report button in the upper right corner of the Preview tab.
The report runs again, and returns for the Date level groupings, for Calendar Year 2003, as expected.
We will now return to the Data tab, to examine the Dataset which has been automatically created, to populate the picklist in support of the new Report Parameter.
19. Click the Data tab.
20. Click the downward selector arrow on the right side of the Dataset selector.
21. Select the new DateCalendarYear dataset that appears, as shown in Illustration 9.
Illustration 9: Select the New Dataset ...
The Dataset loads, and presents the MDX syntax in Query view. This is the query that has been automatically created to generate the picklist to support the new DateCalendarYear Report Parameter (we saw the DateCalendarYear Dataset referenced within the Dataset selector, where columns from the new Dataset were assigned to the Value field and Label field selectors, within the Available values section of the dialog). As we have discussed, the Dataset was created automatically, after which the Report Parameter was itself created and connected to its underlying Dataset via the three settings we described (which were also established automatically by Reporting Services).
As we noted in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, the automatically generated MDX query creates additional fields, in addition to retrieving data from the cube; the purpose of the group of fields is to support the parameter picklist, from which information consumers make selections at runtime. These fields are:
All three fields are products of calculated members created via MDX syntax (using the WITH MEMBER keyword combination). As we discover within other articles of the series, there are multiple ways to approach picklist support (I very often construct members such as the above at the Analysis Services level, to afford central maintenance and reusability, among other, perhaps less obvious, advantages). This is simply the mechanism through which Reporting Services accomplishes support of the automatically generated Report Parameter. As we saw earlier, the Value and Label settings within the Report Parameter dialog reference the ParameterValue and ParameterCaption fields, respectively, within this Dataset.
NOTE: While we will encounter and discuss MDX functions, expressions, queries, and related subject matter throughout the MSSQL Reporting Services series, please refer to the articles of my MDX Essentials series, whose member articles are published monthly here at Database Journal, for detailed information about MDX.
Having examined various details about the objects that are created to support a parameterized filter, we will return to the Data tab to create the remaining two Date parameters that we will require for our practice session. While, as we saw above, we might simply move the hierarchical levels that we need to the Filter pane using the context menu for that matter, we might drag them as well - it is sometimes easier to work directly with the pane selectors. (We can, of course, still refer to the expanded Date.Calendar hierarchy within the Metadata pane as a visual reference, in this case.)
We will add two more parameterized filters directly in the Filter pane next.
22. Using the dropdown Dataset selector, as before, select ProductData, to return to the primary dataset and Filter pane.
23. Click the leftmost box (which contains the placeholder <Select dimension>, as before), within the Dimension column, in the bottom row of the Filter pane (this time, beneath the box containing Date, which we added in creating the first entry in the Filter pane earlier).
The dropdown selector is again enabled.
24. Select Date from the list that appears in the selector, once again.
25. Select Date.Calendar Quarter of Year within the Hierarchy box to the immediate right, using the selector that is built in.
26. Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of Equal, as we did within the first entry.
We will take a small detour, at this point, to confirm our understanding as to the timing of Report Parameters creation, based upon our activities within the Filter pane.
27. Leaving settings in the third row of the Filter pane, select Report ->Report Parameters ... from the main menu, once again.
The Report Parameters dialog opens, revealing only the two Report Parameters (the original ProductCategory parameter, along with the DateCalendarYear parameter we created in the immediately preceding steps) that we saw earlier,
28. Click OK to close the Report Parameters dialog.
29. Returning to the third row of the Filter pane, where we left off with our settings to establish a second new parameterized filter, place a checkmark in the checkbox that appears in the Parameters setting, once again.
30. Leaving the Filter pane once again, select Report ->Report Parameters ... from the main menu.
The Report Parameter dialog opens, this time revealing three Report Parameters. We see that the ProductCategory and DateCalendarYear parameters that existed before have been joined by the DateCalendarQuarterofYear parameter. It thus becomes evident that the act of checking the Parameters checkbox in the respective Filter pane row triggers the creation of the associated Report Parameter.
31. Click the DateCalendarQuarterofYear item within the Parameters list to select the new parameter within the Report Parameters dialog.
32. Replace the default string that appears within the Prompt box, Date.Calendar Quarter of Year, with the following, more user-friendly label:
33. Uncheck the checkbox for the Multi-value setting, as we did with the first Report Parameter.
34. In the Available Values section (the middle section of the Report Parameters dialog), in the bottom of the three settings, Label field, modify the setting from ParameterCaptionIndented to ParameterCaption, once again.
The Report Parameters dialog - with the DateCalendarQuarterofYear parameter selected - appears, with our adjustments, as depicted in Illustration 10.
Illustration 10: Settings for the New Calendar Quarter Report Parameter
35. Click OK to accept settings, and to dismiss the Report Parameters dialog.
Our completed entry appears in the third row of the Filter pane.
36. Click the downward selector arrow on the right side of the Dataset selector, once again.
An examination of the Dataset selection picklist reveals only the pre-existing ProductData and ProductList, together with the newly added DateCalendarYear, datasets. In this manner, we can verify that, although the creation of the Report Parameter, complete with internal references to a supporting dataset, is triggered by our placing a checkmark in the Parameters setting of the respective Filter pane row, the dataset itself is not fully instantiated until we leave the Data tab (as we did in our previous example, when we clicked the Layout tab to transit to the Layout view).
We will now create the final parameterized filter within the Filter pane.
37. In the bottom row of the Filter pane, click the leftmost box within the Dimension column, once again.
38. Select Date from the list that appears in the selector, as we did before.
39. Select Date.Month of Year within the Hierarchy box to the immediate right, using the selector that is built in.
40. Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of Equal, as we did within the first entry.
41. Place a checkmark in the checkbox that appears in the Parameters column, as we have done with the previous two row entries we have made in the Filter pane.
The Filter pane, with all four entries, appears as shown in Illustration 11.
Illustration 11: The Four Parameterized Filters in the Filter Pane
42. Select Report ->Report Parameters ... from the main menu, as before.
The Report Parameters dialog opens, this time revealing four Report Parameters.
43. Click the DateMonthofYear listing (the bottom of the four) within the Parameters list to select the new parameter within the Report Parameters dialog.
44. Replace the default string that appears within the Prompt box, Date.Month of Year, with the following, more user-friendly label:
45. Uncheck the checkbox for the Multi-value setting, as we did with the previous two parameters.
46. In the Available Values section (the middle section of the Report Parameters dialog), in the bottom of the three settings, Label field, modify the setting from ParameterCaptionIndented to ParameterCaption, once more.
The Report Parameters dialog - with the DateMonthofYear parameter selected - appears, with our adjustments, as depicted in Illustration 12.
Illustration 12: Settings for the New Month Report Parameter
47. Click OK to accept settings, and to dismiss the Report Parameters dialog.
48. Click the downward selector arrow on the right side of the Dataset selector, once again.
An examination of the Dataset selection picklist reveals only the ProductData, ProductList and DateCalendarYear Datasets, as we left them earlier.
49. Click the Layout tab, once again, to transit away from the Data tab.
50. Click the Data tab, once more, to return immediately.
51. Click the downward selector arrow on the right side of the Dataset selector, one last time.
Based upon our earlier discussion, we see the behavior we have expected. All five Datasets, including the originally existing two Datasets and the three we have added via the enactment of parameterized filters within the Filter pane (and subsequent departure from the Data tab), now appear, as depicted in Illustration 13.
Illustration 13: All Datasets Appear as Expected ...
We have successfully added three rudimentary parameters, based upon filters we added within the Filter pane of the primary Dataset underlying our report. The addition of the parameters within the graphical interface, as we have verified, triggered the automatic creation of corresponding Report Parameters. Supporting datasets were also created, as soon as we moved away from the Data tab. The timing of the automatic creation of objects can thus be understood; the sequencing becomes very important in the event that we need to modify or remove any of the objects after they are initially created and aligned.
NOTE: As we have noted previously, although a Report Parameter and its underlying Dataset are created automatically when we enable parameterization within the Filter pane of the MDX Query Builder (Design Mode), the removal of the Parameter checkmark within the associated row of the Filter pane, or even the deletion of the entire corresponding filter row, will not produce an opposite effect. The Report Parameter and the dataset will remain until they are manually removed. Moreover, the disablement of parameterization within the Filter pane, followed by re-enablement and / or recreation of the Filter pane entry will, unless we intervene before we re-enable / recreate the parameter, result in the creation of an additional Report Parameter.
Order is somewhat important in preventing confusion: for example, when deleting, the Report Parameter needs to be deleted first, with the underlying dataset (to which it refers) being deleted next, before creating a new Report Parameter even of the same name within the Filter pane. A Report Parameter can, of course, be repointed, but, unless we manually intervene, will likely be left pointing to an incorrect, remaining dataset if the dataset to which it was initially married was, for some reason, deleted from under it.
It is important to remember that changing a name can also have consequences from the perspective of alignment, and that if one side of the equation is modified, the other side must also be modified. Changing the Prompt label has no adverse effect, as we have seen multiple times within the steps of our practice exercise.
Lets perform a quick test to see that all is operating as expected in our sample report, before venturing into the practice session that follows, where we eliminate the All Periods selection within the date-related picklists. In the meantime, we will be setting up the environment for the second half of our article, where we will establish intuitive run-time defaults within our newly added date parameters.
52. Click the Preview tab.
DBJ_OLAP_Report.rdl initializes, and the first prompt (based upon the pre-existing ProductCategory parameter), becomes enabled.
53. Leave the Product Category prompt selection at its default of Bikes, Components.
54. Click the downward pointing arrow on the right side of the Calendar Year selector.
55. Select CY 2003 within the Calendar Year parameter picklist, as shown in Illustration 14.
Illustration 14: Select CY 2003 within the Calendar Year Parameter Picklist
We notice the All Periods selection that appears atop each of the daterelated picklists we have created. This is the All option that we will eliminate within the practice session, as one of our objectives in meeting the expressed needs of our client colleagues.
Once we make a choice within the Calendar Year dropdown selector, the next parameter selector, Calendar Quarter, becomes enabled.
56. Select CY Q3 from the Calendar Quarter picklist.
57. Select the month of August in the Calendar Month parameter picklist.
58. Click the View Report button.
The report executes quickly and returns the data for the selections we have made within our parameter picklists, in a manner similar to that depicted in Illustration 15.
Illustration 15: The Sample Report Operates as Expected with New Date Parameters
Having obtained assurance that the sample report operates as expected, we are ready to proceed with its use in our practice session, which follows below.