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:
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:
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):
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.
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.