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

Monday Jul 18th 2005 by William Pearson
Share:

Create a chart that presents independent percent - of - total values: one from the cube level, and one from a calculated value in the report. Use the chart legend to present combined values, as well, to meet a specific business need.

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.

NOTE: The cube, reporting project and other components we created in Mastering OLAP Reporting: Percent of Total - Two Perspectives will be required to complete the exercises we undertake in this article. Please ensure that the components we created in our last session are intact before beginning the practice exercises in this article. If necessary, create / recreate them per the steps of our last session as preparation for a successful experience within the procedures of this article.

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.

1.  Click Start.

2.  Navigate to the Microsoft Visual Studio .NET 2003 shortcut in the Programs group, as appropriate.

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

Click for larger image

Illustration 1: Beginning in Microsoft Visual Studio .NET 2003 ...

The development environment opens at the Start page.

3.  Select File --> Open from the main menu.

4.  Click Project from the cascading menu, as depicted in Illustration 2.


Illustration 2: Selecting Our Project

The Open Project dialog appears.

5.  Navigate to a location where we placed RS018.rptproj in our last article, as shown in Illustration 3.


Illustration 3: The Open Projects Dialog, with Targeted Project in Sight ...

6.  Click RS018.rptproj within the dialog, to select it.

7.  Click Open.

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.

8.  Close the Multi-Layer_Calculations report, if it is open.

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.

1.  Right-click the Reports folder within the Solution Explorer.

2.  Select Add --> Add Existing Item from the cascading context menus that appear, as shown in Illustration 4.


Illustration 4: Adding the Report to the Project ...

The Add Existing Item - RS018 dialog appears.

3.  Navigate to the location of the Multi-Layer_Calculations.rdl report that we created in our last article.

4.  Right-click Multi-Layer_Calculations.rdl inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 5.


Illustration 5: Copying the Multi_Layer_Calculations.rdl File...

6.  Right-click somewhere in the white space inside the RS018 folder, within the dialog.

7.  Select Paste from the context menu that appears, as shown in Illustration 6.


Illustration 6: Select Paste to Complete Copying ...

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

8.  Right-click the new file.

9.  Select Rename from the context menu that appears.

10.  Type the following name in place of the highlighted existing name:

PercTotalPie.rdl

NOTE: Be sure to include the .rdl extension in the file name.

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


Illustration 7: The New Report File, PercTotalPie.rdl

11.  Select the new file by clicking it, if necessary.

12.  Click Open on the dialog box to add the new report to report project RS018.

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


Illustration 8: The New Report Appears in Solution Explorer - Report Folder

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.

1.  Within the RS018 project tree in the Solution Explorer, double-click the new PercTotalPie report, to open it.

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


Illustration 9: The New Report in Layout View

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.

2.  Drag the right canvas edge to about the 11-inch mark atop the screen area.

3.  Drag the bottom edge of the canvas to about the 6-inch mark on the scale to the left of the Layout tab.

4.  Select View --> Toolbox (as shown in Illustration 10), from the main menu to place the Toolbox within easy reach (if it already appears, simply disregard this step).


Illustration 10: Calling the Toolbox to View

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


Illustration 11: The Toolbox, Pinned to the Upper Left Corner of the Design Environment

5.  Click the Chart button (at the bottom of the Toolbox pane).

6.  Hover the mouse cursor to the right of the existing matrix data region, at approximately the 6-inch point on the scale atop the Layout canvas, and at about the ½-inch point on the scale to the left of the canvas, as shown in Illustration 12.


Illustration 12: Click Approximately at the "X" Point to Begin Drawing ...

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.

7.  Starting at the point indicated on the canvas, click, and then, holding the mouse button down, drag to create a box that reaches to the bottom right corner of the canvas.

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


Illustration 13: Drawing the Box to Position the Chart

8.  Release the mouse to drop the chart item.

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.


Illustration 14: The Generic Chart Item Appears

NOTE: Should you accidentally "drop" the chart item in a manner that you wish to realign, you can simply move the item by clicking inside the chart, then pointing to the now shaded border, to drag it to a new location.

Clicking again on the border will also allow you to expand / contract the chart shape. (The nuances are easy to learn with a little practice.) Double-clicking the chart item will make the "drop regions," seen above, appear, should they be hidden.

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.


Illustration 15: The Fields Tab in My Development Environment

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.

1.  Click the dynamic tab, or otherwise open the Fields window, as appropriate to your own environment.

2.  Drag the Store_Store_Name field and drop it on the area of the chart item marked Drop Category Fields Here.

3.  Drag the Store_Sales field from the Fields window, dropping it on Drop Data Fields Here section of the generic chart item on the Layout tab.

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


Illustration 16: Field Items with Intended Drop Points (Composite View)

4.  Right-click the chart.

5.  Select Properties from the context menu that appears.

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

6.  Type the following into the Title box on the General tab:

Sales Contribution:  U.S. Stores

7.  Click Pie in the Chart type list, in the lower left corner of the tab.

8.  Ensure that the Pie chart subtype button (the left of the two that appear) to the right of the Chart type list is selected.

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


Illustration 17: Chart Properties Dialog - General Tab with Settings

9.  Click OK to close the Chart Properties dialog.

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

10.  Click the Preview button.

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


Illustration 18: Matrix and Chart Combination - Basic Pie Chart

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.

11.  Click the Layout tab.

12.  Adjust the chart region, as necessary, to expand it to the lower corner of the canvas. Do so by passing the cursor over its bottom edge, until it becomes a double-headed arrow, then clicking, to "grab" the edge), down to approximately the 6-inch point on the y-axis to the left of the canvas.

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


Illustration 19: Stretching the Chart Region to Fit the Canvas ...

13.  Right-click the chart, once again.

14.  Select Properties from the context menu that appears.

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

15.  Click the Data tab.

16.  Click the [Value] entry within the Values list (the only entry in the list), to select it.

17  Click Edit.

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.

18.  Click the Function (fx) button to the right of the Series label box.

The Expression editor appears.

19.  Type (or cut and paste) the following into the Expression pane (the right half of the Edit Expression dialog):

="$" & 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.


Illustration 20: The Newly Completed Expression Pane

20.  Click OK to accept our input and close the Expression editor.

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

21.  Click the Function (fx) button to the right of the Value box.

The Expression editor appears.

22.  Type (or cut and paste) the following into the Expression pane (the right half of the Edit Expression dialog):

=SUM(Fields!Store_Sales.Value)

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


Illustration 21: The Newly Completed Expression Pane

23.  Click OK to accept our input and close the Expression editor.

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

24.  Click the Point Labels tab of the dialog.

25.  Place a check mark in the checkbox to the immediate left of Show point labels.

26.  Click the Function (fx) button to the right of the Data label box.

The Expression editor appears.

27.  Type (or cut and paste) the following into the Expression pane (the right half of the Edit Expression dialog):

=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.


Illustration 22: The Expression Pane with Our Input

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.

NOTE: For more information about the construction of the calculated member, as well as considerations surrounding the selection of the appropriate "layer" for placement of various components within an integrated business intelligence solution, see Mastering OLAP Reporting: Percent of Total - Two Perspectives.

28.  Click OK to accept our input and close the Expression editor.

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

29.  Click the Label Style button within the bottom half of the tab.

The Style Properties dialog opens.

30.  Select "8pt" in the Size selector on the upper right of the Font tab, as depicted in Illustration 23.


Illustration 23: Select "8pt" as Font Size ...

31.  Click OK to accept our input and close the Style Properties dialog, and to return to the Point Labels tab of the Edit Chart Value dialog.

The Point Labels tab appears as shown in Illustration 24.


Illustration 24: The Completed Point Labels Tab

32.  Click OK to return to the Data tab of the Chart Properties dialog.

33.  Click the single entry in the Category groups list, whose default title is chart1_CategoryGroup1, to select it.

34.  Click Edit to open the Grouping and Sorting Properties dialog.

35.  Click the Sorting tab.

36.  In the top line of the Expression selection list, select the following expression:

=Fields!Measures_Percent_Total_Sales.Value

37.  Select Descending in the corresponding Direction selector to the immediate right of the newly selected expression.

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


Illustration 25: The Completed Sorting Tab with Our Additions

38.  Click OK to accept changes and close the Sorting tab.

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

39.  Click the Legend tab.

40.  Ensuring that the checkbox to the left of Show Legend is checked, click the Legend Style button in the lower left corner of the Legend tab.

41.  In the Size selector of the Style Properties dialog that appears, select "8pt."

The Style Properties dialog appears as shown in Illustration 26.


Illustration 26: The Style Properties Dialog with Our Setting

42.  Click OK to accept changes and return to the Legend tab.

43.  Click the Data tab.

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


Illustration 27: The Completed Data Tab

44.  Click OK to return to the Layout tab.

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:

  1. Click the Preview tab, to the right of the Layout tab atop the design surface.

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

Click for larger image

Illustration 28: The Chart Report, Preview Tab

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.

  1. Click the Layout tab, once more.
  2. Select File --> Save PercTotalPie.rdl As ... from the main menu, as depicted in Illustration 29.


Illustration 29: Saving the .rdl File for the Report

  1. Resave the file, and then exit Visual Studio.net, when desired.

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved