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.

A second Store_Sales column appears.

=First(Fields!Measures_Percent_Total_Sales.Value)

The Textbox Properties dialog opens.

PercTotalSales1

The Expression Editor opens.

=First(Fields!Measures_Percent_Total_Sales.Value)

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

The Expression Editor appears as depicted in Illustration 55.

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.

We return to the Textbox Properties dialog.

The Textbox Properties dialog appears as shown in Illustration 56.

Report % Total

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

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

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.

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.