Modify
the Foodmart Sales Report Layout to Fit Our Needs
The FoodMart
Sales report that we have cloned as Multi-Layer_Calculations.rdl contains
a prompt to allow users to filter by Product Family. We will make
general layout changes in the report, while also eliminating the existing prompt,
before adding the calculations that form the core of our focus in this lesson.
But first, we will need to "redirect" the report to our new cube
clone, as it is currently "pointed" to the Sales cube in the
original FoodMart 2000 Analysis Services sample database.
1.
Within the RS018 project tree in the Solution
Explorer, double-click
the new Multi-Layer_Calculations
report, to open
it.
The
report opens within the Report Designer, and the Layout View
appears, as depicted in Illustration 26
2.
Click the
Data tab.
3.
In the
Dataset selector atop the Data tab, (which is now occupied by
ProductData, a Dataset created by a simple MDX query to
support the sample FoodMart Sales report), select ProductList.
This
exposes the simple MDX query that supports the parameter picklist.
4.
Click the
Delete Selected Dataset button, shown circled in Illustration 27.
Illustration 27:
Deleting the ProductList Picklist Query ...
5.
Click Yes
when the message box appears asking of our certainty.
The Dataset
definition is deleted, leaving the ProductData query in its place.
6.
Modify the FROM
statement in the MDX query that appears within the ProductData Dataset,
substituting [Percent of Total] for Sales (the original cube).
The
modified MDX query appears on the Data tab, as shown in Illustration 28.
Illustration 28: Modified
MDX Query - Pointed to New Cube Clone
7.
Click the Run
button (marked "!" atop the Data tab).
The Data
pane below the query is populated, indicating that the Data set is
functional, and pointed to the Percent of Total cube.
8.
Replace the
existing query with the following:
SELECT
{ [Measures].[Store Sales], [Measures].[Percent Total Sales] } ON COLUMNS,
{ Descendants([Store].[USA], [Store].[Store Name], LEAVES) } ON ROWS,
{Time.[Year].[1997]} ON PAGES
FROM
[Percent of Total]
The
new MDX query appears on the Data tab, as presented in Illustration 29.
Illustration 29: The
Modified Query on the Data Tab
9.
Click the Run
button, once again.
The Data
pane below the query is populated, again indicating that the Dataset
is functional.
10.
Click the Refresh
Fields button (shown circled in Illustration 30) to refresh the Data
Fields in the report.
Illustration 30: Refresh
Fields in the Report ...
Now we
are ready to alter the report, to simplify it for the exercises ahead.
11.
Click the Layout
tab to get to the report layout.
We
will streamline the report at this point to eliminate distractions, before we
focus on generating Percent of Total values based upon the calculated
member we have added to the underlying cube. We will also create an
independent calculated field to generate the same value independently
within Reporting Services.
We
will first remove the Product Family parameter from the report, and then
eliminate groupings to render a simple, single level report, from which we can
easily focus upon the calculations with which we are concerned in our example.
12.
Select Report
--> Report Parameters from the main menu atop the Report
Designer, as depicted in Illustration 31.
Illustration 31: Select
Report --> Report Parameters from the Main Menu
The Report
Parameters dialog, where we define parameters for the report, appears, as
shown in Illustration 32.
Illustration 32: The
Report Parameters Dialog
Our
objective at this point is to remove the existing parameter, and then to
perform a couple of additional "eliminations" to simplify the report.
13.
In the Parameters
list, on the left side of the dialog, click the single entry, ProductFamily,
to select it.
14.
Click the Remove
button underneath the Parameters list.
The ProductFamily
parameter is removed from the list.
15.
Click OK to
accept removal of the parameter.
The
now empty Report Parameters dialog closes, and we are returned to the Layout
view of the report. We must now delete a reference to the parameter we have
removed, which we can access via the Properties dialog for the matrix.
16.
Click at some
point within the title textbox of the report (containing the label Foodmart
Sales), to make the row and column headers of the matrix data region
visible.
17.
Right-click
the upper left corner of the matrix. (If the headers disappear as you touch
them with the cursor, you should still see a faint outline of the matrix.)
18.
Select Properties
from the context menu that appears, as depicted in Illustration 33.
Illustration 33: Accessing
the Matrix Properties
The Matrix
Properties dialog opens, defaulted to the General tab.
19.
Click the Filters
tab.
20.
Click the Value
field of the single occupied row to select it.
21.
Click the Delete
button to delete the reference to the parameter, as indicated in Illustration 34.
Illustration 34: Select
and Delete the Parameter Reference
The remaining
reference to the now-deleted parameter is itself deleted. All that remains is a
bit of "level extraction," and rearrangement. We recall that the
consumers have told us that the
row axis needs to display USA Stores. Moreover, they have declared that
the custom report will no longer require drilldown features, as it will be a
fixed report whose purpose in life is as a limited analysis tool.
The targeted "extractions,"
which will leave us with a good starting point for customizing the report to
the new measures, are depicted in Illustration 35.
Illustration 35:
Targeted Levels for Removal in the Customized Report
22.
From our current
position within the Matrix Properties dialog, click the Groups tab.
Four groups appear in
the Rows list box, and two added groups appear in the Columns
list box. The groups appear, with those targeted for elimination circled, as
shown in Illustration 36.
Illustration 36:
Existing Groups in the Clone Report
» 
Comment and Contribute
