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 Oct 20, 2005

MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX - Page 6

By William Pearson

Modify the Cloned Report to Convert It to Meet the New Requirements

While we certainly might have modified the copy of the FoodMart Sales report to meet the requirements we have obtained for the new primary report, I wanted to defer this final preparatory step to just before the creation of the drillthrough target report. Keeping our work with the two reports together will make it easier to understand their interrelationships, and how we reference them to each other for passage of parameters, and so forth, as we shall see.

Let's return to the Reporting Services development environment, where we left our new report, RS022_MDX_DRILLTHROUGH.rdl, and make the modifications required to match the specifications we have received as part of our business requirements gathering efforts.

1.  From within the Solution Explorer, within the Reports folder, right-click RS022_MDX_DRILLTHROUGH.rdl.

2.  Select Open, as shown in Illustration 24, from the context menu that appears.

Illustration 24: Select Open from the Context Menu ...

RS022_MDX_DRILLTHROUGH opens and appears on the Layout tab of the Report Designer, as depicted in Illustration 25.

Illustration 25: RS022_MDX_DRILLTHROUGH Appears in Report Designer - Layout Tab

3.  Click the Preview tab to execute the report.

RS022_MDX_DRILLTHROUGH executes briefly, and then returns data. We see, atop the Preview tab, the Product Family parameter selector, as shown (expanded) in Illustration 26.

Illustration 26: RS022_MDX_DRILLTHROUGH Appears in Preview (Partial View) – with Parameter Selector Expanded

Each of the three Product Family selections gives us a result set filtered for its members. The Purchasing team has asked for a "consolidated view," where all products appear on a single report by Product Name, visually grouped by Product Brand Name. Elimination of the parameter in place will be a simple way to get started toward customizing the report to meet the new specifications the consumers have relayed to us.

4.  Click the Layout tab to return to the Layout view.

5.  Select Report --> Report Parameters from the main menu atop the Report Designer, as depicted in Illustration 27.

Illustration 27: Select Report --> Report Parameters from the Main Menu

The Report Parameters dialog, where we define parameters for the report, appears, as shown in Illustration 28.

Illustration 28: The Report Parameters Dialog

We will get more exposure to this dialog later in our session, within the drillthrough target report we create from scratch. Our objective at this point is to remove the existing parameter, and to consolidate the Product Families thereby.

6.  In the Parameters list, on the left side of the dialog, click the single entry, ProductFamily, to select it.

7.  Click the Remove button underneath the Parameters list.

The ProductFamily parameter is removed from the list.

8.  Click OK to accept our removal of the parameter.

The now empty Report Parameters dialog closes, and we are returned to the Layout view of the report.

We now need to modify our Dataset query to enable it to support the new reporting requirements of the information consumers. We will do this from the Data tab within the new report.

9.  Click the Data tab within the Report Designer.

10.  Select ProductList in the Dataset selector.

The ProductList Dataset appears. Because this Dataset was created to support the picklist behind the Parameter we deleted earlier, we can eliminate the Dataset, as a matter of simple housekeeping, while we are "in the neighborhood."

11.  Click the Delete Selected Dataset button, as depicted in Illustration 29.

Illustration 29: Deleting the ProductList Dataset

12.  Click Yes on the warning dialog that appears next, as shown in Illustration 30.

Illustration 30: Click Yes to Confirm Intentions ...

The no longer useful Dataset is deleted, and we arrive at the remaining ProductData dataset, where we will now modify the existing Dataset query, which appears as depicted in Illustration 31.

Illustration 31: The Data Tab with Existing Dataset Query

13.  Replace the existing query with the following MDX syntax:

  {[Measures].[Store Sales]} ON COLUMNS, 
  {Descendants([Product].[All Products],[Product].[Product Name],LEAVES)} ON ROWS

The new Dataset query appears as shown in Illustration 32.

Illustration 32: The Dataset Query Replacement on the Data Tab

14.  Click the Run button on the toolbar to execute the query, as shown in Illustration 33.

Illustration 33: Executing the New Dataset Query ...

The new Dataset appears in the Results pane, below the query, as partially depicted in Illustration 34.

Illustration 34: Partial View of the Returned Dataset

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