MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives - Page 2
June 20, 2005
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.