Modify
the FoodMart Sales Report Structure to Meet Business Requirements
Let's
open the new clone of the FoodMart Sales report, and begin our
alterations. We will make the layout changes to eliminate the unwanted levels,
and then add the new Profit Margin measure. We will then focus upon the
conditional formatting filtering considerations that form the nucleus of our
session.
1.
Double-click AdHocCondFormat_Foodmart Sales.rdl, within the Reports folder
in Solution Explorer, to open it in Report Designer.
The
report opens, displaying the report on the Layout tab.
2.
Select the Preview
tab, to begin a quick check of connectivity and overall report operation.
3.
Select Food
in the parameter selector box that appears atop the Preview area, as
depicted in Illustration 16.
Illustration 16: Select
the Food Product Family in the Parameter Picklist
4.
Click the View
Report button to execute the clone report.
The
report runs and returns the data associated with the Food Product Family
that resides in the MSAS Sales cube. This indicates that our data source
is functional, and that the DataSet that underlies the report is operational.
5.
Drill down on Baking
Goods (click the "+" sign to its left), which appears near
the top left of the report, to expose its underlying groups.
6.
Drill down on Jams
and Jellies group that appears to the right of Baking Goods.
7.
Drill down on Jelly,
which appears to the right of Jams and Jellies.
At
this point we see the lowest level of the row axis, the Product Brand Name,
appear. We know that we need to alter the drill down capability to go one
level below, and instead of, the Product Brand Name, substituting Product
Name, to meet the requirements we have been given. A portion of our view
of the report at this stage appears in Illustration 17.
Illustration 17: The AdHocCondFormat_Foodmart Sales Report with
Drill-down Example
Our next
step involves swapping the Product Name level of the Product dimension
with the current lowest level of the report drill down, Product Brand Name.
Before we can make the field assignments on the Layout tab, we must
ascertain that the needed level is included in the DataSet.
8.
Click the Data
tab.
9.
Ensure that
the ProductData DataSet is selected.
The
DataSet definition (an MDX query) appears as shown in Illustration 18.
Illustration 18: ProductData
DataSet - Current Definition
In
examining the MDX behind the DataSet, we note a limiting factor in reaching our
objectives. The level to which the Descendants() function extends is
the Product Brand Name ([Product].[BrandName]) level, as I have
circled above. This means that we need to adjust the MDX to include the next
lower level, Product Name. Let's make the changes with the following
steps.
10.
Modify the second
line of the MDX query (the Row Axis definition) from its present form of:
{ Descendants([Product].[All Products], [Product].[Brand Name], LEAVES) } ON ROWS,
to the following:
{ Descendants([Product].[All Products], [Product].[Product Name], LEAVES) } ON ROWS,
(The
only change is the switch of [Product Name] for [Brand Name] in
one place, circled in Illustration 18 above.)
The
modified DataSet appears as shown in Illustration 19. (I have circled
the change).
Illustration 19: The AdHocCondFormat_Foodmart Sales Report with
Modification (Circled)
NOTE: For more information about MDX, see my series MDX Essentials
at Database Journal.
11.
Click the Run
(!) button atop the Data tab to execute the query.
The
query executes. An examination of the returned DataSet confirms the selection
of the level element we require to modify the report, Product Name, as
depicted in Illustration 20.
Illustration 20: The
Needed Field Appears in the Returned DataSet (Partial View)
We are
now ready to finish modifications to our report to remove Product hierarchy
drill-down capabilities, and to display the Product Subcategory and Product
Name levels, removing the others, as they are not useful within the scope
of the business requirements for the new report.
12.
Click the Layout
tab to switch to the Layout view.
13.
Widen the
fourth column (fourth from both left and right, and thus the middle column of
the report) enough to expose the full expression in the textbox, =Fields!Brand_Name.Value,
as shown in Illustration 21.
Illustration 21:
Expanded Column in Layout Tab (Partial View)
We must
make modifications in a couple of places, to exchange the Product Name
level with the Brand Name level, and to remove the remaining associated
drill-down capability.
14.
Click anywhere
within the Matrix data region to activate the gray column and row
headers.
15.
Right-click
the upper right corner of the Matrix data region.
16.
Select Properties
from the bottom of the context menu that appears, as shown in Illustration 22.
Illustration 22: Select
Properties from the Bottom of the Context Menu ...
The Matrix
Properties dialog - General tab for our matrix (named BrandSales)
appears, as depicted in Illustration 23.
Illustration 23: Matrix
Properties Dialog - General Tab
17.
Click the Groups
tab, to access the first area we need to modify, the group that currently
points to Brand Name.
18.
Click the BrandSales_Brand_Name
item that appears at the bottom of the Rows group list, as shown in Illustration
24.
Illustration 24: Select
BrandSales_Brand_Name Row Group ...
» 
Comment and Contribute
