Parameterization from Analysis Services - Cascading Picklists - Page 2

March 26, 2008

Practice

Practice: Modify Datasets in Reporting Services to Support Cascading Parameter Picklists

We noted in Support Parameterization from Analysis Services, in the section titled “Verification: Preview the Report and Inspect the Effectiveness of Our Solution,” that the parameter picklists we had put into place in the practice section did not cascade. An example of this behavior can be easily demonstrated by taking the following steps.

1.  From our current position in Layout view, click the Preview tab.

DBJ_OLAP_Report.rdl initializes, and the first prompt (based upon the pre-existing ProductCategory parameter), becomes enabled.

2.  Leave the Product Category prompt selection at its default of” Bikes, Components.”

3.  Click the downward pointing arrow on the right side of the Year selector.

4.  Select CY 2004 within the Year parameter picklist.

5.  Click the downward pointing arrow on the right side of the Quarter selector.

We notice at this stage that all four quarters appear in the Quarter selector, as depicted in Illustration 2.


Illustration 2: Evidence of Non-Cascading Behavior in the Report ...

The appearance of four quarters is a circumstance that is contradicted by the fact that CY 2004 is not a complete year in our cube – only the first three quarters contain data, so CY Q4 should not appear as a selection option. We can thus see clearly that we need to make some modifications to the report to support cascading picklists. We will accomplish this in the steps that follow.

6.  Click the Data tab to return to the datasets underlying the report.

Here we will make modifications to “chain” the report’s datasets, which we created in Support Parameterization from Analysis Services to support our time-related parameters. This might be handled several ways, but the use of simple WHERE expressions in the dataset queries supporting the Quarter and Year picklists will serve as a quick, intuitive approach.

7.  Click the downward selector arrow on the right side of the Dataset selector.

8.  Select AS_DateQtrParams_Support within the selector, as shown in Illustration 3.


Illustration 3: Select the AS_DateParamsQtrSupport Dataset

The Dataset opens.

9.  Underneath the syntax appearing within Query pane of the Query Designer, type the following WHERE clause:

<WHERE
   (STRTOMEMBER(@DateCalendarYear,
CONSTRAINED))

Here we are filtering, via the MDX WHERE clause, the data we are retrieving for Quarter picklist support to only those quarters that are properly included for the year we specify when prompted at runtime.

The Query pane, with our syntax addition, appears as depicted in Illustration 4.


Illustration 4: The Query, with Newly Added WHERE Clause ...

We also need to declare the new query parameter (seen as the @DateCalendarYear placeholder within the WHERE clause) by taking the next step.

10.  Click the Query Parameters button atop the Data pane (the second button to the left of the Execute Query (!) button).

The Query Parameters dialog opens.

11.  Click the leftmost box in the first row of the dialog, labeled Parameter, to activate it.

12.  Type the following into the box:

DateCalendarYear

13.  Click the next box to the right, labeled Dimension, to activate its selector button.

14.  Select Date within the selection list that appears, as shown in Illustration 5.


Illustration 5: Select the Date Dimension ...

15.  Click the next box to the right, labeled Hierarchy, to activate its selector button.

16.  Select Date.Calendar within the selection list that appears, as depicted in Illustration 6.


Illustration 6: Select the Date Dimension, Calendar Hierarchy ...

17.  Click the box to the far right, labeled Default, to activate its selector button.

18.  Using the “+” sign to the immediate left of the All Periods level in the hierarchical tree that appears, expand to the next level, Calendar Years.

19.  Click CY 2004 to highlight / select it, as shown in Illustration 7.


Illustration 7: Select CY 2004 as the Default Year ...

20.  Click OK to accept our selection and to close the selector.

The Query Parameters dialog appears, with our settings, as depicted in Illustration 8.


Illustration 8: The Query Parameters Dialog, with Our Input

21.  Click OK to accept input and to dismiss the dialog.

Selecting a default for the new Query Parameter allows us to execute the parameter immediately, within the MDX Query Builder, as the Default value is substituted for the parameter placeholder in the query syntax at runtime.

22.  Click the Execute Query (!) button to run the query against the Analysis Services data source, as shown in Illustration 9.


Illustration 9: Execute the New Query ...

The query runs, and the data is returned, as depicted in Illustration 10.


Illustration 10: The New Dataset Appears ...

As we can see, we have the workings of a good support dataset for the cascading Quarter parameter picklist – the captions and values correctly reflect the fact that our cube contains data within only three quarters for CY 2004. (This is why I chose CY 2004 as the default, to show the effect on the dataset, and therefore to demonstrate the effectiveness of its use to support the properly cascading parameter picklist.)

Let’s make a similar adjustment to the Month parameter picklist support query.

23.  Click the downward selector arrow on the right side of the Dataset selector, once again.

24.  Select AS_DateMoParams_Support within the selector, as shown in Illustration 11.


Illustration 11: Select the AS_DateParamsMoSupport Dataset

The Dataset opens.

25.  Underneath the syntax appearing within Query pane, type the following WHERE clause:

WHERE
   (STRTOMEMBER(@DateCalendarQuarterofYear,
CONSTRAINED))

We are, of course, filtering once again, via the MDX WHERE clause. This time, we are restricting the data we are retrieving for Month picklist support to only those months that are properly included within the quarter we specify when prompted at runtime. We also need, as we noted when adding a parameter to the Quarter parameter query earlier, to declare the new query parameter (seen as the @DateCalendarQuarterofYear placeholder within the WHERE clause), which we will accomplish by taking the next step.

26.  Click the Query Parameters button atop the Data pane, as we did earlier.

The Query Parameters dialog opens, once again.

27.  Click the leftmost box in the first row of the dialog (“Parameter”) to activate it, as before.

28.  Type the following into the box:

DateCalendarQuarterofYear

29.  Click the next box to the right (“Dimension”), as we did with the last dataset, to activate its selector button.

30.  Select Date within the selection list that appears, exactly as we did in the previous dataset Query Parameters dialog.

31.  Click the next box to the right (“Hierarchy”), to activate its selector button, as we did earlier.

32.  Select Date.Calendar within the selection list that appears, again precisely as we did in the previous dataset Query Parameters dialog.

33.  Click the box to the far right of the row (“Default”), as before, to activate its selector button.

34.  Using the “+” sign to the immediate left of the All Periods level in the hierarchical tree that appears, expand to the next level, Calendar Years.

35.  Expand Calendar Year CY 2004, exposing the two Semester level members H1 CY 2004 and H2 CY 2004.

36.  Expand Semester H1 CY 2004, exposing its Quarter level members Q1 CY 2004 and Q2 CY 2004.

37.  Click Q2 CY 2004 to highlight / select it, as depicted in Illustration 12.


Illustration 12: Select Q2 CY 2004 as the Default Quarter ...

38.  Click OK to accept our selection and to close the selector.

The Query Parameters dialog appears, with our settings, as shown in Illustration 13.


Illustration 13: The Query Parameters Dialog, with Our Input

39.  Click OK to accept input and to dismiss the dialog.

40.  Click the Execute Query (!) button, as we did earlier, to run the query against the Analysis Services data source.

The query runs, and the data is returned, as depicted in Illustration 14.


Illustration 14: The New Dataset Appears ...

We have added a support dataset for the cascading Month parameter picklist – the captions and values correctly reflect the months that belong to our selected default quarter, Q2 CY 2004.

We have extended the parameter support structures we created within Analysis Services in Support Parameterization from Analysis Services to enable cascading picklists. We are now ready to verify the effectiveness of our solution. We will verify that the date-related Report Parameters supported by objects within the Analysis Services layer of our integrated BI solution – perform as expected at runtime, providing cascading picklists as a part of operation.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers