We will
now create a new Dataset, based upon the MDX query used to define the Dataset
of our primary report RS022_MDX_DRILLTHROUGH. In effect, we will
use an identical query, with the DRILLTHROUGH statement added
above the SELECT statement. Because we will be passing
parameters to this report from the primary report, however, we will need to
place the ultimate query within a string, which, as we have seen in other OLAP
reporting articles in this series, complicates the construction of the query to
some extent.
First,
we will populate the Fields list with the SELECT query from the
primary report. Next, we will modify the query with the DRILLTHROUGH
statement, and prepare it to handle the passed parameters from the primary
report upon drillthrough action.
18.
Type (or cut
and paste) the following syntax into the Query pane of the Data tab:
SELECT
{[Measures].[Store Sales]} ON COLUMNS,
{Descendants([Product].[All Products],[Product].[Product Name],LEAVES)} ON ROWS
FROM
[SALES]
WHERE
([Time].[Year].[1997])
The
new Dataset query appears as shown in Illustration 63.
19.
Click the Run
(!) button on the toolbar to execute the query.
The new Dataset
appears in the Results pane, below the query, as partially
depicted in Illustration 64.
Illustration 64: Partial
View of the Returned Dataset
20.
Substitute the
following for the third line (the ON ROWS specification) of the existing
query.
{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good].
[Good Light Beer]} ON ROWS
The Dataset
query appears, with modifications enclosed in a red rectangle, as shown in Illustration
65.
Illustration 65: The Modified
Dataset Query
21.
Click the Run
(!) button on the toolbar, once again, to execute the query.
The new Dataset
appears in the Results pane, below the query, as depicted in Illustration
66.
Illustration 66:
Returned Dataset for the Modified Query
Our
purpose is to create a query that selects a single tuple on each axis, so that
we can use the DRILLTHROUGH statement in a "dummy" query, just
long enough to instantiate the Data Fields for the report before we
parameterize the query and convert it to a string. All we are doing here is
modifying the query to select a single, qualified Product Name in the ON
ROW specification. This single Product Name will be replaced by a
parameter placeholder, as we shall see, in subsequent steps.
22.
Add the DRILLTHROUGH
statement (just the keyword DRILLTHROUGH) to the query above SELECT,
as shown in Illustration 67.
Illustration 67: Add the
DRILLTHROUGH Statement above the SELECT Keyword
23.
Click the Run
(!) button on the toolbar, as before, to execute the query.
The new Dataset
appears in the Results pane, below the query, as partially depicted
in Illustration 68.
Illustration 68: Partial
Results of the DRILLTHROUGH Query
We
note that the fields that appear in the new Dataset are those that we
requested in our Drillthrough Options settings within the Cube Editor
for the Sales cube earlier. (While order of columns is rigidly dictated
by Analysis Services, this does not present an arrangement obstacle within Reporting
Services, as we shall see). We have thus achieved the Drillthrough
effect for a single Product Name, once again with the purpose of
populating the Fields list appropriately.
24.
Click the Refresh
Fields button atop the Data tab, to the immediate left of the Run
(!) button, as shown in Illustration 69.
Illustration 69: Refreshing
the Data Fields ...
The FoodMart_2000
Dataset Fields list is populated to reflect the returned Dataset,
and thus the fields specified within the Sales cube Drillthrough Options,
as depicted in Illustration 70.
Illustration 70: The
Populated Fields List
We have
populated the Fields list, and can next fit the query into string
format. This involves removing carriage return breaks and making the query a
single string. We are also ready to insert a parameterization "placeholder"
for the report parameter we will create next. Finally, also within the
new string, we will include the DRILLTHROUGH statement to leverage MDX
to provide, as part of the newly returned Dataset, the data underlying the
initial query, within the columns that we specified in Drillthrough Options
from the Cube Editor earlier.
25.
Replace the
query with the following exact syntax:
="DRILLTHROUGH SELECT { [Measures].[Store Sales]} ON COLUMNS, {[" &
Parameters!pX_ProductName.value & "]} ON ROWS FROM [Sales] WHERE
([Time].[Year].[1997]) "
NOTE: Do not insert carriage returns / "line breaks."
The query must remain as a single string, preceded by an equals ("=")
sign and enclosed in quotation marks.
The
new Dataset query appears as shown in Illustration 71.
Illustration 71: The
Data Tab Dataset Query Replacement
The query
refers, at this point, to a report parameter that has yet to be created;
the query is therefore not yet functional. We will add this parameter (pX_ProductName)
next. This report parameter will act as the mechanism that allows filtering
upon the specific Product whose Store Sales value we select for drillthrough
in the primary report.
26.
Select Report
--> Report Parameters from the main menu, as depicted in
Illustration 72.
Illustration 72: Adding
a New Report Parameter
The Report
Parameters dialog appears.
27.
Click the Add
button on the dialog.
28.
Type the
following into the Name box:
pX_ProductName
29.
Type the
following into the Prompt box:
Product Name:
30.
Remove the
checkmark from the Allow blank value checkbox.
31.
Ensure that,
in the Available values section, the radio button to the immediate left
of Non-queried is selected.
32.
Ensure that,
in the Default values section, the radio button to the immediate left of
None is selected.
The Report
Parameters dialog appears as shown in Illustration 73.
Illustration 73: The Report
Parameters Dialog for the Product Name Prompt
33.
Click OK
to accept our settings and to close the Report Parameters dialog.
We
return to the Data tab.
34.
Click the Preview
tab to attempt to execute the report in Preview mode.
The
report begins to run, and presents the prompt for the Report Parameter
we have created. This serves as a "pre-test" that the modified Dataset
query we have constructed is syntactically sound; recall that, because we are
forced to enclose the query string within parentheses, the capability to
execute the query with the standard Run (!) button is disabled.
» 
Comment and Contribute
