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 Nov 17, 2004

MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in 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 first make the layout changes to bring about the required drilldown, and then we will focus upon the filtering considerations that form the nucleus of our session.

1.  Double-click the FoodMart Sales - Filtered Dataset.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 17.

Illustration 17: 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 the report is retrieving is operating.

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, Product Brand Name, appear. We note no empty cells at this stage. 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 18.

Illustration 18: The FoodMart Sales - Filtered Dataset.rdl 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 19.

Illustration 19: 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 19 above.)

The modified DataSet appears as shown in Illustration 20. (I have circled the change).

Illustration 20: The FoodMart Sales - Filtered Dataset.rdl Report with Modification (Circled)

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

Illustration 21: The Needed Field Appears in the Returned DataSet (Partial View)

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