MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances - Page 2

Overview

In our previous article, Mastering OLAP Reporting: Percent of Total - Two Perspectives, we examined two approaches to meeting a common business requirement, the generation of percent of total values, within the integrated Microsoft Business Intelligence solution - or, more specifically, a means for doing so at two different levels within the layers of the solution. We explored an approach to generating percent of total values within the Analysis Services layer, before looking at meeting the same challenge at the Reporting Services layer.

While we examined a relatively simple set of business requirements, we noted at several junctures that we do, indeed, have options for the placement of structures supporting such capabilities among the multiple layers of the architecture. We discussed the fact that these options, and the intelligent selection among them for such placement, can become extremely important in enterprise-level design and implementation. Our secondary objective within the article, then, was to emphasize that determining the placement of components among the layers requires far more than mere expertise in Reporting Services, or in any other single layer of the integrated solution, alone. To summarize our assertions once again, multi-level expertise is required to optimize a multi-level solution.

In this article, we will extend our percent of total solution to its presentation within a chart, combining the concepts involved with a few mechanical procedures about which I receive recurring questions via e-mails and forum insertions. We will use a pie chart, as it offers more "surface area" with regard to optional displays, but the same concepts apply in most cases to other chart types. We will begin at the point of dataset construction in the chart, and so I would recommend first creating the supporting Analysis Services components for the exercise based upon our previous article, Mastering OLAP Reporting: Percent of Total - Two Perspectives.

In this article, we will:

• Discuss a common business need within the reporting environment, the requirement to present percent of total values within a chart data region;
• Discuss briefly two options for supporting the calculation that we need, focusing upon the Analysis Services layer as the home of one solution, and Reporting Services as the layer containing the second, and referring to the construction of these in our previous article;
• Prepare for our practice session by returning to the project that we created in the previous article of this series, and by creating a "clone" report within Reporting Services, within which we will perform our exercises;
• Leverage the dataSet from our previous article to "jumpstart" our practice session;
• Work with a Pie Chart data region to demonstrate user-friendly presentation options for percent of total values.
• Preview the report in Report Designer, to verify the operation of the calculations that form the scope of our practice exercise.

Percent of Total - Chart Presentation Nuances

Introduction and Business Scenario

As I related in Percent of Total - Two Perspectives, a request for assistance that I often receive, via e-mail, forums, and elsewhere, involves the need to calculate a percent of a total, primarily to determine members' contribution to greater wholes. Another common request deals with ways to show the percent of total in combination with the values themselves in a chart data region. The idea is to convey both the values, and the percents of totals they represent, together in a unified, user-friendly view.

We will illustrate this need within our usual context of a business scenario. We will assume that we have received a request, once again, from a group of information consumers in the Budget and Planning unit of the FoodMart 2000 organization. The request is for additional support in the presentation of some data, housed within the Sales sample cube, regarding USA Store Sales for 1997. The consumers wish to see, as an extension to the information we provided before in a matrix data region, the Sales values that are already available for each Store, but within a separate pie chart. They add that, ideally, the individual "pieces of the pie" will display the respective percentage total sales value (that is, percentage contribution of each Store to the total USA Store Sales), as well as showing total Sales by Store, together with the corresponding percentage contribution of each Store in a legend, which is color-keyed to the chart. Additionally, the legend entries will sort by percentage sales in descending order. The consumers feel that this presentation will afford compactness, while enhancing the understanding of the report's intended audience.

As in the previous article, the ultimate 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.

As we listen to the requirement, we realize that this is a common request. The requirement is also easily met with Reporting Services, whose general flexibility is reflected, as well, in the pie chart data region. We will first examine the satisfaction of the request of the information consumers through reference to the calculated member that we created in our last session, and then achieve the same results using an approach from within Reporting Services. The differences in the approaches are somewhat subtle, and consist predominantly of the source of our percent of total calculation. Of additional interest is the process of managing a function within the string we use to define our pie chart labels, as we shall see.

In addition to demonstrating these approaches to labeling the pie chart, we will also offer an approach whereby we can achieve the same effects via the chart's legend, as a means of presenting the data in a manner that might be clearer in scenarios where the pie sections are perhaps too small to contain the text that we wish to display. While the primary objective of our practice exercises will be to illustrate approaches to a commonly expressed need, a secondary objective is, as it was in our previous article, to illustrate the fact that we can provide solutions based upon components that reside at different layers of the integrated BI solution to meet the business requirements of our clients and employers.

Considerations and Comments

In our previous article, we created a copy of the sample Sales cube, from which we performed our practice exercises, to allow us to make changes to our cube while retaining the original sample in a pristine state. We also created a copy of the Foodmart Sales sample report, for the same reasons. In this session, we will leverage the cube, together with the dataset for the report from our last lesson, to save time in getting to the core concepts of our article. This means that we will need to access the cube and Reporting Services project that we assembled in our last session. If you are joining the series at this article, and have not completed its predecessor, I urge you to do so, both to make preparation for this article a breeze, and to gain an understanding of the important "layering" concepts that we treated in Percent of Total - Two Perspectives.