dcsimg

MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts - Page 9

July 27, 2004

Next, we will complete the procedure we performed in the ds_pX_State dataset earlier, and tie together the remaining datasets for City and Store.

23.  Click OK to close the Report Parameters dialog, if it remains open.

24.  Once the above report parameters have been created, click the Data tab once again, as required.

25.  Select ds_pX_City from the Dataset selector.

The ds_pX_City dataset opens. Here we will again be converting the existing MDX query to a string, which will pass parameters via Reporting Services to MSAS. As we did with the ds_pX_State dataset, we will add the parameter reference, and then we will shape the query into a string.

26.  Eliminate the comment line atop the existing query.

27.  In the query, change the ON AXIS(1) - the ON ROWS line - as follows:

Original Line:

{[Store].[Store City].Members} ON AXIS(1)

Change to:

{DESCENDANTS(
  {" & Parameters!pX_State.Value & " }, 
  [Store].[Store City])} on AXIS(1)

The modified dataset appears as shown in Illustration 36.


Illustration 36: Modified Dataset for ds_pX_City

28.  Pull the query together into a single string, adding a =" (an equals sign and open double quotes combination) to the beginning, and a " (close double quotes symbol), as shown in Illustration 37.


Illustration 37: Make the Query a Single String

The presence of the Parameters!pX_City.Value reference binds our modified query to its respective parameter. Keeping in mind the need for the query to be joined together in a single string, we will continue to the modification of the ds_pX_Store dataset.

29.  Select ds_pX_Store from the Dataset selector.

The ds_pX_Store dataset opens. Here we will again be converting the MDX query to a string, which will pass parameters via Reporting Services to MSAS. As we did with the ds_pX_State and ds_pX_City datasets, we will add the parameter reference, and then form the query into a string.

30.  Eliminate the comment line atop the query, once again.

31.  In the query, change the ON AXIS(1) - the ON ROWS line - as follows:

Original Line:

{[Store].[Store Name].Members} ON AXIS(1)

Change to:

{DESCENDANTS({" & Parameters!pX_City.Value & " }, [Store].[Store Name])} on AXIS(1)

32.  Pull the query together into a single string, adding a =" (an equals sign and open double quotes combination) to the beginning, and a " (close double quotes symbol).

The modified dataset appears as shown in Illustration 38.


Illustration 38: Modified Dataset for ds_pX_Store

This action completes the binding of all of our queries to their respective report parameters. We are now ready to verify operation of the cascading parameters we have constructed.

Verification and Operation








The Network for Technology Professionals

Search:

About Internet.com

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