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

MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives - Page 6

By William Pearson

Modify the Foodmart Sales Report Layout to Fit Our Needs

The FoodMart Sales report that we have cloned as Multi-Layer_Calculations.rdl contains a prompt to allow users to filter by Product Family. We will make general layout changes in the report, while also eliminating the existing prompt, before adding the calculations that form the core of our focus in this lesson. But first, we will need to "redirect" the report to our new cube clone, as it is currently "pointed" to the Sales cube in the original FoodMart 2000 Analysis Services sample database.

1.  Within the RS018 project tree in the Solution Explorer, double-click the new Multi-Layer_Calculations report, to open it.

The report opens within the Report Designer, and the Layout View appears, as depicted in Illustration 26

Click for larger image

Illustration 26: The Report Clone - Layout View

2.  Click the Data tab.

3.  In the Dataset selector atop the Data tab, (which is now occupied by ProductData, a Dataset created by a simple MDX query to support the sample FoodMart Sales report), select ProductList.

This exposes the simple MDX query that supports the parameter picklist.

4.  Click the Delete Selected Dataset button, shown circled in Illustration 27.

Illustration 27: Deleting the ProductList Picklist Query ...

5.  Click Yes when the message box appears asking of our certainty.

The Dataset definition is deleted, leaving the ProductData query in its place.

6.  Modify the FROM statement in the MDX query that appears within the ProductData Dataset, substituting [Percent of Total] for Sales (the original cube).

The modified MDX query appears on the Data tab, as shown in Illustration 28.

Illustration 28: Modified MDX Query - Pointed to New Cube Clone

7.  Click the Run button (marked "!" atop the Data tab).

The Data pane below the query is populated, indicating that the Data set is functional, and pointed to the Percent of Total cube.

8.  Replace the existing query with the following:


   { [Measures].[Store Sales], [Measures].[Percent Total Sales]  } ON COLUMNS,

   { Descendants([Store].[USA], [Store].[Store Name], LEAVES) } ON ROWS,

   {Time.[Year].[1997]} ON PAGES


   [Percent of Total]

The new MDX query appears on the Data tab, as presented in Illustration 29.

Illustration 29: The Modified Query on the Data Tab

9.  Click the Run button, once again.

The Data pane below the query is populated, again indicating that the Dataset is functional.

10.  Click the Refresh Fields button (shown circled in Illustration 30) to refresh the Data Fields in the report.

Illustration 30: Refresh Fields in the Report ...

Now we are ready to alter the report, to simplify it for the exercises ahead.

11.  Click the Layout tab to get to the report layout.

We will streamline the report at this point to eliminate distractions, before we focus on generating Percent of Total values based upon the calculated member we have added to the underlying cube. We will also create an independent calculated field to generate the same value independently within Reporting Services.

We will first remove the Product Family parameter from the report, and then eliminate groupings to render a simple, single level report, from which we can easily focus upon the calculations with which we are concerned in our example.

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

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

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

Illustration 32: The Report Parameters Dialog

Our objective at this point is to remove the existing parameter, and then to perform a couple of additional "eliminations" to simplify the report.

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

14.  Click the Remove button underneath the Parameters list.

The ProductFamily parameter is removed from the list.

15.  Click OK to accept removal of the parameter.

The now empty Report Parameters dialog closes, and we are returned to the Layout view of the report. We must now delete a reference to the parameter we have removed, which we can access via the Properties dialog for the matrix.

16.  Click at some point within the title textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible.

17.  Right-click the upper left corner of the matrix. (If the headers disappear as you touch them with the cursor, you should still see a faint outline of the matrix.)

18.  Select Properties from the context menu that appears, as depicted in Illustration 33.

Illustration 33: Accessing the Matrix Properties

The Matrix Properties dialog opens, defaulted to the General tab.

19.  Click the Filters tab.

20.  Click the Value field of the single occupied row to select it.

21.  Click the Delete button to delete the reference to the parameter, as indicated in Illustration 34.

Illustration 34: Select and Delete the Parameter Reference

The remaining reference to the now-deleted parameter is itself deleted. All that remains is a bit of "level extraction," and rearrangement. We recall that the consumers have told us that the row axis needs to display USA Stores. Moreover, they have declared that the custom report will no longer require drilldown features, as it will be a fixed report whose purpose in life is as a limited analysis tool.

The targeted "extractions," which will leave us with a good starting point for customizing the report to the new measures, are depicted in Illustration 35.

Illustration 35: Targeted Levels for Removal in the Customized Report

22.  From our current position within the Matrix Properties dialog, click the Groups tab.

Four groups appear in the Rows list box, and two added groups appear in the Columns list box. The groups appear, with those targeted for elimination circled, as shown in Illustration 36.

Illustration 36: Existing Groups in the Clone Report

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