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

July 27, 2004

Our next action is to tie together the datasets of the cascading chain. We will designate ds_pX_Country as the "lead" dataset - it will not require modification, as we shall see. However, each of the remaining ds_pX datasets will require a modification, which we will accomplish in the next steps.

13.  Click the Data tab once again, as required.

14.  Select ds_pX_State from the dataset selector, as depicted in Illustration 31.

Click for larger image

Illustration 31: Select ds_pX_State from the Dataset Selector

The ds_pX_State dataset opens.

At this stage, we will need to focus upon precision in the modifications we make to our MDX query. In essence, we will be converting it to a string, which will pass parameters to MSAS. First, we will add the parameter reference, and then we will shape the query into a string.

15.  Remove the comment line at the top of the query.

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

Original Line:

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

Change to:

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

The modified dataset appears as shown in Illustration 32.


Illustration 32: Modified Dataset for ds_pX_State (Partial View)

17.  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 33.


Illustration 33: Make the Query a Single String

NOTE: It is critical to remember that the query must be joined together in a single string - it would stretch to a single, long line if the query view were opened wide enough to contain it. What we must avoid specifically is the presence of artificial line breaks / "carriage returns," created by the Enter key. In addition, absence of the equals ("=" sign at the beginning of the query, which notifies Reporting Services that a string containing an expression follows, or either or both of the opening and closing quotes, will result in failure when we next execute the query.

Once we take the above modification actions, the Run button is disabled. This is a bit cumbersome, as converting the query to a string results in our inability to actually see the query in action going forward, at least with regard to its populating the results pane underneath it. This is why precision is critical - we may not know we have an error until we run the query in the context of the full report. (A consolation, however, is that Reporting Services will inform us of the offending dataset(s) in the event of a failure, helping us to isolate and repair syntactical errors).

We will perform a similar process with the queries returning the ds_pX_City and ds_pX_Store datasets. But first, let's bind the ds_pX_State query to a report parameter, linking it, in the process, to the first member of the "cascading chain," ds_pX_Country.

18.  Select Report --> Report Parameters from the main menu, as depicted in Illustration 34.


Illustration 34: Select Report Parameters ...

The Report Parameters dialog appears.

19.  Click Add to begin a new report parameter.

A new parameter with default name appears.

20.  For the dialog boxes shown in Table 5, type the corresponding items (replacing defaults where required):

In this Dialog Box:

Type the following:

Name:

pX_Country

Prompt:

Country:

Data type:

String (default)

Available Values:

From query

Dataset:

ds_pX_Country

Value field:

Measures_Country_Qualified_Name

Label field:

Measures_Country_Report_Name

Default Values:

None (default)

Table 5: pX_ Country Parameter Details

The completed Report Parameters dialog appears as shown in Illustration 35.


Illustration 35: Completed Report Parameters Dialog ...

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

Having created the first of four report parameters, we will follow the same process with the three remaining parameters.

22.  Create the following report parameters:

  • pX_State
  • pX_City
  • pX_Store

using the corresponding details contained within Tables 6, 7 and 8 below.

In this Dialog Box:

Type the following:

Name:

pX_State

Prompt:

State:

Data type:

String (default)

Available Values:

From query

Dataset:

ds_pX_State

Value field:

Measures_State_Qualified_Name

Label field:

Measures_State_Report_Name

Default Values:

None (default)

Table 6: pX_ State Parameter Details

 

In this Dialog Box:

Type the following:

Name:

pX_City

Prompt:

City:

Data type:

String (default)

Available Values:

From query

Dataset:

ds_pX_City

Value field:

Measures_City_Qualified_Name

Label field:

Measures_City_Report_Name

Default Values:

None (default)

Table 7: pX_ City Parameter Details

 

In this Dialog Box:

Type the following:

Name:

pX_Store

Prompt:

Store:

Data type:

String (default)

Available Values:

From query

Dataset:

ds_pX_Store

Value field:

Measures_Store_Qualified_Name

Label field:

Measures_Store_Report_Name

Default Values:

None (default)

Table 8: pX_ Store Parameter Details







The Network for Technology Professionals

Search:

About Internet.com

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