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
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.
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.
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.
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."
session we will:
Create a chart
report in Report Designer;
Locate a Chart
Item on the new report;
chart item with the required data.
use of the Data Label property;
palette for the chart report we create;
properties we can select for the pie chart.
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
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
Considerations and Comments
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).
Create a Reporting
begin, we will launch Reporting Services' Report Designer, found in Microsoft
Visual Studio .NET 2003.
the Microsoft Visual Studio .NET 2003 shortcut in the Programs
group, as appropriate.
equivalent on my PC appears as shown in Illustration 1.
Beginning in Microsoft Visual Studio .NET 2003 ...
--> New from the main menu.
from the cascading menu, as shown in Illustration 2.
Selecting a New Project
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).
Click Business Intelligence Projects in the Project Types tree, if necessary.
Project in the Templates list.
Navigate to a
location in which to place the Report Project files.
following into the Name box, leaving other settings at default:
The New Project
dialog appears, with our additions, as shown in Illustration 3.
Illustration 3: The New
Projects Dialog, with Addition
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
have now created a Report Project, and are ready to proceed with creating
the new report.
Create a New Chart Report
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.
a Blank Report
begin by creating a blank report.
Right-click the Reports
folder in Solutions Explorer.
Select Add from the
context menu that appears.
Click Add New Item from
the cascading menu, as shown in Illustration 5.
Click Report in the Add
New Item dialog.
Type the following into the Name
box, replacing the default of Report1.rdl (or similar).
The Add New Item dialog appears, as shown in Illustration
The Add New Item Dialog Initial View
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.
7: The Design Environment - Data View Tab (Compacted)
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
Let's set up
a Connection, and create a Dataset within our practice example.
Select New Dataset in
the Dataset selector at the top of the Data tab, as depicted in Illustration
As soon as we click the New Dataset selection, the Data
Link Properties dialog box appears, defaulted to the Connection tab.
Type the name of the computer
housing the targeted OLTP database, AdventureWorks2000.
(My server name, MOTHER1,
appears in this article.)
Select the radio button to the
left of the authentication option that is appropriate for your environment.
(Mine is Windows NT
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: Data Link Properties Dialog Connection Tab
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.
10: We Test Positive for Connectivity
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.
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.
11: Editing the New Dataset
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
12: Completed Dataset Dialog
OK to accept the settings and return to the Data tab, which displays
in the Generic Query Designer.
following into the SQL pane:
SELECT Vendor.VendorID AS VendorID,
StateProvince.StateProvinceCode AS StateCode
INNER JOIN VendorAddress
INNER JOIN Vendor
INNER JOIN StateProvince
WHERE (StateProvince.StateProvinceCode IN( 'WA', 'CA', 'OR'))
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).
Run button (shown in Illustration 13, atop the query we have
constructed) to ascertain that the syntax is correct.
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
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.
Click the Layout
tab to switch to the Layout view.
report edges to comfortably fill the screen area.
--> 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).
14: Calling the Toolbox to View
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.)
15: The Toolbox, Pinned to the Upper Left Corner of the Design Environment
Click the Chart
button (at the bottom of the Toolbox pane).
mouse cursor over the upper left corner of the report layout.
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 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.
layout should have this box appearing at its perimeter.
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
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
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
the Pie Chart item to Meet the Business Requirements
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.
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.
Drag the StateCode
field and drop it on the area of the chart item marked Drop Category
fields are depicted, circled, in Illustration 17, with arrows (in
different colors) pointing to the sections into which we are dropping each.
from the context menu that appears.
Properties dialog box appears, defaulted to the General tab.
following into the Title box on the General tab:
Vendors by State
Style button, which appears to the right of the Title box.
The Style Properties dialog box appears.
Make the settings,
listed in Table 1 below, within the Style Properties dialog box:
Table 1: Style Properties Dialog
Properties dialog appears, with our settings, as shown in Illustration 18.
18: Style Properties Dialog Box with Settings
to accept changes and to exit the Style Properties dialog box.
to the General tab.
Using the Palette
dropdown selector, choose Semi-Transparent.
under Chart Type in the lower left corner of the General tab.
the leftmost of the two Chart sub-types is selected.
Click the Data
tab to select it.
in the Dataset name box.
click Edit, to the right of the Values box.
Chart Value dialog box appears.
Click the Point
Labels tab to select it.
place a checkmark, in the checkbox to the left of Show point labels.
Click the Expression
button (pictured in Illustration 19) to the immediate right of the Data
label selector box.
The Expression Button
Expression dialog box appears.
following into the Expression area:
=Fields!StateCode.Value & vbcrlf & "(" &
CSTR(Count(Fields!VendorID.Value)) & ")"
Expression dialog appears as shown in Illustration 20.
20: The Edit Expression Dialog with our Input
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.
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
to accept input.
Expression dialog box closes, returning us to the Point Labels tab.
Click the Label
Style button in the lower left section of the Point Labels tab.
The Style Properties dialog box appears.
Make the settings,
listed in Table 2 below, within the Style Properties dialog box:
Table 2: Style Properties Dialog
Properties dialog appears, with our settings, as depicted in Illustration
21: Style Properties Dialog Box with Settings
to accept settings.
Properties box closes. We return to the Edit Chart Value dialog box,
which now appears as shown in Illustration 22.
22: The Edit Chart Value Dialog Box
to close the Edit Chart Value dialog.
to the Chart Properties dialog box, Data tab.
Click the Legend
tab to select it.
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.
tab appears, with our settings, as depicted in Illustration 23.
21: Legend Tab with Settings
Click the 3-D
Effect tab to select it.
Effect tab appears, with our settings, as depicted in Illustration 24.
Illustration 24: 3-D
Effect Tab with Settings
checkbox to the immediate left of Display Chart with 3-D Visual Effect,
to place a checkmark there.
Click the General
tab to review settings.
tab appears, at this stage in our process, as shown in Illustration 25.
25: The General Tab with Settings
to accept all the settings we have made in the Chart Properties dialog
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.
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.
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.