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

June 20, 2005

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.

MSSQL Server Reporting Services
Introducing the Tablix Data Region: Basic Grouping Concepts
Introducing the Tablix Data Region in Reporting Services 2008
100% Stacked Column Chart for Analysis Services Data
XY (Scatter) Chart for Analysis Services Data
Simple Doughnut Chart for Analysis Services Data
Exploded Pie Chart for Analysis Services Data
Stacked Bar Chart for Analysis Services Data
Line Chart for Analysis Services Data
Stacked Column Chart for Analysis Services Data
A More Advanced Pie Chart for Analysis Services Data
Simple Pie Chart for Analysis Services Data
Simple Bar Chart for Analysis Services Data
Simple Column Chart for Analysis Services Data
Introducing Reporting Services Charts for Analysis Services
Mastering OLAP Reports: Parameterized Grouping
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
Support Parameterization from Analysis Services - Parameter Defaults
Parameterization from Analysis Services - Cascading Picklists
Support Parameterization from Analysis Services
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Reporting Services: Customize Automatically Created Parameter Support Objects
Snapshot Reports II: SQL Server Management Studio Perspective
Snapshot Reports I: Report Manager Perspective
Report Execution Caching II: Report Manager Perspective
Report Execution Caching I: SQL Server Management Studio Perspective
Report Session Caching in Reporting Services 2005
Black Belt Administration: Reporting Services Configuration Manager
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Black Belt Components: Support Simple Navigation with a Document Map
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Black Belt Components: Interactive Sorts within a Matrix Data Region
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
BlackBelt Administration: Linked Reports in SQL Server Management Studio
BlackBelt Administration: Linked Reports in Report Manager
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Report Builder: Creating a Report Model
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Interactive Sorting Within Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers