Modify
the Cloned Report to Convert It to Meet the New Requirements
While we
certainly might have modified the copy of the FoodMart Sales report to
meet the requirements we have obtained for the new primary report, I wanted to
defer this final preparatory step to just before the creation of the
drillthrough target report. Keeping our work with the two reports together
will make it easier to understand their interrelationships, and how we
reference them to each other for passage of parameters, and so forth, as we
shall see.
Let's
return to the Reporting Services development environment, where we left
our new report, RS022_MDX_DRILLTHROUGH.rdl, and make the modifications
required to match the specifications we have received as part of our business
requirements gathering efforts.
1.
From within the
Solution Explorer, within the Reports folder, right-click RS022_MDX_DRILLTHROUGH.rdl.
2.
Select Open,
as shown in Illustration 24, from the context menu that appears.
Illustration 24: Select
Open from the Context Menu ...
RS022_MDX_DRILLTHROUGH
opens and appears
on the Layout tab of the Report Designer, as depicted in Illustration
25.
Illustration 25: RS022_MDX_DRILLTHROUGH
Appears in Report Designer - Layout Tab
3.
Click the Preview
tab to execute the report.
RS022_MDX_DRILLTHROUGH
executes briefly,
and then returns data. We see, atop the Preview tab, the Product
Family parameter selector, as shown (expanded) in Illustration 26.
Illustration 26: RS022_MDX_DRILLTHROUGH
Appears in Preview (Partial View) – with Parameter Selector Expanded
Each
of the three Product Family selections gives us a result set filtered
for its members. The Purchasing team has asked for a "consolidated view,"
where all products appear on a single report by Product Name, visually grouped
by Product Brand Name. Elimination of the parameter in place will be a
simple way to get started toward customizing the report to meet the new
specifications the consumers have relayed to us.
4.
Click the Layout
tab to return to the Layout view.
5.
Select Report
--> Report Parameters from the main menu atop the Report
Designer, as depicted in Illustration 27.
Illustration 27: Select
Report --> Report Parameters from the Main Menu
The Report
Parameters dialog, where we define parameters for the report, appears, as
shown in Illustration 28.
Illustration 28: The
Report Parameters Dialog
We
will get more exposure to this dialog later in our session, within the
drillthrough target report we create from scratch. Our objective at this point
is to remove the existing parameter, and to consolidate the Product Families
thereby.
6.
In the Parameters
list, on the left side of the dialog, click the single entry, ProductFamily,
to select it.
7.
Click the Remove
button underneath the Parameters list.
The ProductFamily
parameter is removed from the list.
8.
Click OK to
accept our removal of the parameter.
The
now empty Report Parameters dialog closes, and we are returned to the Layout
view of the report.
We now
need to modify our Dataset query to enable it to support the new
reporting requirements of the information consumers. We will do this from the Data
tab within the new report.
9.
Click the Data
tab within the Report Designer.
10.
Select ProductList
in the Dataset selector.
The ProductList
Dataset appears. Because this Dataset was created to support the
picklist behind the Parameter we deleted earlier, we can eliminate the Dataset,
as a matter of simple housekeeping, while we are "in the
neighborhood."
11.
Click the Delete
Selected Dataset button, as depicted in Illustration 29.
Illustration 29: Deleting
the ProductList Dataset
12.
Click Yes
on the warning dialog that appears next, as shown in Illustration 30.
Illustration 30: Click
Yes to Confirm Intentions ...
The no longer useful Dataset
is deleted, and we arrive at the remaining ProductData dataset, where we
will now modify the existing Dataset query, which appears as depicted in Illustration 31.
Illustration 31: The
Data Tab with Existing Dataset Query
13.
Replace the
existing query with the following MDX syntax:
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 32.
Illustration 32: The
Dataset Query Replacement on the Data Tab
14.
Click the Run
button on the toolbar to execute the query, as shown in Illustration 33.
Illustration 33: Executing
the New Dataset Query ...
The new Dataset
appears in the Results pane, below the query, as partially
depicted in Illustration 34.
Illustration 34: Partial
View of the Returned Dataset
» 
Comment and Contribute
