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.

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.

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

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

RS008

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

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

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.

RS008_PieChart

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

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.

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.

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

(Mine is Windows NT Integrated security.)

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

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

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.

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.

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.

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

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.

The layout should have this box appearing at its perimeter.

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.

The fields are depicted, circled, in Illustration 17, 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.

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

We return to the General tab.

The Edit Chart Value dialog box appears.

The Edit Expression dialog box appears.

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



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

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.

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

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

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

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

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.

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

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

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:

The new chart report generates, and appears as depicted in Illustration 26.

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.