MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services

Monday Aug 30th 2004 by William Pearson
Share:

Join MSAS Architect Bill Pearson as he begins a set of articles surrounding the rich Reporting Services chart features. In this article, we meet an illustrative business need with a pie chart.

About the Series ...

This is the eighth article of the series MSSQL Server 2000 Reporting Services. The series is designed to introduce MSSQL Server 2000 Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. I will also use the column as a vehicle for sharing my conviction in Reporting Services' role as a new paradigm in enterprise reporting. As I advise clients on a more and more frequent basis these days, this is the future in a big way. I hope you will consider my input valuable, and that you will investigate closely the savings and advanced functionality that will soon be available to anyone with an MSSQL Server 2000 (and beyond) license.

Important: For information concerning the applications to which you will require access to benefit the most from our series, please see our initial Database Journal article, A New Paradigm for Enterprise Reporting.

For many of the articles in this series, it is assumed that you have prepared security to allow "power user" status in virtually every regard. 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 our series, as well as the Reporting Services Books Online.

Overview

Reporting services enables us to present both summarized and detailed data in colorful, easy-to-read charts of various types. The Master Chart Reports subset of MSSQL Server 2000 Reporting Services series will demonstrate how to create chart reports of various types, and how to use the abundant features contained in Reporting Services that enable us to use them to make report data more meaningful and easier to understand. We can choose from a number of chart layouts and types within the Reporting Services chart data region options. (A data region is an area on a report that contains data from a data source that is repeated. The types of data regions are list, matrix, table, and chart.)

We can also format chart objects in a host of ways, drill down to see the details behind the graphical summaries, combine chart reports with other types of reports, , and leverage myriad other options in the powerful Reporting Services tool set.

While the Books Online give step-by-step instructions for assembling charts, in some cases (in a handful of tutorials of somewhat limited scope), this digital documentation focuses more on definitions and purposes of fields and settings than on building a specific kind of report from scratch. This non-linear approach is often great for context-sensitive help, when we need a reminder or have a question regarding "what exactly does the system want in this field?" or "what are my options here?" and so forth. This sort of documentation is usually quite helpful from the perspective of a report author who already has a general idea of the steps involved in creating a report. However, the issue with a non-linear documentation system, and an issue that has become more and more pervasive, as applications have evolved documentation to online formats, is that it does not necessarily provide a quick means of learning overall, start-to-finish procedures, before homing in on specific setting options of interest. All information is, in effect, contained in a general pool, organized only in a multidirectional, hyperlink manner.

The focus in most of my articles is a full set of, albeit sometimes simple, procedures that are designed to underlie a more in-depth study of specific property settings and so forth in subsequent articles. My objective is to allow a reader to complete a report, or a report component, in a manner that is insulated from non-linear distractions.

In this article, we will begin our exploration of chart reports with an examination of the humble pie chart. While virtually all of us have interacted with these kinds of charts before, (if not in the context of report authoring, then almost certainly as an information consumer), we will find that the pie chart item in Reporting Services is both feature-rich and easy to use. The various chart types in Reporting Services have different properties (and different dialog boxes, as a result) because of a wide array of features. The pie chart is a good place to begin a review of the chart types, because it contains many of the basic features common to most chart types, but not an effusive number that are highly "pie-chart specific."

In this session we will:

  • Create a chart report in Report Designer;
  • Create an underlying dataset;
  • Locate a Chart Item on the new report;
  • Populate the chart item with the required data.
  • Practice the use of the Data Label property;
  • Modify the palette for the chart report we create;
  • Examine other properties we can select for the pie chart.
  • Preview the report to verify its operation.

Create a Pie Chart Report in Reporting Services

Objective and Business Scenario

In the following sections, we will perform the steps required to create a pie chart report to meet a business need as expressed by a hypothetical group of organizational information consumers. We will base our report datasets on the AdventureWorks2000 sample OLTP database that accompanies the installation of Reporting Services.

For purposes of our practice procedure, we will assume that information consumers within the Purchasing department of the AdventureWorks2000 organization have expressed the need for a pie chart report. The consumers have stated that they intend to use it within various other Microsoft Office applications, including PowerPoint presentations, Word documents, and, ultimately, within other reports. They need a report that presents the concentration of our organizational vendors, by state, for numerous analytical ventures. To begin, however, they are interested in only three states: Washington, California, and Oregon. These states, they tell us, contain the lion's share of our vendors.

Once we understand the business need, we begin the process of creating the chart report to satisfy the information consumers.

Considerations and Comments

The report that we will create involves the sample MSSQL Server 2000 database, AdventureWorks2000, which accompanies the installation of Reporting Services. At the time of writing, the Service Pack 1 update is assumed for Reporting Services and the related Books Online and Samples.

For purposes of this exercise, we will create a Reporting Services project within the Visual Studio.Net 2003 Report Designer environment, within which we will work primarily with a Chart data region. While the construction of a pie chart is simple enough to follow, ensure that you have the authority, access and privileges, within both MSSQL Server and Reporting Services, needed to establish a data connection and accomplish the process and that performing these operations within the AdventureWorks2000 database presents no other issues in your environment.

If the sample AdventureWorks2000 database was not created as part of the initial Reporting Services installation, or was removed prior to your beginning this article, please see the Reporting Services documentation, including the Installation Notes, for the procedure to create the database, and direction to the appropriate files. As of this writing, a copy of the samples can be obtained from the installation CD or via download from the appropriate Microsoft site(s).

Hands-On Procedure

Preparation

Create a Reporting Services Project

To begin, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

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.


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

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

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


Illustration 2: Selecting a New Project

The New Project dialog appears. We note that Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services (the folder was added by the installation of Reporting Services, as it established the Report Designer in Visual Studio .NET).

5.  Click Business Intelligence Projects in the Project Types tree, if necessary.

6.  Click Report Project in the Templates list.

7.  Navigate to a location in which to place the Report Project files.

8.  Type the following into the Name box, leaving other settings at default:

RS008

The New Project dialog appears, with our additions, as shown in Illustration 3.


Illustration 3: The New Projects Dialog, with Addition

9.  Click OK.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 4.


Illustration 4: The New Project Appears in the Solution Explorer

We have now created a Report Project, and are ready to proceed with creating the new report.

Create a New Chart Report

In this section, we will launch Reporting Services' Report Designer, and then create a new report with a dataset. Next, we will place the chart item on the report. Finally, we will populate the report item.

Create a Blank Report

Let's begin by creating a blank report.

1.  Right-click the Reports folder in Solutions Explorer.

2.  Select Add from the context menu that appears.

3.  Click Add New Item from the cascading menu, as shown in Illustration 5.

Click for larger image

Illustration 5: Select Add ---> Add New Item

4.  Click Report in the Add New Item dialog.

5.  Type the following into the Name box, replacing the default of Report1.rdl (or similar).

RS008_PieChart

The Add New Item dialog appears, as shown in Illustration 6.


Illustration 6: The Add New Item Dialog Initial View

6.  Click the Open button at the bottom of the Add New Item dialog.

The design environment opens. We see the Data, Layout and Preview tabs appear in the Report Designer (I have docked many of my toolbars in places I find convenient, and so your environment may differ somewhat). The report has opened in Data View, as shown in Illustration 7.


Illustration 7: The Design Environment - Data View Tab (Compacted)

Set up a Data Connection and Create a Dataset

Our next step is to set up a Data Connection. Reporting Services can connect with, and create the datasets it needs from, virtually any ODBC or OLE DB-compliant data source (in addition to the obvious MSSQL Server and MSAS data stores). .NET-based API's add the potential for other data sources, assuming that you have a legacy, or otherwise eccentric, scenario on your hands.

Let's set up a Connection, and create a Dataset within our practice example.

1.  Select New Dataset in the Dataset selector at the top of the Data tab, as depicted in Illustration 8.

Click for larger image

Illustration 8: Select New Dataset in the Dataset Selector Data Tab

As soon as we click the New Dataset selection, the Data Link Properties dialog box appears, defaulted to the Connection tab.

2.  Type the name of the computer housing the targeted OLTP database, AdventureWorks2000.

(My server name, MOTHER1, appears in this article.)

3.  Select the radio button to the left of the authentication option that is appropriate for your environment.

(Mine is Windows NT Integrated security.)

4.  Select AdventureWorks2000 within the Select the database on the server selector.

The settings on the Connection tab of the Data Link Properties dialog should resemble those shown in Illustration 9.


Illustration 9: Data Link Properties Dialog Connection Tab

5.  Click the Test Connection button to verify connectivity to the data source.

We receive a message box, indicating a successful test connection, as shown in Illustration 10.


Illustration 10: We Test Positive for Connectivity

6.  Click OK to accept the settings we have made, and to close the Data Link Properties dialog.

Report Designer next presents us with the dataset design tool, based upon our newly connected source. We are immediately positioned to design our query, which brings us to the next step.

7.  Click the ellipses ("...") button to the right of the default dataset name of AdventureWorks2000, which appears in the Dataset selector, as shown in Illustration 11.


Illustration 11: Editing the New Dataset

8.  Enter VendorStateAllocation for the name of the Dataset dialog box, replacing the default name of AdventureWorks2000.

(AdventureWorks2000 remains selected for the data source by default.) The Dataset dialog appears as shown in Illustration 12.


Illustration 12: Completed Dataset Dialog

9.  Click OK to accept the settings and return to the Data tab, which displays in the Generic Query Designer.

10.  Type the following into the SQL pane:

SELECT Vendor.VendorID AS VendorID,
		
		StateProvince.StateProvinceCode AS StateCode

FROM Address 

INNER JOIN  VendorAddress 

ON Address.AddressID 

= VendorAddress.AddressID 

INNER JOIN Vendor 

ON VendorAddress.VendorID 

= Vendor.VendorID 

INNER JOIN StateProvince 

ON Address.StateProvinceID 

= StateProvince.StateProvinceID
	    
     WHERE (StateProvince.StateProvinceCode IN( 'WA', 'CA', 'OR'))

As we have noted in earlier articles in the MSSQL Server 2000 Reporting Services series, we might have constructed the above query using the Query Builder. Query Builder's graphical tools are very helpful when we are unfamiliar with the database we are querying, or if we are learning the syntax of SELECT queries. Because it is more efficient to simply type the query into the SQL pane of the Dataset dialog box, or in the Generic Query Designer, we will take this route in many articles to conserve time and space. (Some queries cannot be created through the Query Builder, due to complexity or other complications, but the tool will serve many of us well, in most cases).

Click the Run button (shown in Illustration 13, atop the query we have constructed) to ascertain that the syntax is correct.


Illustration 13: Running the Query (Compact View, Run Button Circled)

As we can see, the resulting dataset contains a simple list of Vendor IDs, together with the respective States in which they are located. We will rely upon this dataset to populate our pie chart in the next section. The VendorIDs will be counted to generate number of vendors in each State (or Category, in chart property parlance), as we shall see.

Locate the Pie Chart Item on the Blank Report

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 the report specifications.

1.  Click the Layout tab to switch to the Layout view.

2.  Drag the report edges to comfortably fill the screen area.

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


Illustration 14: Calling the Toolbox to View

The toolbox window should appear similar to that shown in Illustration 15. 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 that I dock my Fields and Server Explorer panes in this area to maximize design real estate, as an aside.)


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

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

5.  Place the mouse cursor over the upper left corner of the report layout.

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

6.  Starting in the upper left corner of the layout body, click, and then, holding the mouse button down, drag to create a box that covers the report layout.

The layout should have this box appearing at its perimeter.

7.  Release the mouse to drop the chart item.

The chart item appears, in its generic manifestation, as shown in Illustration 16. Because the chart is the only item we intend to place in the report, it should extend almost completely over the report layout.


Illustration 16: 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.

The chart item is now in place, and we are ready to specify its "pie" nature, as well as to populate it with the dataset that we have created.

Populate the Pie Chart item to Meet the Business Requirements

Our next steps focus upon simply dragging fields from the Fields window. Ensure that the Fields window appears, either fixed in place or as a dynamic tab (as mine appears in Illustration 15 above), for easy access in accomplishing the next steps.

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

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

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

Click for larger image

Illustration 17: Field Items with Intended Drop Points

3.  Right-click the chart.

4.  Select Properties from the context menu that appears.

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

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

Vendors by State

6.  Click the Style button, which appears to the right of the Title box.

The Style Properties dialog box appears.

7.  Make the settings, listed in Table 1 below, within the Style Properties dialog box:

Property

Setting

Family

Verdana

Size

14pt

Style

Normal

Weight

Bold

Color

Dark Green

Decoration

None

Table 1: Style Properties Dialog

The Style Properties dialog appears, with our settings, as shown in Illustration 18.


Illustration 18: Style Properties Dialog Box with Settings

8.  Click OK to accept changes and to exit the Style Properties dialog box.

We return to the General tab.

9.  Using the Palette dropdown selector, choose Semi-Transparent.

10.  Select Pie under Chart Type in the lower left corner of the General tab.

11.  Ensure that the leftmost of the two Chart sub-types is selected.

12.  Click the Data tab to select it.

13.  Select VendorStateAllocation in the Dataset name box.

14.  Highlighting [Value], click Edit, to the right of the Values box.

The Edit Chart Value dialog box appears.

15.  Click the Point Labels tab to select it.

16.  Click, to place a checkmark, in the checkbox to the left of Show point labels.

17.  Click the Expression button (pictured in Illustration 19) to the immediate right of the Data label selector box.


Illustration 19: The Expression Button

The Edit Expression dialog box appears.

18.  Type the following into the Expression area:

=Fields!StateCode.Value & vbcrlf & "(" & 

        CSTR(Count(Fields!VendorID.Value)) & ")"

The Edit Expression dialog appears as shown in Illustration 20.


Illustration 20: The Edit Expression Dialog with our Input

By typing in the expression above, we are leveraging the data label to perform two functions: First, we are generating a State abbreviation (recall we are focusing on Washington, California, and Oregon) to label the sections of the chart. Second, we are adding a count of vendors within each respective section. The delivery of this data is, in effect, adding another conceptual dimension to the information we are imparting.

TIP:

We used "&" in the expression to concatenate each State abbreviation (always separate the "&" character from others by a single space on both sides) with the respective count of the vendors within each, adding a line break (via the old carriage return-line feed keyword, vbcrlf, ) to separate the two. (Using vbcrlf in this manner is a great way to make what would normally appear on a single line separate into two or more lines.

19.  Click OK to accept input.

The Edit Expression dialog box closes, returning us to the Point Labels tab.

20.  Click the Label Style button in the lower left section of the Point Labels tab.

The Style Properties dialog box appears.

21.  Make the settings, listed in Table 2 below, within the Style Properties dialog box:

Property

Setting

Family

Verdana

Size

11pt

Style

Normal

Weight

Bold

Color

Black (Default)

Decoration

None

Table 2: Style Properties Dialog

The Style Properties dialog appears, with our settings, as depicted in Illustration 21.


Illustration 21: Style Properties Dialog Box with Settings

22.  Click OK to accept settings.

The Style Properties box closes. We return to the Edit Chart Value dialog box, which now appears as shown in Illustration 22.


Illustration 22: The Edit Chart Value Dialog Box

23.  Click OK to close the Edit Chart Value dialog.

We return to the Chart Properties dialog box, Data tab.

24.  Click the Legend tab to select it.

25.  Uncheck Show Legend.

As we shall see, our design will alleviate the need for a legend of the garden variety. Our expression in the Data Label above causes the placement of a label directly upon each section of the chart.

The Legend tab appears, with our settings, as depicted in Illustration 23.


Illustration 21: Legend Tab with Settings

26.  Click the 3-D Effect tab to select it.

The 3-D Effect tab appears, with our settings, as depicted in Illustration 24.


Illustration 24: 3-D Effect Tab with Settings

27.  Click the checkbox to the immediate left of Display Chart with 3-D Visual Effect, to place a checkmark there.

28.  Click the General tab to review settings.

The General tab appears, at this stage in our process, as shown in Illustration 25.


Illustration 25: The General Tab with Settings

29.  Click OK to accept all the settings we have made in the Chart Properties dialog box.

The Chart Properties dialog closes, returning us to the generic chart item in Report Designer, Layout tab.

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 depicted in Illustration 26.

Click for larger image

Illustration 26: The Chart Report, Preview Tab

2.  Click the Layout tab, once more.

3.  Select File --> Save RS008_PieChart.rdl As ... from the main menu, as shown in Illustration 27.


Illustration 27: Resaving the Report .Rdl File

4.  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 AdventureWorks2000 Purchasing department. We have provided a pie chart report, which will lend itself readily to use in other Microsoft Office applications, and, ultimately, within other reports. We have also met the expressed need to analyze the concentration of our organizational vendors, by State, filtering upon the three specific states of interest.

Conclusion...

In this article, we began our exploration of chart reports with an examination of one of the simplest, the pie chart. We stated that the pie chart is a good place to begin a review of the chart types, because it contains many of the basic features common to most chart types, without the distractions of many properties that appear in other chart types. We illustrated the use of the chart item within an illustrative exercise that involved helping a group of hypothetical information consumers meet a stated business need.

We created a blank report in Report Designer, added an underlying dataset, and then located a chart item onto the new report. We then populated the chart item with data, making use of the Data Label property, along with other properties, to add informational value to the report we created. Finally, after constructing the dataset and building and populating the chart report, we previewed the report and verified its operation as a whole.

» 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