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 8

By William Pearson


Insert the Calculated Member from the Cube into the Report

Having prepared the report, we are ready to assemble the data presentation requested by the information consumers.

We will take the next step from our current position on the Layout tab, within the Report Designer. Here we will add the new calculated member, Percent Total Sales from the Percent of Total cube.

1.  Click the new calculated member, named Measures_Percent_Total_Sales in the Report Designer Field List, to select it.

2.  Drag the Measures_Percent_Total_Sales onto the Layout tab, dropping it to the right of the data field underneath the Store Sales column in the matrix (just as we would add any existing field to a report in Report Designer), as depicted in Illustration 47.

Click for larger image

Illustration 47: Adding the New Calculated Member to the Report

One of the more obvious advantages in creating a calculated member in the cube, versus creating an equivalent calculation in the report, is that the calculation thereby becomes available to us anytime we refer to it in using the parent cube as a data source. If we create it within the report, we have to re-create it in subsequent reports. Using cube structures to house calculations (and, as I show in numerous articles of the series, other report objects) therefore means centralized creation and maintenance, not to mention consistency in reporting, as calculations of this sort are more easily controlled - we don't have three developers, for instance, creating three separate calculations locally, in three separate reports, each, perhaps, giving the calculation a different name, or, worse, each arriving at differing results because of differences in the construction of the calculations themselves. Performance gains can also be enjoyed by constructing the calculation within the data source.

The new measure appears in a new column, automatically labeled Measures Percent Total Sales, to the right of the Store Sales column.

3.  Right-click the new data textbox, which currently displays the following expression:


4.  Select Properties from the context menu that appears, as shown in Illustration 48.

Illustration 48: Select Properties for the New Data Item

5.  The Textbox Properties dialog opens.

6.  Replace the text in the Name box (which was automatically provided) with the following:


7.  Modify the existing expression in the Value box from:


to the following:


8.  Click the Standard radio button in the Format section (right half of the dialog) to select it.

9.  Click Percentage within the Standard list.

10.  Click the top selection in the two options in the right-most pane (the selection that provides for two decimal points).

The Textbox Properties dialog appears as depicted in Illustration 49.

Illustration 49: The Textbox Properties Dialog, with Our Modifications

11.  Click OK to accept our changes and to close the dialog.

12.  Double-click the box directly above the textbox just modified (currently labeled Measures Percent Total Sales).

13.  Replace the text in the box (which was automatically provided) with the following:

Cube % Total

14.  With the newly labeled field still selected, select Format --> Justify from the main menu of the Report Designer.

15.  Select Center from the cascading menu that appears, as shown in Illustration 50.

Illustration 50: Centering the New Label ...

The report appears, in Layout view, as depicted in Illustration 51.

Illustration 51: The Calculated Member Renamed ...

16.  Click the Preview tab, atop the design environment.

The report executes quickly, and appears as shown in Illustration 52.

Illustration 52: Multi-Layer_Calculations, Reflecting Modifications ...

We see the effects of our handiwork. We can easily compare the values to those we see in the Cube Browser of Analysis Services, to ascertain accuracy. Next, we will investigate the accomplishment of the same results with a solution that is freestanding within the report layer.

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