MSAS Architect Bill Pearson leads the hands-on creation of a somewhat more advanced Reporting Services pie chart, using dual category groups and a series group based upon an Analysis Service data source.
This article examines additional capabilities we can employ
in creating advanced Pie charts with Reporting
Services. It extends the examination of simple Pie charts that
we undertook in Simple
Pie Chart for Analysis Services Data, and focuses on the creation of a
somewhat more sophisticated Pie chart than we created in the earlier
article, using dual category
groups and a series group, among other nuances, again based upon an Analysis
Service data source. This article continues the examination of Reporting Services charts for Analysis Services data sources that we began in
an earlier article of my MSSQL Server Reporting Services series, Introducing
Reporting Services Charts for Analysis Services. In that article we summarized
the many different chart (or chart
data region) types that are available, and looked ahead to
individual articles surrounding each type, where we would specify details and
real world innovations involving the use of each in reporting Analysis
Services data. We noted that the focus of these related articles,
interspersed among other topics within my MSSQL
Server Reporting Services series over time, would be the design
and creation of Analysis Services chart reports of
various types, and the exploitation of the rich and flexible features contained
in Reporting Services that enable
us to make report data more meaningful, and easier to understand, from the
perspectives of our information consumer audiences.
In
many cases, which I try to outline in my articles at appropriate junctures, the
functionality of well-established, but expensive, solutions, such as Cognos (PowerPlay,
Impromptu, and other applications), Business Objects, and myriad
other reporting / OLAP applications can be met by Reporting Services
at a tiny fraction of the total cost of ownership. And the flexibility and
richness of the chart data regions alone in Reporting Services exceed,
in many ways, the rather fixed options available in other enterprise reporting
solutions.
As I
have repeated in many of my articles in this column, one of the first things
that become clear to early adopters of Reporting Services is that the
knowledgebase for Analysis Services reporting with this tool is, to
say the least, sparse. The vacuum of documentation in this arena, even
taking into consideration the release of several books surrounding Reporting
Services in recent years, continues to represent a serious undersell of Reporting
Services, from an Analysis Services reporting perspective. I hope
to contribute to making this space more accessible for everyone, and to share
my implementation and conversion experiences as the series evolves. In the
meantime, we can rest assured that the Analysis Services potential in Reporting
Services will contribute significantly to the inevitable commoditization of
business intelligence, via the integrated Microsoft BI solution.
Note: For more
information about my MSSQL Server Reporting Services column in
general, see the section entitled About the MSSQL
Server Reporting Services Series that follows the
conclusion of this article.
Overview
As we observed in Introducing Reporting Services Charts
for Analysis Services, Reporting
Services enables us to present both summarized and detailed data in
colorful, easy-to-read charts of various
designs, from which we can chose the layout and type that best meets any given
business requirement. We learned, in Simple Pie Chart for Analysis Services
Data, that, among the types offered, the simple Pie chart type
is perhaps among the most popular. In this article, we will extend our
examination of the Pie chart type
and get some hands-on exposure to the creation of an enhanced Pie chart,
continuing to explore general characteristics. This will serve as a basis for
other, more in-depth, practical exercises in coming articles, where we will extend
the value of our chart-enhanced reports in
myriad ways. Among these ways, just for starters, are the capability to format
chart and other objects within a host of
options, to drill down to see the details behind the
graphical / numerical summaries, to combine
chart reports with other types of reports,
and to access many other options in the powerful Reporting
Services tool set.
My objective within this article is to assist the reader in
quickly assembling a report containing a working, enhanced Pie chart
(relying upon, for instance, already assembled datasets and other
underlying support within an existing sample report), and to move efficiently
into targeted reporting nuances that meet real world needs. While this initial
introduction will focus more on the creation of a relatively straightforward,
albeit enhanced, Pie chart, the
report we create will serve as a basis, in prospective articles, to demonstrate
more detailed intricacies that I have found useful in meeting business
requirements of my own clients and readers. The ultimate objective, as is
typically the case within my various series, is to provide hands-on
opportunities to learn overall, start-to-finish procedures, before homing in on
specific options of interest (although we will certainly deal with many of
these options in even our early exercises, as a part of completing the stated
objectives of these sessions).
Introducing Pie Charts for Analysis Services
In
Introducing
Reporting Services Charts for Analysis Services, we learned that the Pie chart type is available in
the following variants:
In
this article we will focus upon the Simple Pie variant, although we will
enhance the Pie chart beyond the simple Pie chart that we created
in Simple Pie
Chart for Analysis Services Data. (We take up the Exploded Pie variant within
a relevant context in a sister article of the MSSQL Server Reporting Services series.)
We noted in our introductory article that Pie
charts are typically used to present percentages (as in percent
composition of a whole, etc.). In generally describing the type, we observed
that the Pie chart presents value data as percentages
of the whole. Categories are represented by individual slices. The size
of the slice is determined by the value.
In this article, we will explore an
enhanced Pie chart data region in detail, and gain practical exposure to
the creation of a basic example of such a chart that is employed in reporting from
an Analysis
Services
data source. In introducing the enhanced Pie chart, we
will:
-
Perform a
brief overview of the Pie chart type, discussing its variants and
typical uses;
-
Open the
sample Report Server project, AdventureWorks Sample Reports, and ascertain
connectivity of its shared Analysis Services data source;
-
Create a clone
of an existing sample Analysis Services report, containing a matrix data
region, with which to launch our overview;
-
Examine the Pie
chart type from the standpoint of the existing report, noting how we
add it to an open report (and thus save time in leveraging existing datasets
and other support structures) in the Layout tab;
-
Modify the
existing primary dataset within the sample report clone, adding a filter
to limit the size of the data presentation;
-
Make
modifications to the report layout to support the stated client
reporting needs and practice session objectives;
-
Create a
complete, working sample of an enhanced Pie chart data region, within
the existing report, and alongside a pre-existing, populated matrix data
region, which will allow us to verify its accuracy and completeness once we
have the chart in place;
-
Examine all
relevant property settings within each of the General, Data,
X Axis, Y Axis, Legend, 3D Effect and Filters
tabs;
-
Discuss the
results obtained with the development techniques that we exploit throughout our practice session.
Objective and Business Scenario
In this article, we will
continue to perform a relatively straightforward examination of the Pie chart type, from within
a copy of an existing sample Reporting Services 2005 report that we will
create for this purpose. Our focus will be to create a working, enhanced Pie
chart, using an Analysis
Services
data source (the
Adventure Works DW sample OLAP database / Adventure Works cube
that accompanies the installation of Reporting Services), while discussing various characteristics
of this chart type as we progress.
We will examine relevant chart properties, and get further hands-on
exposure to the manipulation of those properties to support the delivery of
information to meet the needs of a hypothetical group of organizational information
consumers.
Other articles within the MSSQL Server Reporting Services series will advance beyond
the practice session that we undertake here, using both the simple Pie chart we created in Simple Pie Chart for
Analysis Services Data and the enhanced Pie chart which we will create in this
article, as a basis from which we can concentrate on in-depth procedures and
nuances that we can use to achieve precision in meeting specific requirements,
and to deliver data presentation effects, that we might encounter within the
environments of our respective employers and / or clients.
The Business Need
For purposes of our
practice procedure, we will assume that a group of report developers and
analysts, composed of members of the Sales, Marketing, Information
Technology, and other departments of the Adventure Works
organization, have expressed the need to present some of the information displayed
in the existing Sales
Reason Comparisons OLAP
report through a new
Pie chart report. The group has stated that they want to leverage this
conversion process to learn more about the construction and characteristics
of an enhanced Pie chart report in general. Moreover, they assure us
that they will extrapolate the techniques they learn to scenarios where they
will design, create and deploy reports of this type in the future.
Once
we understand the business need, we propose using a copy of the existing Sales Reason Comparisons report (which, among other samples,
accompanies the installation of Reporting Services). Our tandem
objectives here, we explain, are 1) to streamline our procedures (by using existing connections, datasets,
and other structures that are already in place within the pre-existing report),
and 2) to provide a ready means of verifying at least some of the accuracy and
completeness of the new report (a feature that might be useful in initial
report testing). We mention, as an aside, that we can always delete the
existing matrix data region prior to deploying the final report.
Once
we obtain agreement on this approach, we begin the process of creating the enhanced Pie chart
report to satisfy the information consumers.
Practice
Our
first objective is to create a copy of the Sales Reason Comparisons sample report, within which we will perform the
addition and setup of a Pie chart data region, for the reasons noted
above. (We typically work with a similar report copy in articles that are
focused on the individual chart types elsewhere in the series.) We will perform our practice
session from inside the MSSQL Server Business Intelligence Development
Studio. For more exposure to the Business Intelligence Development
Studio itself, and the myriad design, development and other evolutions we
can perform within this powerful interface, see other articles in this column,
as well as within my Database Journal series Introduction to MSSQL
Server Analysis Services. In this article, we will be commenting only on
the features relevant to our immediate practice exercise, to allow us to keep
to the focus of the article more efficiently.
Preparation: Create a Clone Report within the Reporting Services Development Environment
For purposes of our practice
session, we will create a copy of the Sales Reason Comparisons report, one of several samples that are available
with (and installable separately from) the MSSQL Server 2005 integrated
business intelligence suite. Making preparatory modifications, and then making
the enhancements to the report to add the functionality that forms the subject
of our lesson, can be done easily within the Business Intelligence Studio
environment. Working with a copy of the report will allow us the luxury of
freely exploring our options, and will leave us with a working example of the
specific approach we took, to which we can refer in our individual business
environments.
Open the Sample Report Server Project
For purposes of our
practice session, we will open the AdventureWorks Sample Reports
project, which contains the sample reports that ship with the Reporting
Services component of the MSSQL Server 2005 suite. We will complete
our practice session within the sample project, so as to save the time required
to set up a development environment from scratch within the Business
Intelligence Development Studio.
To open the AdventureWorks
Sample Reports project, please see the following procedure in the References
section of my articles index:
Ascertain
Connectivity of the Shared Data Source
Lets
ensure we have a working data source. Some of us may be running side-by-side
installations of MSSQL Server 2000 and MSSQL Server 2005. This
means that our installation of the latter will need to be referenced as a server
/ instance combination, versus a server name alone. (The default for
the Adventure Works DW project samples connection is localhost,
which will not work correctly in such a side-by-side installation, as MSSQL
Server 2000 will have assumed the identity of the local PC by default.)
If you do not know how
to ascertain or modify connectivity of the Analysis Services data
source, please perform the steps of the following procedure in the References
section of my articles index:
Create
a Copy of the Sales Reason Comparisons Report
We will begin with a copy
of the Reporting Services 2005 Sales Reason Comparisons OLAP report,
which we will use as a basis for our Column chart types practice
exercise. Creating a
clone of the report means we can make changes to select components (perhaps
as a part of later exploration with our independent solution), while retaining
the original sample in a pristine state for other purposes, such as using it to
accompany relevant sections of the Books Online, and other
documentation. Such uses may form a part of learning more about Reporting
Services (particularly about an OLAP report using an Analysis Services data
source), and other components of the Microsoft integrated business
intelligence solution in general.
If you do not know how
to create a copy of an existing report, please perform the steps of the
following procedure in the References section of my articles index:
We now
have a clone Analysis Services report file within our Reporting
Services 2005 Project, and are ready for our hands-on practice session with
the enhanced Pie chart type, which we will begin in the next section.
Preparation: Modify the Analysis Services Report Clone for Use within Our Practice Session
We will
next make a few modifications to prepare the report for our practice session. Lets open the report clone we
created above in Layout view (for those of us not already there), upon
which we can commence our overview steps.
1.
Right-click DBJ_OLAP_Report.rdl
in the Solution Explorer.
2.
Select Open
from the context menu that appears, as shown in Illustration 1, as
necessary.
Illustration 1: Opening the New Report ...
DBJ_OLAP_Report.rdl
opens in Layout
view.
Filter
the Primary Dataset to Limit the Data Retrieved
First,
we will modify the primary dataset underlying the existing report,
called ProductData. We will simply place a filter on returned
data to limit the size of our ultimate report to two (versus four) Calendar
Years activity.
1.
Click the Data
tab.
2.
Ensure that
the ProductData dataset appears within the data selector.
3.
On the Metadata
tab, within the pane that appears under the dataset selector (which now
indicates ProductData), expand the Date dimension by clicking the
+ sign to its immediate left.
4.
Expand the Calendar
folder that appears underneath the newly expanded Date dimension.
5.
Click the Date.Calendar
Year hierarchy to select it.
6.
Drag the Date.Calendar
Year hierarchy to the Query pane, dropping it to the immediate left
of the Sales Reason column that is already in place.
The Query
pane, with the newly added Calendar Year column, appears as partially
presented in Illustration 2.
Illustration 2: The Query Pane with the Newly Added Column (Partial View)
7.
Click the Edit
Selected Dataset (...) button to the immediate right of the Dataset
selector, as depicted (circled) in Illustration 3.
Illustration 3: Editing the Dataset ...
The
multi-tabbed Dataset dialog opens.
8.
Click the Filters
tab.
9.
In the first
row of the Filters box, within the leftmost Expression column,
select =Fields!Calendar_Year.Value, as shown in Illustration 4.
Illustration 4: Select =Fields!Calendar_Year.Value in the Expression Column ...
10.
In the Operator
column (to the immediate right of the Expression column), select the =>
operator.
11.
Type (or cut
and paste) the following into the Value column (to the immediate right
of the Value column):
="CY 2003"
Because
we are stating that we want only Calendar Years greater than or equal
to CY 2003, we know we will retrieve only two years data into
the dataset (the Adventure Works cube contains Calendar Years 2001 through
2004).
The Filters
tab of the Dataset dialog appears as presented in Illustration 5.
Illustration 5: The Filters Tab of the Dataset Dialog with Our Additions ...
12.
Click OK
to accept our addition, and to dismiss the Dataset dialog.
Modify
the Report Layout to Accommodate Our Focus
Next,
we will make some changes to the report layout to more easily accommodate the
focus of our practice session. We will start with a larger report canvas.
1.
Click the Layout
tab atop the Report Designer.
2.
Click the report
body at some point below the matrix data region that is in place.
The Body
bar, atop the report canvas, assumes the focus (becomes darker). Body
also appears in the Properties pane (by default to the lower right of
the design environment). The point here is to ascertain that the report
body is, indeed, selected.
3.
Pass the
cursor over the lower edge of the report body, until it becomes a
two-headed arrow.
4.
Enlarge the
report canvas to about the 6-1/2 point on the scale on the left side of
the Layout tab, stretching it downward to create empty space below the matrix
data region on the report body, as depicted in Illustration 6.
Illustration 6: Stretch the Canvas Downward to Create Empty Space in the Report Body
Because,
from this point, we will be performing procedures that relate only to the chart
type with which we are working in this article, and because we create similar
files for different chart types in other articles, lets rename the .rdl file
to clearly associate it with this article and the Pie chart type.
5.
Select File
from the main menu.
6.
Select Save
DBJ_OLAP_Report.rdl As ....
7.
Type the
following into the File name box of the Save File As box that
appears next:
RS062_Pie_Chart
8.
Click the Save
button in the lower right corner of the Save File As box.
The Save
File As box is dismissed and we see the new name appear in the Solution
Explorer. We are now ready to begin the procedural section of our practice
session.
Procedure: Create a Pie Chart within the Report
Add a Chart Control to the Report Canvas
At
this point, we will
select the Chart item from the Report Items listed within the Toolbox
pane (the position of which is defaulted to the left of the Layout tab),
for inclusion within our report. Once the general control is placed on the report canvas, we will
adjust it, setting properties as appropriate to meet the report specifications.
1.
Select View
from the main menu.
2.
Select Toolbox from the menu, which appears as partially shown in Illustration 7, to place
the Toolbox within easy reach (if it already appears, simply disregard
this step).
Illustration 7: Calling the Toolbox Into View (Partial View Menu Shown)
The toolbox
window should appear similar to that depicted in Illustration 8. Mine
is pinned to the left side of the design environment, where I find it most
convenient. This is, of course, subject to your own choice. (I also dock my Fields
and Server Explorer panes in this area to maximize design real
estate, as an aside.)
Illustration 8: The Toolbox, Pinned to the Left Side of the Design Environment
3.
Click the Chart
item (at the bottom of the Toolbox pane).
The
cursor becomes a small chart icon, in combination with crosshairs, when held
above the report canvas. This indicates that we can click to anchor the
point, from which we wish to define the space that the chart will inhabit.
4.
Click on the
canvas, below the existing matrix, approximately as shown in Illustration 9.
Illustration 9: Placing the Chart Selection onto the Report Campus ...
The chart item appears, in its generic manifestation,
as depicted in Illustration 10.
Illustration 10: The Generic Chart Item Appears
NOTE: Should you accidentally place or drop the chart item
into a position that is not satisfactory, you can simply move the item by
clicking inside the chart item, then pointing to the then - shaded
border of the chart item, 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.
5.
Click the
canvas, outside the chart item.
6.
Click the
chart item, once again, to make it appear within a simple, bordered box.
7.
Pass the
cursor over the left border of the chart item, until it becomes a four-arrow
icon.
8.
Click and drag
the chart item to the left side of the report canvas.
9.
Click the
chart item, once again, to make it appear within a simple, bordered box.
10.
Pass the
cursor over the right border, and over the small square that appears about
midway down the right side, until the cursor becomes a simple double-headed
arrow.
11.
Click and drag
the right border of the chart item, widening the chart (along with the
underlying canvas) to approximately the 11
point on the ruler appearing atop the Layout tab.
The
chart item is now in place, and we are ready to specify its Pie chart
nature, as well as to populate it with the primary dataset that is already in
place.
Populate the Pie Chart item to Meet the Business Requirements
While our
next steps might focus upon simply dragging fields from the Fields pane
of the Report Designer, we will populate our chart from inside the
multi-tabbed Chart Properties dialog. Because few charts I encounter in
the business environment can be built completely through the drag-and-drop
process anyway (expressions are often involved in combination with the data
fields, as an example), I have developed the habit of working from the Properties
dialog. (The practice of tabbing through the Properties dialog can
often remind us of other settings we need to make, additional options that are
available in meeting client / employer needs, and so forth, as an another
benefit.)
12.
Right-click
the generic chart item.
13.
Select Properties
from the context menu that appears, as shown in Illustration 11.
Illustration 11: Select Properties from the Context Menu
The Properties dialog appears, defaulted to the General
tab, where we will begin our examination of the chart properties in the subsections
that follow.
General Tab
1.
Type the
following into the Title box on the General tab:
Sales Reason Comparisons
2.
Click the
Style button, depicted (and also magnified) within the respective red
squares in Illustration 12, to the immediate right of the Title box.
Illustration 12: Click the Style Button for the Title Box ...
The Style
Properties dialog box appears.
3.
Make the
settings, listed in Table 1 below, within the Style Properties
dialog box:
Property
|
|
Setting
|
|
|
|
Family
|
|
Arial
|
Size
|
|
12pt
|
Style
|
|
Italic
|
Weight
|
|
Bold
|
Color
|
|
Black
|
Decoration
|
|
None
|
|
|
|
Table 1: Style Properties Dialog
The Style
Properties dialog appears, with our settings, as shown in Illustration 13.
Illustration 13: Style Properties Dialog Box with Settings
4.
Click OK
to accept changes and to exit the Style Properties dialog box.
We return
to the General tab.
5.
Select Pie
in the Chart type selection list.
6.
Ensure that
the left-most selection, Pie chart (the simple versus the exploded Pie
chart) is highlighted, among the context-sensitive Chart sub-type
selections to the immediate right of the Chart type selection list.
7.
Click the Chart
Area Style... button, which appears in the bottom left corner of the General
tab.
We can
manipulate settings for chart borders and lines via the Borders and Lines
tab, depicted with default settings in Illustration 14.
Illustration 14: Style Properties Dialog Box Border and Line Tab (with Default Settings)
8.
Click the
Fill tab to expose the chart Fill settings, which appear (with
defaults) as shown in Illustration 15.
Illustration 15: Style Properties Dialog Box Fill Tab (with Default Settings)
9.
Click the
OK button at the bottom of the Style Properties... dialog, to dismiss
the dialog with all properties settings at default.
We return
to the General tab, once again.
10.
Click the
Plot Area Style... button, which appears to the immediate right of the Chart
Area Style... button at the bottom of the General tab.
The
two-tabbed Style Properties dialog that appears is identical to that
which we saw when we clicked the Chart Area Style... button in the
immediately preceding steps. Since we will be leaving these settings, too, at
their defaults, we will dismiss the Style Properties dialog at this point.
11.
Click the
OK button at the bottom of the Style Properties dialog, to dismiss
the dialog with all properties settings at default.
We return
to the General tab, once again, which appears, with our settings, as
depicted in Illustration 16.
Illustration 16: Chart Properties Dialog Box General Tab with Our Settings
We will
move to the Data tab in the subsection that follows.
Data Tab
1.
Click the Data
tab.
2.
Using the
downward pointing arrow on the right side of the Dataset name box atop
the Data tab, display and select the ProductData dataset, as
shown in Illustration 17.
Illustration 17: Select the ProductData Dataset ...
3.
Click the Add...
button to the right of the Values list box, found immediately underneath
the Dataset name selector we populated above.
The values in a Value series determine the size
of the slice in the pie. Multiple Value series appear as
separate slices. Settings for the x- and y-axis are ignored for Pie charts,
as we shall see. If we have multiple Category or Series groups,
the group labels are displayed in the chart legend.
The Edit
Chart Value dialog appears, defaulted to the Values tab.
4.
Type the
following into the Series label box, atop the Values tab:
Internet Sales
5.
Click the function
(fx) button to the right of the Value box, depicted within
a circle in Illustration 18.
Illustration 18: Click the Function Button for the Value Box ...
The Expression
Editor opens.
6.
Click the Fields
(ProductData) option in the bottom left corner pane of the Expression
Editor.
The field
values of the ProductData dataset appear within the pane at the bottom
right of the Expression Editor.
7.
Double-click Internet_Sales_Amount
to place this selection into the main pane atop the Expression Editor.
The Expression
Editor appears, with our input, as shown in Illustration 19.
Illustration 19: The Expression Editor with Our Input ...
8.
Click OK to
accept our input and to dismiss the Expression Editor.
We are
returned to the Edit Chart Value dialog, which appears with our input as
depicted in Illustration 20.
Illustration 20: The Edit Chart Value Dialog, with Our Input...
9.
Click the Appearance
tab on the Edit Chart Value dialog.
10.
Click the Series
Style... button in the lower left corner of the Appearance tab.
The dual
tabbed Style Properties dialog appears again, just as it did
for the Chart Area Style... and Plot Area Style... buttons we
examined earlier. We see the same Border and Line and Fill tabs,
which we will leave at default at this point.
11.
Click OK
to dismiss the Style Properties dialog, and to return to the Appearance
tab of the Edit Chart Value dialog.
12.
Click the Point
Labels tab on the Edit Chart Value dialog.
13.
Click the
check box to the immediate left of Show point labels atop the Point
Labels tab, to place a check mark within the box.
14.
Click the
downward pointing arrow to the right of the Data label box to display
data selection options.
15.
Select =Fields!Internet_Sales_Amount.Value
from the options, as shown in Illustration 21.
Illustration 21: Select =Fields!Internet_Sales_Amount.Value as the Data Label ...
16.
Within the Format
code box (just underneath the Data label selector) type the
following character pattern:
#,###
This will
result in the point labels being displayed within the slices of
the Pie (when accommodated by slice size), making the chart more
compact and, in this case, making the labels themselves somewhat easier to visually
associate with their respective slices.
17.
Click the Label
Style ... button immediately underneath the Angle setting.
The Style Properties dialog box appears.
18.
Make the
settings, listed in Table 2 below, within the Style Properties
dialog box:
Property
|
|
Setting
|
|
|
|
Family
|
|
Arial
|
Size
|
|
8pt
|
Style
|
|
Normal
|
Weight
|
|
Normal
|
Color
|
|
Red
|
Decoration
|
|
None
|
|
|
|
Table 2: Style Properties Dialog
The Style
Properties dialog appears, with our settings, as depicted in Illustration
22.
Illustration 22: Style Properties Dialog Box with Settings
19.
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 23.
Illustration 23: The Edit Chart Value Dialog Box, with Our Input
20.
Click the Action
tab of the Edit Chart Value dialog.
Here we
can define Hyperlink actions, including Jump to report, Jump
to bookmark, or Jump to URL. We will not be working with a
drillthrough scenario in this practice session, and so will leave undisturbed
the default setting of None, as depicted in Illustration 24.
Illustration 24: The Action Tab of the Edit Chart Value Dialog Box
21.
Click the Data
Output tab of the Edit Chart Value dialog.
The Data
Output tab affords us a means of defining XML data output options
for the values in the chart. We will not leverage these capabilities within
this introduction to simple Column charts, and will therefore leave the
settings of the Data Output tab at their defaults, as shown in Illustration
25.
Illustration 25: The Data Output Tab with Default Settings
22.
Click the
OK button at the bottom of the Edit Chart Value dialog, to dismiss
the dialog with our settings.
We are
returned to the Data tab of the Chart Properties dialog. Next we
will work with the Category groups area of the tab, just under the Values
area. Categories are displayed as individual slices in the
pie. Each Category is displayed in the chart legend, when the legend
is selected for use.
23.
Click the Add...
button to the right of the Category groups list box, found
immediately underneath the Values list box with which we worked above.
The Grouping
and Sorting Properties dialog appears, defaulted to the General tab.
24.
Type (or cut
and paste) the following into the Name box, atop the General tab:
Year
25.
Click the top
row of the Expression box within the Group on section that
appears just underneath the Name box, to enable the selector button (the
downward-pointing arrow) on the right side of the box.
26.
Select =Fields!Calendar_Year.Value
from the options that appear, as depicted in Illustration 26.
Illustration 26: Select =Fields!Calendar_Year.Value in the Top Row of the Expression List ...
As we
have no input for the Filters or Data Output tabs (which resemble
their counterparts in other areas of the Report Designer, as we have,
and shall, see), within the context of this newly added group, and as we intend
to leave the Sorting at the settings naturally found within the cube
structure (the Sales Reasons will sort, where appropriate, in alphabetical
order), we will accept our input at this stage.
The Grouping
and Sorting Properties dialog appears, with our modifications, as shown in Illustration
27.
Illustration 27: The Grouping and Sorting Properties Dialog with Our Modifications
27.
Click the
OK button at the bottom of the Grouping and Sorting Properties
dialog, to save our settings and to dismiss the dialog.
We will
next add another Category group this time a group within a group,
for the Sales Territory.
28.
Click the Add...
button to the right of the Category groups list box, once again.
The Grouping
and Sorting Properties dialog appears, defaulted to the General tab,
as before.
29.
Type (or cut
and paste) the following into the Name box, atop the General tab:
Territory
30.
Click the top
row of the Expression box within the Group on section that
appears just underneath the Name box, again to enable the selector
button (the downward-pointing arrow) on the right side of the box.
31.
Select =Fields!Sales_Territory_Group.Value
from the options that appear.
Because,
once again, we have no input for the Filters or Data Output tabs,
within the context of this newly added group, and as we also intend, as
earlier, to leave the Sorting at the settings naturally found within the
cube structure (the Sales Reasons will sort, where appropriate, in
alphabetical order), we will accept our input at this stage.
The Grouping
and Sorting Properties dialog appears, with our modifications, as depicted
in Illustration 28.
Illustration 28: The Grouping and Sorting Properties Dialog with Our Modifications
32.
Click the
OK button at the bottom of the Grouping and Sorting Properties
dialog, to save our settings and to dismiss the dialog.
We are
returned to the Data tab of the Chart Properties dialog, which
appears, with our Values and Category groups settings, as shown
in Illustration 29.
Illustration 29: Chart Properties Dialog Box Data Tab with Our Settings
Our next
step will be to add another group this time a Series group. Series
are displayed as individual slices in the Pie chart. Each series
is also displayed in the chart legend. At this point, we will add a Series
group to contain the Sales Reason (Manufacturer, On Promotion, Price,
etc.) that is associated with the grouped Internet Sales totals that the
report will present.
33.
Click the Add...
button to the right of the Series groups list box, just as we did
in making our two Category groups entries earlier.
The Grouping
and Sorting Properties dialog appears, once again, defaulted to the General
tab.
34.
Type (or cut
and paste) the following into the Name box, atop the General tab:
Sales_Reason
35.
Click the top
row of the Expression box within the Group on section, yet again,
to enable the selector button (the downward-pointing arrow) on the right side
of the box.
36.
Select =Fields!Sales_Reason.Value
from the options that appear, as depicted in Illustration 30.
Illustration 30: Select =Fields!Sales_Reason.Value in the Top Row of the Expression List ...
The Grouping
and Sorting Properties dialog appears, with our modifications, as shown in Illustration
31.
Illustration 31: The Grouping and Sorting Properties Dialog with Our Modifications
Because
we again have no input for the Filters or Data Output tabs within
the context of this newly added group, and as we intend to leave the Sorting
at the settings naturally found within the cube structure (the Sales Reasons
will sort in alphabetical order), we will accept our input at this stage, just
as we did with the previously added Category groups, Year and Territory.
37.
Click the
OK button at the bottom of the Grouping and Sorting Properties
dialog, to save our settings and to dismiss the dialog.
We return
to the Data tab, once again, which appears, with our Values, Category
groups, and Series groups settings, as depicted in Illustration 32.
Illustration 32: Chart Properties Dialog Box Data Tab with Our Settings
We will
move to the X Axis tab in the subsection that follows.
X Axis Tab
The X
Axis tab is disabled for a Pie chart data region. All settings are
therefore grayed out.
Y Axis Tab
The Y
Axis tab, like the X Axis tab, is disabled for a Pie chart
data region. All settings are therefore grayed out.
We will
move to the Legend tab next.
Legend Tab
We advise
our client colleagues that, since the default position for the legend is
to the right of the chart area, and since our simple pie chart may be
wider than expected, due to its nature and the amount of data we are
presenting, placing the legend underneath the chart will offer another
means of compressing the overall presentation.
2.
Ensure that
the Show legend checkbox is checked, in the upper left corner of the
tab.
3.
Ensure that
the Column radio button is selected, in the upper left corner of the Layout
section of the tab.
4.
Click the
bottom middle button underneath the Position selection diagram, to align
the Legend box underneath the Pie chart area.
5.
Click the Legend
Style button that appears immediately beneath the checkbox labeled Display
legend inside plot area (ensure that the box remains unchecked).
The Style
Properties dialog box appears, defaulted to the Font tab.
6.
Make the
settings, listed in Table 3 below, within the Font tab of the Style
Properties dialog box:
Property
|
|
Setting
|
|
|
|
Family
|
|
Arial
|
Size
|
|
9pt
|
Style
|
|
Normal
|
Weight
|
|
Normal
|
Color
|
|
Black
|
Decoration
|
|
None
|
|
|
|
Table 3: Style Properties Dialog Font Tab
The Font
tab of the Style Properties dialog appears, with our settings, as shown
in Illustration 33.
Illustration 33: Font Tab of the Style Properties Dialog Box, with Settings
We note
the presence of the Border and Line and Fill tabs, but we will
leave the settings within each at default, at this point.
7.
Click OK
to accept changes, and to exit the Style Properties dialog box.
We return
to the Legend tab, once again, where we will leave all else at default.
The relevant portion of the Legend tab appears, with our settings, as
depicted in Illustration 34.
Illustration 34: Chart Properties Dialog Box Legend Tab with Our Settings
We will
move to the 3D Effect tab in the next subsection.
3D Effect Tab
1.
Click the 3D
Effect tab.
We inform our client colleagues that the 3D Effect tab
offers us a means of transforming the ordinarily flat appearance of our Column
chart to a highly customizable, three dimensional presentation. Here we
can enable 3-D visual effects (via the checkbox to the immediate left of
the Display chart with 3-D visual effect label). The four variables
that we can manipulate are:
-
Horizontal
rotation
-
Perspective
-
Wall thickness
-
Vertical
rotation
Once 3-D visual effects are enabled, a slider
becomes enabled for each of these variables, which we can move to adjust each
variable until we achieve just the degree of readability we desire within the
chart.
We will
leave the settings of the 3D Effect tab at default at this point. The 3D
Effect tab appears, with default settings, as shown in Illustration 35.
Illustration 35: Chart Properties Dialog Box 3D Effect Tab with Our Settings
We will examine
the final remaining tab, Filters, in the next subsection.
Filters Tab
1.
Click the Filters
tab.
The Filters tab is but one option we have, within Reporting
Services, to filter the data that is displayed within our chart. We advise
our client colleagues that we performed all desired filtering at the dataset
level earlier within our practice session. When this is adequate (that is,
when we can afford to filter at the dataset level for the entire report)
we may achieve performance gains at report runtime, due to the overall
retrieval of less data from the Analysis Services data source. But, we
caution the client representatives, due consideration should be given to the
various points at which we can filter within a given report, to ascertain that
we optimize performance while retaining complete and accurate information for
presentation.
The Filters tab allows us to choose either simple dataset
columns or expressions to filter data at the chart level. This might
make sense as a filter point if we were, say, using multiple data regions
(charts, matrices, tables, lists, or a combination of these, perhaps) that were
sharing the same common dataset(s), but where each region had different
filtering requirements and needed to present different subsets of data from the
underlying dataset(s). Whatever our needs, Reporting Services,
once again, offers flexibility in ways to meet the challenges involved.
Lets filter our Pie chart to show results of only
the European and North American Territories.
2.
Click the top
row of the Expression box within the Filters section, yet again,
to enable the selector button on the right side of the box.
3.
Select =Fields!Sales_Territory_Group.Value
from the options that appear, as depicted in Illustration 36.
Illustration 36: Select =Fields!Sales_Territory_Group.Value in the Top Row of the Expression List ...
4.
In the Operator
column (to the immediate right of the Expression column), select the =
operator.
5.
Type (or cut
and paste) the following into the Value column (to the immediate right
of the Value column):
="Europe"
6.
Click the
second row of the Expression box within the Filters section,
again to enable the selector button on the right side of the box.
7.
Select =Fields!Sales_Territory_Group.Value
from the options that appear, once again.
We note
that an or is automatically inserted in the And/Or column on
the far right of the first row.
8.
In the Operator
column (to the immediate right of the Expression column), select the =
operator
9.
Type (or cut
and paste) the following into the Value column (to the immediate right
of the Value column):
=North America"
We are
simply asking to see the Internet Sales values for two of the Sales Territories, at this point. In reality, we
might parameterize territory selection to make the report do more with less,
in offering information consumers an ad hoc choice of territories at runtime.
The Filters
tab of the Chart Properties dialog appears as shown in Illustration 37.
Illustration 37: The Filters Tab of the Dataset Dialog with Our Additions ...
10.
Click OK
to accept our additions, and to dismiss the Chart Properties dialog.
The Chart Properties dialog closes,
returning us to the placeholder chart item in Report Designer, Layout tab. We will adjust the height of the chart item to make the preview we conduct
next easier to read.
11.
Pass the cursor over the bottom border,
and over the small square that appears about midway amid the bottom edge, of
the chart item, until the cursor becomes a simple double-headed arrow.
12.
Click and drag
the bottom border of the chart item, increasing the height of the chart
(along with the underlying canvas) to approximately the 6-1/2 point on
the ruler appearing along the left side the Layout tab.
The Layout
tab appears, with our adjustment, as depicted in Illustration 38.
Illustration 38: The Layout Tab with Our Height Adjustment ...
We will conclude our practice
session in the next section, where we will verify the operation of our new Pie chart.
Verify Operation of the Pie Chart
Lets 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.
2.
Select Bikes
within the Product Category report parameter picklist, as shown in Illustration
39.
Illustration 39: Select Bikes as the Product Category ...
3.
Click the View
Report button.
The new report
generates, displaying both the original matrix and new Pie chart
data regions.
4.
Scroll down,
as required to focus upon the new Pie chart data region.
The Pie
chart data region appears as depicted in Illustration 40.
Illustration 40: The Pie Chart Report, Preview Tab
We can
easily verify the displayed simple Pie chart totals against the matrix
data region by comparing any given Sales Reasons Internet Sales
Amount displayed in the Pie chart data region to the total Internet
Sales Amount corresponding to the respective Sales Reason in the matrix
data region. For example, if we add CY 2003 Europe Price Internet
Sales (1,519,252) and CY 2004 Europe Price Internet Sales (2,066,776),
we obtain a total that corresponds with the total Internet Sales Amount
(consisting of a combination of amounts for Calendar Years 2003 and 2004)
displayed for Europe (3,586,028) in the original matrix data
region that we have left within the report.
Our enhanced
Pie chart meets the expressed business requirements and demonstrates many
details surrounding its property settings. The client representatives express
satisfaction with our efforts, and state that, with a few cosmetic changes
(including the simple removal of the existing matrix data region, and
the subsequent realignment of the Pie chart on the canvas, realignment /
resizing of the point labels to better fit the slices of the pie,
etc.), the report will be ready for deployment to the targeted information
consumer group. Moreover, they assure us that the details they have examined
within the practical exercise we have undertaken can be extrapolated to their
creation efforts of other, perhaps even more sophisticated, Pie charts.
5.
Experiment
further with the report, if desired.
6.
When finished
with the report, click the Layout tab.
7.
Select File
-> Save RS062_Pie_Chart.rdl As ... to save our work, up to this
point, to a location where it can be easily accessed for later reference.
As we
can see from our examination above, Reporting Services offers a wide
range of options for Pie chart creation and manipulation, to
assist us in the delivery of information within the business environment. We extend our examination of chart
types, specifically examining each type, together with the properties and
methods we can manipulate for the precise presentations we seek to be able to
deliver, in other articles of this series.
8.
Select File
-> Exit to leave the design environment,
when ready (saving as desired), and to close the Business Intelligence Development
Studio.
Conclusion
In this article, we
performed a relatively straightforward examination of a Reporting Services enhanced Pie
chart, from within a copy of an existing sample Reporting Services 2005 report
that we created for this purpose. Our focus, as we stated in the introduction,
was to create an enhanced, working Pie chart, using an Analysis Services data source (the Adventure Works DW sample
OLAP database / Adventure Works cube that accompanies the installation
of Reporting Services), and to discuss various characteristics of the Pie chart type
as we progressed.
We examined relevant chart properties, and got some
hands-on exposure to the manipulation of those properties to support the
delivery of information to meet the needs of a hypothetical group of
organizational information consumers. We noted that this article would serve as a basis,
in some cases, for other, more advanced articles within the MSSQL Server Reporting
Services
series, where we use the Pie chart we created here as a basis from which we can
concentrate on in-depth procedures and nuances that we can use to achieve
precision in meeting specific requirements and data presentation effects that
we might deliver in the business environment.
About the MSSQL Server Reporting Services
Series ...
This
article is a member of the series MSSQL Server Reporting Services. This monthly column is designed
to introduce MSSQL Server Reporting Services (Reporting Services),
presenting an overview of its features, with tips and techniques for real-world
use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.
»
See All Articles by Columnist William E. Pearson, III