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

Monday Jun 20th 2005 by William Pearson
Share:

Discover two approaches to the generation of percent - of - total values in Reporting Services." In this article, BI Architect Bill Pearson provides options from both cube and report layers within an integrated BI solution, contrasting and comparing approaches, and introducing "which layer" considerations.

About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I have stated since the charter article of the series, published about the time Reporting Services was first publicly released, my conviction is that Reporting Services will commoditize business intelligence, particularly in its role as a component in an integrated Microsoft BI solution. Having been impressed from my first exposure to this exciting application, when it was in early beta, my certainty in its destiny grows stronger by the day, as I convert formerly dominant enterprise Business Intelligence systems, such as Cognos, Business Objects, Crystal, and others, to the Reporting Services architecture. I receive constant requests to conduct strategy sessions about these conversions with large organizations in a diverse range of industries - the interest grows daily as awareness of the solution becomes pervasive. Indeed, the six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

Basic assumptions underlying the series are that you have correctly installed Reporting Services, including current service packs, along with the applications upon which Reporting Services relies, and that you have access and the other rights / privileges required to complete the steps we undertake in my articles. For details on the specifics of the adjustments necessary to quickly allow full freedom to complete the exercises in this and subsequent articles, as well as important assumptions regarding rights and privileges in general, please see earlier articles in the series, as well as the Reporting Services Books Online.

About the Mastering OLAP Reporting Articles ...

As I have noted in many articles and presentations, one of the first things that becomes clear to "early adopters" of Reporting Services is that the "knowledgebase" for OLAP reporting with this tool is, to say the least, sparse. (I recently heard an internal "reporting guru" say, during a BI strategy session with a major soft drink manufacturer in Atlanta, that "we didn't evaluate Reporting Services because it doesn't do cubes ...") As most of us are aware, minimal, if any, attention is given to using Analysis Services cubes as data sources for reports in the handful of books that have been published on Reporting Services to date. All are written from the perspective of relational reporting, as if with existing popular tools for that purpose. One Reporting Services book discusses OLAP reporting with Reporting Services, and then performs illustrative exercises with Office Web Components (OWC), instead. Another depicts an MDX snippet at the end of the book, as if as an afterthought. All of the early books focus entirely on relational reporting, and most make heavy use, typically enough, of the Books Online and other scraps of documentation that we already have anyway. (I could go on, but my overall opinion of the technical book industry is already well known.)

As I stated in my article, Mastering OLAP Reporting: Cascading Prompts, the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the reporting solutions of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services - at a tiny fraction of the cost. The vacuum of documentation in this arena, to date, represents a serious "undersell" of Reporting Services from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, rest assured that the OLAP potential in Reporting Services will be yet another reason that the application "commoditizes" Business Intelligence.

For more information about the Mastering OLAP Reporting articles, see the section entitled "About the Mastering OLAP Reporting Articles" in my article Ad Hoc TopCount and BottomCount Parameters.

Overview

An important consideration, when designing a Business Intelligence system within any environment, is a consideration for "where to put the intelligence" among the various "layers" within the system. I have stated many times in the past, in both articles and presentations, that "multi-layered reporting solutions require multi-layered architects," and nowhere is this truer than within the design and implementation of the Microsoft integrated business intelligence solution. This article provides an excellent example of such considerations: the option for placing a needed calculation at either the Analysis Services level (within the cube structure) or the Reporting Services level.

NOTE: For a summary of my opinions surrounding the importance of thinking "multi-dimensionally" within the design and implementation of a business intelligence system, see Multi-Layered Business Intelligence Solutions ... Require Multi-Layered Architects.

In this session, we will examine the satisfaction of a business requirement within the cube upon which a pre-existing report is based (we will modify a sample OLAP report to save time and maintain the focus of the lesson as much as possible). We will then add the same capability at the report level, comparing and contrasting the steps as we do so. We will discuss, as we progress, some of the considerations that arise in similar scenarios, where we have multiple options for "where to put the intelligence."

While we will be examining a relatively simple set of circumstances in the interest of time and space, we should keep in mind that the concepts involved (the fact that we do, indeed, have options, and where to put the capabilities among the multiple layers of the architecture) can become extremely important in enterprise-level design and implementation. Perhaps one of the most important considerations I hope to make plain is that, as I have mentioned many times, determining the placement of components among the layers requires far more than expertise in Reporting Services alone, an excellent grasp of SQL or MDX, an outstanding capability to generate relational reports from MSSQL Server, in general, or mastery of any of the other discrete layers of the integrated BI solution. Multi-level expertise is required to optimize a multi-level solution.

In this article, we will:

  • Discuss a common business need within the reporting environment, the requirement to present percent of total values;
  • Discuss two options for meeting the business requirement, focusing upon the Analysis Services layer as the home of one solution, and Reporting Services as the layer containing the second;
  • Prepare for our practice session by creating copies of both an Analysis Services sample cube and an existing sample OLAP report, to save time, while preserving the respective original samples;
  • Provide an overview of the logic behind the percent of total calculation, as part of a specification to create the capability for a hypothetical group of information consumers;
  • Create a calculated member in the Analysis Services layer to generate the desired percent of total value;
  • Explain the MDX that we employ at the cube level, within the Percent of Total calculated member;
  • Modify the clone report to more closely meet the new presentation specifications of the information consumers;
  • Create a calculated field in the sample report to present percent of total at the reporting layer;
  • Explain the expression we assemble to support the calculated field we create within the sample report;
  • Add both calculated member and calculated field to the streamlined report to demonstrate approach;
  • Discuss considerations, throughout the article, surrounding which layer to select for the construction of the percent of total calculation we create for the information consumers;
  • Preview the report in Report Designer, to verify the operation of the calculations that form the scope of our practice exercise.

Percent of Total - Two Perspectives

Introduction and Business Scenario

A request for assistance that I receive on a frequent basis, via e-mail, forums, and elsewhere, involves the need to calculate a percent of a total, primarily to determine members' contribution to greater wholes. This determination is quite desirable and common in financial and other reporting. A good example might be the percentage of total organizational revenue generated by each individual store, to support analysis of store performance relative to peer stores in a group, or for other possible purposes. We might build further sophistication to allow us to perform the calculation of each store's share of the subtotals at the various levels of the Store dimension hierarchy (City, State and Country), but in the present scenario, we will find ourselves tasked with a relatively basic requirement.

Let's assume for our practice example that we have received a request from a group of information consumers in the Budget and Planning unit of the FoodMart 2000 organization. The request is for support in the presentation of some data, housed within the Sales sample cube, regarding USA Store Sales for 1997. The consumers wish to see the percentage contribution of each Store to the total USA Store Sales, alongside the sales values that are already available for each Store. To restate, they want to see the percentages of total USA Store Sales that can be attributed to each Store.

The ultimate report, we are told, will display USA Stores on the row axis, with Store Sales on the column axis. The report will require no drilldown features, and will concern itself with 1997 data only. Its use will be strictly limited to a high-level analysis of store performance from a Store Sales perspective.

We will examine the satisfaction of the request of the information consumers through two independent means. First, we will present a solution at the Analysis Services layer of the business intelligence system, using a straightforward calculated member to deliver the percent of total value. We will then examine a solution that will be completely contained within Reporting Services. We will perform verification to ensure that both solutions deliver the same answer at the presentation layer of the system by inserting both into a report we modify especially for that purpose. Finally, we will discuss some of the advantages that lie within each solution, and the some of the contexts within which one might be a more optimal approach than its counterpart. My objective is thus to illustrate the fact that we can provide solutions at different layers of the system to meet the hypothetical business requirement we have outlined, as a means of reinforcing the concepts in our minds. Once we have activated the concepts, they can then be triggered upon meeting similar situations in our respective business environments.

This article focuses on two approaches to the same end, one at the data source (an Analysis Services cube), and another at the reporting layer (a Reporting Services report). In addition to providing approaches to meet the need to present percent of total information, another important objective of this article is to emphasize the fact that, even though Reporting Services is an excellently flexible tool for generating calculations, manipulating data, and more, an integrated philosophy needs to be adopted in an environment where we can perform many operations at different layers within a "chained" solution.

Considerations and Comments

Before we make alterations to the FoodMart Sales cube to support the Percent of Total calculation in our report, we will create a copy of the cube. Creating a clone of the sample Sales cube means we can make changes to our cube while retaining the original sample in a pristine state - perhaps for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, in learning more about Analysis Services in general. Once we finish with our structural changes to the clone cube, we will create a copy of the Foodmart Sales sample report, for the same reasons.

Before we can work with a clone of the Foodmart Sales report, we need to create a Reporting Services project in the Visual Studio.Net 2003 Report Designer environment. Streamlining, and then making the enhancements to the report to add the requested functionality, can be done easily within the Reporting Services Report Designer environment. In addition, working with a copy of the report will allow us the luxury of freely exploring our options, and leave us a working example of the specific approach we took, to which we can refer in our individual business environments. This approach also preserves the original sample in a pristine state - for the same reasons we do so for the Sales sample cube. If you already have a project within which you like to work with training or development objects, you can simply skip the Create a Reporting Services Project section.

If the sample FoodMart 2000 Analysis Services database or the Foodmart Sales report was not created / installed as part of the initial installation of the associated application, if either was removed prior to your beginning this article, or if either or both applications have yet to be installed, etc., see the respective Books Online or other documentation for the necessary procedures to prepare for the exercises that follow.

Hands-On Procedure

Preparation

Create a Clone of the Foodmart Sales Cube

Let's first create a copy of the existing FoodMart Sales cube to provide a "disposable" work environment.

1.  Open Analysis Manager.

2.  Expand the Analysis Servers folder in the management console.

3.  Expand the Analysis Server with which you are working by clicking the "+" sign to its left.

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder inside the FoodMart 2000 database.

6.  Right-click the Sales cube.

7.  Click Copy from the context menu that appears, as shown in Illustration 1.


Illustration 1: Select Copy from the Context Menu

8.  Right-click the Cubes folder.

9.  Select Paste from the context menu that appears, as depicted in Illustration 2.


Illustration 2: Select Paste, after Right-clicking the Cubes Folder

The Duplicate Name dialog appears.

10.  Rename the new cube as follows:

Percent of Total

TIP:

This is also a good way to rename MSAS objects for which a "Rename" option does not exist. We simply create the new object in the manner shown above, give it the desired name, and discard the original object, as appropriate.

Keep in mind that, in the case of cubes and other structural objects, this will mean reprocessing before the clone will be fully usable.

The Duplicate Name dialog appears as shown in Illustration 3.


Illustration 3: Duplicate Name Dialog with Our Input

11.  Click OK to create the clone cube.

The new Percent of Total cube appears in the tree as depicted in Illustration 4.


Illustration 4: The New Cube Appears

Enhance the Foodmart Percent of Total Cube to Support the Percent of Total Calculation

From the perspective of the Percent of Total cube, in order to present the percent of total information requested by the information consumers, we will need to create a calculated member in Analysis Services.

1.  Right-click the new Percent of Total cube.

2.  Select Edit ... from the context menu that appears, as shown in Illustration 5.

Click for larger image

Illustration 5: Select Edit from the Context Menu

The Cube Editor opens.

3.  Right-click the Calculated Members folder within Cube Editor.

4.  Select New Calculated Member ... from the context menu that appears, as depicted in Illustration 6.


Illustration 6: Select New Calculated Member from the Context Menu

The Calculated Member Builder opens.

5.  Ensure that Measures is selected in the Parent dimension selector atop the Calculated Member Builder.

6.  Type the following into the Member name box within the Calculated Member Builder:

Percent Total Sales

7.  Type the following into the Value expression box within the Calculated Member Builder:

([Store Sales], [Store].CURRENTMEMBER) / ([Store Sales], [Store].[All Stores])

The above MDX provides for a simple Percent Total Sales calculation. In essence, it consists of Store Sales for the currently selected Store, divided by Store Sales for all Stores. We will assign a "percent" format in the following steps, mostly for purposes of viewing in the cube browser - we will handle formatting for the report at the Reporting Services level in a subsequent section. Keep in mind that the above is a simple approach, and that, in a real world scenario, we would want to build in more sophisticated logic to manage such scenarios as nulls, composition of rollups, and so forth.

NOTE: For more information regarding the CurrentMember function, see my Database Journal article MDX Essentials: MDX Member Functions: "Relative" Member Functions. For information on managing nulls and other considerations surrounding "contribution to totals" scenarios, see both MDX in Analysis Services: Intermediate Concepts - Part 2, and MDX Essentials: Logical Functions: The IsEmpty() Function, also here at Database Journal.

The Calculated Member Builder appears as shown in Illustration 7.


Illustration 7: The Calculated Member Builder, with Our Input

8.  Click OK to close the Calculated Member Builder, and to accept our input.

We see the new Calculated Member appear in the tree pane, within the Calculated Member folder

9.  Click the Percent Total Sales calculated member in the tree, to select it, if necessary.

10.  Click the Properties bar at the bottom of the pane, to open the Properties pane, if necessary. (If "Properties" appears on the bar with an upward-pointing arrow to its right, clicking will open the pane / point the arrow downward).

11.  Click the Advanced tab in the Properties pane.

12.  In the selector for the Format String property box, select Percent, as depicted in Illustration 8.


Illustration 8: Setting Percent Format for the Percent Total Sales Calculated Member

Let's process the cube, to update it for the first time since its cloning.

13.  Select Tools --> Process Cube from the Cube Editor main menu, as shown in Illustration 9.


Illustration 9: Process the Cube ...

14.  Click Yes when prompted to save the cube, as depicted in Illustration 10.


Illustration 10: Save the Cube before Processing

15.  Click No when asked if you wish to design aggregations, in the next Analysis Manager dialog, shown in Illustration 11.


Illustration 11: Decline Aggregation Design ...

The Process a Cube dialog appears.

16.  Ensure that the radio button to the left of Full Process, as depicted in Illustration 12, is selected (the default, as the cube has not been processed since its creation).


Illustration 12: Full Process Option Selected ...

17.  Click OK to begin processing.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Illustration 13.


Illustration 13: Processing Completes Successfully ...

18.  Click Close to dismiss the viewer.

Let's do a quick verification of the operation of the cube's newest calculated member, Percent Total Sales. We can examine the results of our handiwork while here in the Analysis Manager, via the Cube Browser, before revisiting it later in our report. This allows us to ensure effectiveness before we leave the design environment.

19.  From our present position within the Cube Editor, click the Data tab to open the Browser and see cube data.

20.  Drag the Store dimension down, and drop it atop the existing row ("Y") axis heading, as necessary.

21.  Ensure that the Measures dimension appears on the column ("X") axis, dragging it there if necessary.

The Data tab matrix appears as partially depicted in Illustration 14.


Illustration 14: The Data Tab Matrix Axes, with Our Modifications (Partial View)

22.  Double-click the USA Store Country in the row axis to expand it.

23.  Expand the Store State level for Washington (WA).

24.  Scroll over to the right in the Cube Browser, until our new calculated measure, Percent Total Sales, appears, as shown in Illustration 15.


Illustration 15: Percent Total Sales Appears ...

We can readily see that the simple percent of total calculation is performing as expected. We will now move to Reporting Services, where we will create a similar calculated field, which we will then juxtapose in a report side by side with the Percent Total Sales calculated member in the cube, to compare the two, and discuss considerations surrounding the two methods from the standpoint of an integrated BI solution.

25.  Verify the math behind the Percent Total Sales values, if desired, to gain comfort with its correctness.

26.  Leave the Cube Editor open in Data view, for comparison against our report calculations later, if desired.

Create a Reporting Services Project

To begin, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

1.  Click Start.

2.  Navigate to, and click, the Microsoft Visual Studio .NET 2003 shortcut in the Programs group, as appropriate.

The equivalent on my PC appears as depicted in Illustration 16.

Click for larger image

Illustration 16: Beginning in Microsoft Visual Studio .NET 2003 ...

Visual Studio .NET 2003 opens at the Start page.

3.  Select File --> New from the main menu.

4.  Click Project from the cascading menu, as shown in Illustration 17.

Click for larger image

Illustration 17: Selecting a New Project

The New Project dialog appears. Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services.

5.  Click Business Intelligence Projects in the Project Types tree, if necessary.

6.  Click Report Project in the Templates list.

7.  Type the following into the Name box, leaving other settings at default:

RS018

8.  Navigate to a location in which to place the Report Project files.

The New Project dialog appears, with our input, as depicted in Illustration 18.


Illustration 18: The New Projects Dialog, with Addition

9.  Click OK.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 19.


Illustration 19: The New Project Appears in the Solution Explorer

Having created a Report Project, we are ready to proceed with creating the new report clone.

Adding Multi-Layered Calculations in an OLAP Report

In this section, we will copy the existing Foodmart Sales report, and then open it in Reporting Services' Report Designer, where we will add the calculations that are the focus of our article.

Create a Copy of the Foodmart Sales Report

As we have noted, we will be working with a copy of the Foodmart Sales report, to keep the original sample intact for easy access and use at another time.

1.  Right-click the Reports folder underneath the newly created shared data source, in the Solution Explorer.

2.  Select Add --> Add Existing Item from the cascading context menus that appear, as depicted in Illustration 20.


Illustration 20: Adding the Report to the Project ...

The Add Existing Item - RS018 dialog appears.

When we installed Reporting Services, the default installation point for the sample report files was the Samples folder within the Reporting Services program folder. A common example of this default path is as follows:

C:\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Reports

3.  Using the Add Existing Item - RS018 dialog, navigate to the location of the sample reports in your own environment.

An example of the Add Existing Item - RS018 dialog, having been pointed to the sample Reports folder (which contains the Foodmart Sales report we seek), appears as partially shown in Illustration 21.


Illustration 21: Navigating to the Sample Reports Folder ...

4.  Right-click the Foodmart Sales report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 22.


Illustration 22: Performing a Quick Copy of the FoodMart Sales Report

6.  Within the Add Existing Item - RS018 dialog, navigate to the RS018 folder we created earlier, when we added the new RS018 project.

7.  Right-click somewhere in the white space inside the RS018 folder, within the dialog.

8.  Select Paste from the context menu that appears, as shown in Illustration 23.


Illustration 23: Select Paste to Complete Copying ...

A copy of the Foodmart Sales report appears within the dialog.

9.  Right-click the new file.

10.  Select Rename from the context menu that appears.

11.  Type the following name in place of the highlighted existing name:

Multi-Layer_Calculations.rdl

NOTE: Be sure to include the .rdl extension in the file name.

The renamed copy of the Foodmart Sales sample report appears as depicted in Illustration 24.


Illustration 24: The New Report File, Multi-Layer_Calculations.rdl

12.  Select the new file by clicking it, if necessary.

13.  Click Open on the dialog box to add the new report to report project RS018.

Multi-Layer_Calculations.rdl appears in the Reports folder, within the RS018 project tree in the Solution Explorer, as shown in Illustration 25.


Illustration 25: The New Report Appears in Solution Explorer - Report Folder

Having created a copy of the functional report, we are now ready to make the enhancements requested by the FoodMart information consumers.



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:


SELECT

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

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

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

FROM

   [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

23.  Click the BrandSales_Product_Category field (second from the top in the Rows list) to select it.

24.  Click the Delete button to delete the group.

25.  Click the BrandSales_Product_Subcategory field (currently the middle entry in the Rows list) to select it.

26.  Click the Delete button to delete the group.

27.  Click the BrandSales_Brand_Name field (currently the bottom entry in the Rows list) to select it.

28.  Click the Delete button to delete the group.

29.  Click the BrandSales_Quarter field (currently the middle entry in the Columns list) to select it.

30.  Click the Delete button to delete the group.

The Groups tab appears, after our deletions, as depicted in Illustration 37.

Click for larger image

Illustration 37: Groups Tab after Eliminations

31.  Click the BrandSales_Product_Department group (now the only entry in the Rows list) to select it.

32.  Click Edit.

The Grouping and Sorting Properties dialog for the group opens to the General tab.

33.  Type the following into the Name box on the General tab, replacing the existing name:

StoreName.

34.  Select Fields!Store_Store_Name.Value within the dropdown selector of the Expression list, in the Group on section.

The Grouping and Sorting Properties dialog appears as shown in Illustration 38.


Illustration 38: The Grouping and Sorting Properties Dialog with Our Changes

35.  Click OK to accept changes and close the Grouping and Sorting Properties dialog for the StoreName group.

36.  Click OK to accept changes, and to close the Matrix Properties dialog, and to return to the Layout view in Report Designer.

Only a few minor changes remain to prepare the report for its new uses.

37.  Right-click the textbox underneath the report title, currently displaying =Fields!Product_Department.Value.

38.  Select Properties from the context menu that appears, as depicted in Illustration 39).


Illustration 39: Select Properties from the Context Menu ...

The Textbox Properties dialog opens.

39.  Replace the contents of the Name box with the following:

StoreName.

40.  Click the Function button ("fx") to the right of the Value box.

The Edit Expression dialog appears.

41.  Replace the contents of the Expression pane (the right half of the dialog) with the following expression:

= Fields!Store_Store_City.Value & " - " &  Fields!Store_Store_Name.Value

The Edit Expression dialog appears as shown in Illustration 40.


Illustration 40: Edit Expression Dialog with Our Modifications

42.  Click OK to accept changes and return to the Textbox Properties dialog, which appears as depicted in Illustration 41.


Illustration 41: Textbox Properties Dialog

43.  Click OK to close the Textbox Properties dialog.

44.  Click the textbox under the Store Cost label in the Matrix, as shown in Illustration 42, to select it.


Illustration 42: Targeting Store Cost for Elimination ...

45.  Touch the Delete key.

Store Cost disappears from the report.

46.  Click the textbox under the Store Profit label in the Matrix, as depicted in Illustration 43, to select it.


Illustration 43: Targeting Store Profit for Deletion ...

47.  Touch the Delete key.

Store Profit disappears from the report. The Multi-Layer_Calculations report, Layout view, appears as shown in Illustration 44.


Illustration 44: The Multi-Layer_Calculations Report - Layout View

We need to complete a final "cleanup" step before executing the report.

48.  Right-click the Store_Profit calculated field in the Fields list.

49.  Select Delete from the context menu that appears, as depicted in Illustration 45.


Illustration 45: Deleting the Store_Profit Calculated Field

This removes the calculated field, which is no longer relevant to our report, from the list.

50.  Click the Preview tab to execute the modified report.

The Multi-Layer_Calculations report appears as shown in Illustration 46.


Illustration 46: Multi-Layer_Calculations, Reflecting Our Modifications

The report's layout has been altered sufficiently to meet the layout requirements as expressed by the information consumers. We are now ready to insert the calculated member that we created in the cube earlier.

Procedure

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:

=First(Fields!Measures_Percent_Total_Sales.Value)

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:

PercTotalSales

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

=First(Fields!Measures_Percent_Total_Sales.Value)

to the following:

=SUM(Fields!Measures_Percent_Total_Sales.Value)

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.

Create a Calculated Field within the Report to Deliver Percent of Total

Having delivered the calculation specified by the information consumers through a calculated member in our cube, we are ready to create a report-based solution for comparison purposes. There are certainly reasons why this might be the only approach available to us; Examples include the need, as a report author, to deliver the functionality in an environment where we are afforded no access to the Analysis Services layer.

I find, in consulting with many large companies, that the administration of RDBMS, OLAP and reporting application / presentation layers are often managed by different areas of the business, or within a large, segmented IT department by what often amounts to small, rather territorial subgroups or individuals. The territorialism, proliferation of information silos, and other such circumstances tend to become even more pronounced when the enterprise has another RDBMS / several different RDBMS' (such as Teradata, Oracle, etc.), and an effort is underway to use MSSQL Server as the Warehouse / Mart to support Microsoft OLAP with Analysis Services. "Failure to communicate" often results, for reasons other than connectivity issues, as anyone who has been in similar straits (particularly in the role of a BI Architect or equivalent) can attest.

The unfortunate result is often akin to a scenario where the person or persons setting about the BI initiative do not have a complete understanding of all the layers. To the Reporting Services "guru," the foregone conclusion is virtually always that "we can do it in the report." And because we can, we do. (One is reminded of the expression "To a hammer, everything is a nail ..."). Regardless of whether the author of a report is simply unaware that more optimal options exist, or if the author is not allowed to cross layers in designing and building support for reports, the risk is the same: the most optimal solution may not be forthcoming.

We will take our next step from our current position on the Data tab, within the Report Designer. We will add a calculated field that generates the requested Percent of Total value, completely within the confines of Reporting Services.

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

2.  Click the Store_Sales field in the Report Designer Field List, to select it.

3.  Drag the Store_Sales field onto the Layout tab, dropping it to the right of the data field underneath the Cube % Total column in the matrix (with which we were working in our last section) as depicted in Illustration 53.


Illustration 53: Adding the Store_Sales Field to the Report Again

A second Store_Sales column appears.

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

=First(Fields!Measures_Percent_Total_Sales.Value)

5.  Select Properties from the context menu that appears, as shown in Illustration 54.


Illustration 54: Select Properties for the New Data Item

The Textbox Properties dialog opens.

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

PercTotalSales1

7.  Click the Function (fx) button to the right of the Value box.

The Expression Editor opens.

8.  Modify the existing expression in the Expression pane (right half of the Editor):

=First(Fields!Measures_Percent_Total_Sales.Value)

to the following:

=(SUM( Fields!Store_Sales.Value)/ SUM(Fields!Store_Sales.Value, "ProductData"))

The Expression Editor appears as depicted in Illustration 55.


Illustration 55: The Expression Editor, with Our Modifications

The above expression provides for a simple Percent Total Sales calculation. In essence, it consists of Store Sales for the Store on a given row of the matrix, divided by Store Sales for all Stores (hence, the context of "ProductData" that appears in the denominator of the expression). We will assign a "percent" format in the following steps. As with the calculated member that generated the Percent Total Sales in the cube, please keep in mind that the above is a simple approach, and that, in a real world scenario, we would likely want to build in additional logic to manage such scenarios as nulls, and so forth.

9.  Click OK to accept changes, and to close the Expression Editor.

We return to the Textbox Properties dialog.

10.  Click Percentage in the Format list (right half of the dialog)

11.  Click the top selection in the two options in the right-most pane (the selection that provides for two decimal points, as we selected in the last section).

The Textbox Properties dialog appears as shown in Illustration 56.


Illustration 56: The Textbox Properties Dialog, with Our Modifications

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

13.  Double-click the box directly above the textbox just modified (currently labeled Store Sales).

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

Report % Total

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

16.  Select Center from the cascading menu that appears, as we did in the earlier section.

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


Illustration 57: The Report with New Addition - Layout View

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

The report executes once again, and appears as shown in Illustration 58.


Illustration 58: The Report with Both Calculations in Place

We now see both calculations in place, and, with the capability to compare them side-by-side, we see that they offer identical functionality from a presentation perspective. In addition to providing a couple of approaches to the popular question "How do I generate percents of totals?" , our practice example represents, as part of the process, an excellent illustration of the quandaries that can arise, and an equally excellent case for an architect / designer / implementer who understands the workings of multiple layers, within the integrated BI system.

18.  Select File --> Save All to save all work to this point.

19.  Select File --> Exit when ready to leave the Reporting Services development environment.

20.  Within Analysis Services (if left open from the earlier section), select File --> Exit to close the Cube Editor, when desired.

21.  Exit Analysis Services, as appropriate.

Conclusion ...

In this article, we continued our exploration of OLAP reporting with Reporting Services, focusing generally upon multiple approaches to a common question: "How can I calculate and present a percent of a total among items that compose it?" After discussing in general the requirement to present percent of total values, we discussed two options for meeting the business requirement, focusing upon the Analysis Services layer as the home of one solution, and Reporting Services as the layer containing the second. We then presented a hypothetical business need, and prepared for our practice example by creating copies of both an Analysis Services sample cube and an existing sample OLAP report, to save time while preserving the respective original samples.

In exploring the two options we proposed for creation of the percent of total calculation, we first created a calculated member in the Analysis Services layer to generate the desired values, explaining the MDX that we employed. We next added the new calculated member to the supporting Dataset of the clone report, which we had modified to more closely meet the expressed presentation specifications of the information consumers.

We then created a calculated field, explaining the expression that we put in place to generate it, within the sample report to present percent of total at the reporting layer. Finally, we verified, via previews in the Report Designer, that both calculations delivered identical results from a presentation perspective. Throughout the article we discussed general considerations surrounding which layer to select for the construction of the calculations and other objects we create for the information consumers, emphasizing that multi-layer expertise is often key to the optimal implementation of an integrated business intelligence solution.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved