Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II - Page 4
June 27, 2008
Next, we will add a similar filter to the TimeMonth dataset. The steps are almost identical as those we took for the TimeYear dataset; the only material difference is that the ParameterLevel for the Month level within the Date dimension is identified by the number 4.
15. Select TimeMonth, within the Dataset selector on the Data tab.
16. Click the Edit Selected Dataset (...) button to the right of the Dataset selector, as we did for the TimeYear dataset earlier.
The Dataset dialog appears, once again.
17. Click the Filters tab on the Dataset dialog.
18. Click the Expression box in the top row of the table labeled Filters on the Filters tab, to activate its downward pointing selector arrow, as we did for the TimeYear dataset earlier.
19. Using the selector, select the =Fields!ParameterLevel.Value option, exactly as we did for the TimeYear dataset.
20. Ensure, once again, that an equals ( = ) sign appears in the Operator column, to the right of the Expression column, within the same row of the Filters table.
21. Type the following into the Value column, to the right of the Operator column, within the same row:
The Dataset dialog, along with our settings, appears this time as depicted in Illustration 8.
22. Click OK to accept our input, and to close the Dataset dialog.
Let's take a quick look at the picklists for the date-related parameters, to ascertain the effectiveness of our dataset filters, as well as to verify their satisfactory operation.
23. Click the Preview tab next, to execute the report, once again.
The report runs, and then displays the drop-down parameters as specified in the preceding steps, including the defaults requested.
24. Click the downward pointing arrow to the right of the Month parameter selector, to expose the parameter picklist.
The parameter picklists appears, displaying months as partially shown in Illustration 9.
As we can see, our filter has been effective in populating the parameter picklist with months only. (If we click the downward pointing arrow to the right of the Year parameter selector, we can see that there, too, the correct parameter level only is displayed.) But while our dataset filters appear to be effective, we note in the Month parameter picklist that the months displayed are not restricted to FY 2005. This indicates that the Month parameter picklist is not cascading appropriately.
As is probably obvious, we must perform some additional steps before the new Month parameter picklist cascades correctly. Moreover, even though the parameter picklists in general appear to function at the Preview tab level, we will need to perform the steps to make them actually work (to make the look back capability actually restrict the presentation to the number of look back months specified, etc.). These steps lie within the realm of the underlying dataset query, within which we will be working in the next section.
Before we do that, lets return to the report parameters dialog to conclude this section with a couple of cosmetic changes.
25. Click the Data tab to return to the Data view.
26. Click Report --> Report Parameters to re-open the Report Parameters dialog, as before.
27. Ensure selection of the TimeMonth parameter in the Parameters pane of the dialog.
28. Within the Prompt box, found within the Properties section (the third input box from the top) of the Report Parameters dialog, replace the existing prompt name (TimeYear) with the following:
29. Within the Label field selector, the third setting from the top within the Available values section of the dialog (about midway down and underneath the Properties section), select Parameter Caption as presented in Illustration 10.
The Report Parameters dialog, including our setting changes, appears as depicted in Illustration 11.
30. Select the TimeMonth parameter within the Parameters pane on the left side of the Report Parameters dialog.
31. Within the Prompt box, found within the Properties section of the Report Parameters dialog, once again, replace the existing prompt name (TimeMonth) with the following:
32. Within the Label field selector, the third setting from the top within the Available values section of the dialog, select Parameter Caption.
The Report Parameters dialog, including our setting changes, appears as shown in Illustration 12.
Finally, lets make some settings for the new LookBackSpan parameter. As we noted in Mastering OLAP Reports: Parameterizing Number of Look Back Periods with the MDX LastPeriods() Function, Part I, the LookBackSpan parameter will differ from the other two automatically created parameters, particularly in the fact that it will not reference a corresponding, automatically created dataset a situation we both described and explained in Part I. Because the parameter picklist will simply offer a range of numerals as selection options, we will reference those numerals through direct specification within the Report Parameters dialog (although other approaches exist to support such a parameter, such as using a manually created dataset, etc.)
33. Select the LookBackSpan parameter within the Parameters pane on the left side of the Report Parameters dialog.
34. Within the Prompt box, again within the Properties section of the Report Parameters dialog, replace the existing prompt name (LookBackSpan) with the following:
No.Months to Look Back:
35. Within the Available values section of the dialog, ensure that the Non-queried radio button is selected.
36. Populate the table that appears to the right of the Non-queried radio button as specified in Table 1 below.
Table 1: Label and Value Input for the Non-queried Available Values Table
The Report Parameters dialog, including our setting changes, appears as partially depicted in Illustration 13.
37. Click OK to accept our changes and to dismiss the Report Parameters dialog.
As we mentioned earlier, we must perform some additional steps before the new parameters actually work. These steps will need to be accomplished within the syntax of a couple of underlying dataset queries, within which we will be working in the next section.