Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 20, 2004

MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports - Page 6

By William Pearson

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM