Featured Database Articles

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.

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.

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:

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 ...