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

July 18, 2005

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.