About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I have stated since the charter article of the series, published about the time Reporting Services was first publicly released, my conviction is that Reporting Services will commoditize business intelligence, particularly in its role as a component in an integrated Microsoft BI solution. Having been impressed from my first exposure to this exciting application, when it was in early beta, my certainty in its destiny grows stronger by the day, as I convert formerly dominant enterprise Business Intelligence systems, such as Cognos, Business Objects, Crystal, and others, to the Reporting Services architecture. I receive constant requests to conduct strategy sessions about these conversions with large organizations in a diverse range of industries - the interest grows daily as awareness of the solution becomes pervasive. Indeed, the six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

Basic assumptions underlying the series are that you have correctly installed Reporting Services, including current service packs, along with the applications upon which Reporting Services relies, and that you have access and the other rights / privileges required to complete the steps we undertake in my articles. For details on the specifics of the adjustments necessary to quickly allow full freedom to complete the exercises in this and subsequent articles, as well as important assumptions regarding rights and privileges in general, please see earlier articles in the series, as well as the Reporting Services Books Online.

About the Mastering OLAP Reporting Articles...

As I have noted in many articles and presentations, one of the first things that becomes clear to "early adopters" of Reporting Services is that the "knowledgebase" for OLAP reporting with this tool is, to say the least, sparse. (I recently heard an internal "reporting guru" say, during a BI strategy session with a major soft drink manufacturer in Atlanta, that "we didn't evaluate Reporting Services because it doesn't do cubes ...") As most of us are aware, minimal, if any, attention is given to using Analysis Services cubes as data sources for reports in the handful of books that have been published on Reporting Services to date. All are written from the perspective of relational reporting, as if with existing popular tools for that purpose. One Reporting Services book discusses OLAP reporting with Reporting Services, and then performs illustrative exercises with Office Web Components (OWC), instead. Another depicts an MDX snippet at the end of the book, as if as an afterthought. All of the early books focus entirely on relational reporting, and most make heavy use, typically enough, of the Books Online and other scraps of documentation that we already have anyway. (I could go on, but my overall opinion of the technical book industry is already well known.)

As I stated in my article, Mastering OLAP Reporting: Cascading Prompts, the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the reporting solutions of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services - at a tiny fraction of the cost. The vacuum of documentation in this arena, to date, represents a serious "undersell" of Reporting Services from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, rest assured that the OLAP potential in Reporting Services will be yet another reason that the application "commoditizes" Business Intelligence.

For more information about the Mastering OLAP Reporting articles, see the section entitled "About the Mastering OLAP Reporting Articles" in my article Ad Hoc TopCount and BottomCount Parameters.

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.

Hands-On Procedure

Preparation

Return to the Reporting Services Project

To begin, we will open the Reporting Services project, from Microsoft Visual Studio .NET 2003, that we created in our last session.

The equivalent on my PC appears as shown in Illustration 1.

The development environment opens at the Start page.

The Open Project dialog appears.

RS018.rptproj opens in the development environment, defaulting to the Layout view of the only report, Multi-Layer_Calculations.rdl, which we created in our previous lesson.

We will create a copy of this report, which will allow us to leverage the existing dataset, and to save preparation time. Making a copy for our practice session will also allow us to keep our original sample intact for other prospective uses.

Copy the Previous Report to Provide a Practice Platform

In this section, we will copy the existing RS018 report, and then open it in the Report Designer, where we will add the pie chart data region. Having the chart side-by-side with the matrix region will also provide us a quick means of verifying the accuracy of our results.

The Add Existing Item - RS018 dialog appears.

A copy of the Multi-Layer_Calculations report appears within the dialog.

PercTotalPie.rdl

The renamed copy of the Multi-Layer_Calculations report appears just below it, as depicted in Illustration 7.

PercTotalPie.rdl appears in the Reports folder, within the RS018 project tree in the Solution Explorer, as shown in Illustration 8.

Having created a copy of the functional report, we are now ready to work with the pie chart in meeting the expressed requirements of the information consumers.

Procedure

Add the Chart Item to the Layout Canvas

Recall in our last session that we "redirected" the Multi-Layer_Calculations report (which was itself a modified clone of a sample report that we installed along with Reporting Services), to a clone of the Sales sample cube. We created the cube copy, called Percent of Total, in the preparation section of the practice exercise. Within the Percent of Total cube, we created the Percent Total Sales calculated member, as support for our subsequent work with Reporting Services, where we compared and contrasted the cube-based calculation with a report-based calculated field that generated the same results.

Our focus within the previous article was the derivation of the percent of total calculation itself (in answer to many requests I receive for guidance in deriving this capability for reporting purposes), coupled with added considerations surrounding "where to put the intelligence" within an integrated BI solutions' "layers," (both within the immediate context of percent of total, and for similar calculations and capabilities in general). In this article, we will somewhat arbitrarily call upon both calculations to support our pie chart, mainly to demonstrate the mechanics for doing so for each, but keep in mind that the "home" of the support structure of the calculation remains an important consideration from many perspectives, including overall system optimization, security, and others.

Let's open the new report and get started with the chart region.

The report opens within the Report Designer, and the Layout View appears, as depicted in Illustration 9.

As we learned in my introductory article, Master Chart Reports: Pie Charts in Reporting Services, the process of building a chart report consists of dragging the chart item onto the Layout tab, and adjusting it, while setting properties as appropriate to meet report specifications. "Borrowing" the dataset that supports the existing matrix will save us preparation time, and allow us to get directly to the chart manipulation topics that we will take up in this section.

The toolbox window should appear similar to that depicted in Illustration 11. Mine is pinned to the upper left corner of the design environment, where I find it most convenient. This is, of course, subject to your own choices. (Note also, as an aside, that I dock my Fields and Server Explorer panes in this area, most of the time, to maximize design real estate.)

The cursor becomes a small chart icon in combination with crosshairs when held above the Layout canvas. This indicates that we can click to "anchor" the point, from which we wish to draw the box that the chart will inhabit.

The Layout view, with the box we have drawn, appears similar to that depicted in Illustration 13.

The chart region appears, in its generic manifestation, as shown in Illustration 14. (Don't worry about getting the lineup perfect - it is actually best to "realign" after we get done building the chart, to compensate for any changes we make in its size, for how it ultimately looks in the report as opposed to initial expectations, etc.

The chart item is now in place, and we are ready to populate it with the appropriate fields from the dataset, to endow it with the characteristics required to meet the requests of the information consumers.

Populate the Chart item to Meet the Business Requirements

Our next steps focus upon simply dragging fields from the Fields window. My Fields window is set up as a dynamic tab, and appears as depicted in Illustration 15.

The Fields window can be either fixed in place or as a dynamic tab (my usual choice, as it frees real estate), for easy access in accomplishing the next steps.

The fields are shown, circled, in Illustration 16, with arrows (in different colors) pointing to the sections into which we are dropping each.

The Chart Properties dialog box appears, defaulted to the General tab.

Sales Contribution:  U.S. Stores

The Chart Properties dialog - General tab - appears, with our settings, as shown in Illustration 17.

We will return to Chart Properties shortly. For now, let's get a feel for the basic setup that we have put into place.

The report and chart combination appear, as depicted in Illustration 18.

We note that the basic pie appears, complete with a Series legend that appears to list the individual stores. Of further significance is the fact that, although a section of the pie exists for each respective Store Name, the sections are identical in size - something that simply is not likely, with regard to the magnitude of the Stores' Sales. We will be looking at the setting that is causing this as we progress, and will even mention an opportunity in the use of the default setting that may be appropriate in some instances.

For now, we will return to the Layout tab, and take the following steps to align the chart a bit.

The chart region should "meet" the bottom of the canvas as partially depicted in Illustration 19.

The Chart Properties dialog box appears, defaulted to the General tab, once again.

The Edit Chart Value dialog appears. Here we can assign a Series label and / or Value. Both settings can be useful in helping us to deliver a desired chart presentation, as we shall see. First, let's examine the workings of the Series label.

The Expression editor appears.

="$" & FORMAT(ROUND(SUM(Fields!Store_Sales.Value),2), "#,###")

& vbcrlf & "(" & ROUND((SUM(Fields!Measures_Percent_Total_Sales.Value) * 100), 2) & "%" &  ")"

& vbcrlf &  ""

The Expression pane of the dialog appears as shown in Illustration 20.

We return to the Values tab of the Edit Chart Value dialog.

The expression within the Value box, =Count(Fields!Store_Sales.Value), explains an occurrence we noted earlier: because this default expression is a Count, it produces a value of 1, in our case, for each of the Store Sales values (there is one Store Sales value per Store). This is the reason we get equally sized sections within our pie, and, while we will change this within the current context, it can be useful to leave it as it is, and to use another means of showing the actual values (such as the legend). If the "slices" are narrow / small, they may not afford us the room to add a lengthy label, such as the combination of Store Sales and Percent Total Sales values that we added to the Series label above.

We will select an alternative here, and present only the percentage value, but this is only one possibility. (A significantly larger chart region might make more data easily presentable, for instance).

The Expression editor appears.

=SUM(Fields!Store_Sales.Value)

The Expression pane of the dialog appears as depicted in Illustration 21.

We return to the Values tab of the Edit Chart Value dialog.

The Expression editor appears.

=ROUND(((SUM(Fields!Store_Sales.Value)/ SUM(Fields!Store_Sales.Value, "ProductData")) * 100), 2)

& "%" &  ")" & vbcrlf &  ""

The Expression pane of the dialog appears as shown in Illustration 22.

Note that we have added an expression to generate a Percent of Total Sales (we would be challenged for room within the pie slices, as we shall see, for much more than the percentage). In this case, however, we are generating the percentage independently within the report - and not selecting the calculated member we created in the cube (in our last article) to generate this percentage.

We have once again encountered a scenario, similar to one that we came across in assembling our matrix in the last article, where we are confronted with a "layer" selection. While performance might be enhanced by selecting the calculated member in the cube, we are simply demonstrating that we have the option of generating it locally for purposes of our practice exercise here.

We return to the Point Labels tab of the Edit Chart Value dialog.

The Style Properties dialog opens.

The Point Labels tab appears as shown in Illustration 24.

=Fields!Measures_Percent_Total_Sales.Value

The Sorting tab appears, with our selections, as depicted in Illustration 25.

We return to the Data tab, where we will make a final cosmetic adjustment to our chart.

The Style Properties dialog appears as shown in Illustration 26.

We return to the Data tab of the Chart Properties dialog, which now appears as depicted in Illustration 27.

We are now ready to Preview the results of our handiwork.

Verify Operation of the Chart Report

Let's ascertain the accuracy and completeness of our construction efforts. We will execute the report with the following steps:

The new chart report generates, and appears as shown in Illustration 28.

We see that our new pie chart presents the data requested by the information consumers. The individual "pieces of the pie" display the percentage total sales value for each of the Stores. Moreover, total Sales by Store, together with the corresponding percentage contribution of each (in descending order), appears in the legend, which is color-keyed to the chart.

Through the forgoing steps, we have met the requirements of the information consumers within the Budget and Planning unit of the FoodMart 2000 organization. We have augmented the data presented within a pre-existing matrix region (which we created in our previous article) with a pie chart that serves multiple functions. The chart region we have constructed demonstrates the presentation of a calculation, Percent of Total, within both the body of the chart itself and its legend, combining the base value, Store Sales, with the calculated Percent of Total Sales within the chart legend, to achieve desired impact. The presentations are certainly interchangeable, as well as subject to other options; these approaches are only simple examples of the flexibility that Reporting Services offers us as authors / architects.

Of further significance is our selection of two distinct sources for the Percent of Total Sales calculation, which, as a follow-on to concepts we introduced in our previous article, demonstrates the end use of calculations that are constructed to inhabit different levels of the integrated BI solution. Our example is only a tiny illustration of the opportunities that abound in a solution that is designed and constructed to leverage the many options that exist at "multiple layers" for optimization of the overall solution. Indeed, as I have often stated in the past, multi-layered architecture requires a familiarity with all the layers to facilitate the optimal placement of each component of the "intelligence."

Conclusion ...

In this article, we returned to our examination of chart reports, this time with objectives that went beyond our introduction, earlier in the MSSQL Server Reporting Services series, Master Chart Reports: Pie Charts in Reporting Services. Following the introduction of the Percent of Total calculation (largely as an example of a business intelligence component that can be constructed at multiple levels within an integrated BI solution) we introduced in our previous article, Mastering OLAP Reporting: Percent of Total - Two Perspectives. We leveraged much of the setup required by using components created in that article in support of the practice examples we undertook in this article.

Our objective was to 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 e-mails and forum insertions. We used a pie chart, but noted that the same concepts apply, directly or indirectly, within other Reporting Services chart types. After discussing a common business need within the reporting environment, the requirement to present percent of total values within a chart data region, we focused upon the Analysis Services layer as the home of one calculation, and Reporting Services as the layer containing the second, referring to the construction of these components in our previous article.

We then returned to the project that we created in the previous article, creating a "clone" report within Reporting Services, within which we performed our practice exercises, allowing us to leverage the dataset from our previous article to "jumpstart" our practice session. We then worked with a Pie Chart to demonstrate audience-friendly presentation options for percent of total values. Finally, we previewed the report in Report Designer, to verify the operation of the calculations, labels, and other report elements that formed the scope of our practice exercise.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.