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.
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
» 
Comment and Contribute
