Put the stacked bar chart to work in
your own business environment. BI Architect Bill Pearson continues
his hands-on introduction to Reporting Services charts based upon Analysis
Services data sources.
This article focuses upon Stacked Bar
charts, and extends the examination of Reporting Services charts
for Analysis Services data sources that we began in 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 perspective 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 in most respects 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, at
appropriate junctures, 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. Like the Stacked Column
chart type we examined in Stacked
Column Chart for Analysis Services, the Stacked
Bar chart type represents a more sophisticated means of presenting data then
the simple version of the same chart data region offered by Reporting
Services. (We introduced the simple bar chart in Simple
Bar Chart for Analysis Services Data.) In this
article, we will introduce the Stacked Bar chart type and get some hands-on
exposure to its creation and its 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 Stacked Bar 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 Stacked Bar 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 Stacked Bar Charts for Analysis Services
In Introducing Reporting
Services Charts for Analysis Services, we learned that the Bar
chart type is available in the following variants:
-
Simple Bar
-
Stacked Bar
-
100% Stacked
Bar
In
this article we will focus upon the Stacked Bar variant, although we will take up the other variants
within relevant contexts in sister articles of the MSSQL Server Reporting
Services series.
We noted in Simple Bar Chart for Analysis Services
Data that Bar
charts are typically used to compare values between categories.
In generally describing the type, we observed that the (simple) Bar chart
presents series as sets of horizontal bars that are grouped by category. In contrast, the Stacked Bar chart displays all
series stacked in a single bar for each category. In the Stacked Bar chart,
the Values are represented by the width of the bars, as measured by the x-axis,
just as they are within the simple Bar chart the difference is that each value
series appears as a section within the bar (again, the width / length of each bar
is determined by the total of all series values for the category).
As we saw to be the case with the Simple Bar variant, Category
labels are displayed on the y-axis. The
general scheme of the Bar chart is the same as that of a Column chart turned 90
degrees, as we noted in our examination of its Simple Bar cousin. And just as we also noted
in Simple Bar Chart for Analysis Services Data, all
variants of the Bar chart have the advantage of more accurately depicting
value comparisons for layouts where we have more available horizontal space.
In this article, we will introduce
the Stacked Bar chart data
region in detail, and gain practical exposure to the creation of a basic
example of such a chart employed in reporting from an Analysis Services data
source. In introducing the Stacked Bar chart, we will:
-
Perform a
brief overview of the Bar 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;
-
Modify the
existing primary dataset within the sample report clone, adding a filter to
limit the size of the data presentation;
-
Examine the Stacked
Bar 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;
- Make
modifications to the report layout to support the stated client reporting
needs and practice session objectives;
-
Create a
complete, working sample of a Stacked Bar chart data region, within the
existing report, 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,
and 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 perform a relatively straightforward examination of the Stacked Bar 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 Stacked Bar 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 some initial 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 (and referencing) the Stacked Bar chart we create 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 delivering 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 Bar chart report. The group has
stated that they want to leverage this conversion process to learn more about
the construction and characteristics of Stacked Bar chart reports 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 Stacked Bar chart report
to satisfy the requirements of the information consumers.
Practice
Our
first objective is to create a copy of a pre-existing sample report, within which we will perform the
addition and setup of the
Stacked Bar 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 Bar 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 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 Stacked Bar 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 Operator
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 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 3-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 Stacked Bar 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:
RS065_Stacked_Bar_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 Bar Chart within the Report
Add a Chart Control to the Report Canvas
At
this point, we will
drag 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). 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 to View (Partial View Menu Shown)
The toolbox
window should appear similar to that depicted in Illustration 8. 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 8: The Toolbox, Pinned to the Upper Left Corner 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 draw the box 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 now 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.
Pass the
cursor over the left border of the chart item, until it becomes a four-arrow icon.
6.
Click and drag
the chart item to the left side of the report canvas.
7.
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.
8.
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.
9.
Click and drag
the bottom border of the chart item, widening the chart downward (along with
the underlying canvas) to approximately the 7 point on the ruler appearing
along the left side of the Layout tab.
The
chart item is now in place, and we are ready to specify its Stacked Bar chart
nature, as well as to populate it with the primary dataset that is already in
place.
Populate the Bar 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 date fields, as an example), I long
ago 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.)
1. Right-click
the generic chart item.
2.
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
square 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. Ensure
that Bar is selected in the Chart type selection list.
6. Ensure
that the middle selection, Stacked Bar 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 associated 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
- Click
the Data tab.
- 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 ...
- 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 length / width of the bars in that
series, as we mentioned earlier. Value labels appear on the x-axis (we will
examine format settings for these in our subsection on the X Axis tab below).
Within a Stacked Bar chart, each Value series appears as a section within the
bar.
The Edit
Chart Value dialog appears, defaulted to the Values tab.
- Type
the following into the Series label box, atop the Values tab:
Internet Sales
- 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.
- 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 to the bottom right of
the Expression Editor.
- Double-click
Internet_Sales_Amount to place this selection into the Expression 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 ...
- 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...
- Click
the Appearance tab on the Edit Chart Value dialog.
- 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.
- Click OK
to dismiss the Style Properties dialog, and to return to the Appearance tab
of the Edit Chart Value dialog.
- Click
the Point Labels tab on the Edit Chart Value dialog.
- Click
the check box to the immediate left of Show point labels atop the Point
Labels tab.
- Click
the downward pointing arrow to the right of the Data label box to display
data selection options.
- 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 ...
- Within
the Format code box (just underneath the Data label selector) type the
following character pattern:
#,###
- Uncheck
the Auto checkbox to the right of the Format code box.
This will
result in the point labels being displayed horizontally within the bar sections
of our chart, making the chart more compact and, in this case, making the
labels themselves somewhat easier to read.
- Click
the Label Style ... button immediately underneath the Angle setting.
The Style Properties dialog box appears.
- Make
the settings, listed in Table 2 below, within the Style Properties dialog
box:
Property
|
|
Setting
|
|
|
|
Family
|
|
Arial
|
Size
|
|
7pt
|
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
- 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
- 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
- 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
- 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 optional, and are displayed as bars or groups of bars on the Y-axis.
Multiple groups are nested, as we shall see within our current practice
example.
- 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.
- Type
(or cut and paste) the following into the Name box, atop the General tab:
Year
- 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.
- 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 ...
Because
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 Calendar
Years will sort in numerical 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
- 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. Next we will add
another Category group to our report.
- Click
the Add... button to the right of the Category groups list box, just as we
did before.
The Grouping
and Sorting Properties dialog appears, once again, defaulted to the General
tab.
- Type
the following into the Name box, atop the General tab:
Territory
- Click
the top row of the Expression box within the Group on section, once again,
to enable the selector button (the downward-pointing arrow) on the right
side of the box.
- Select =Fields!Sales_Territory_Group.Value
from the options that appear, as depicted in Illustration 28.
Illustration 28: Select =Fields!Sales_Territory_Group.Value in the Top Row of the Expression List ...
The Grouping
and Sorting Properties dialog appears, with our modifications, as shown in Illustration
29.
Illustration 29: The Grouping and Sorting Properties Dialog with Our Modifications
As we
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 Territory Groups will sort in
alphabetical order), we will accept our input at this stage, just as we did
with the previously added Category group, Year.
- 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, once again. Our next
step will be to add another group this time an optional Series group. Series
are displayed as sections within bars in the Stacked Bar 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 each of the grouped Internet Sales totals that the report will
present.
- 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.
- Type
(or cut and paste) the following into the Name box, adding an underscore (
_ ) character between the two words of the Name, atop the General tab:
Sales_Reason
- 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.
- 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.
- 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
- Click
the X Axis tab.
We advise
our client colleagues that the labels assigned to the groups that we created
earlier will suffice to address the visual x-axis, and that we therefore need
only to deal with the labels settings (we can leave the Title and other
settings at default).
- Ensure
that the Show labels checkbox is checked.
- Click
the Style button to the immediate right of the blank Format code box.
The Style
Properties dialog box appears, once again.
- Make
the settings, listed in Table 3 below, within the Style Properties dialog
box:
Property
|
|
Setting
|
|
|
|
Family
|
|
Arial
|
Size
|
|
9pt
|
Style
|
|
Normal
|
Weight
|
|
Normal
|
Color
|
|
Black
|
Decoration
|
|
None
|
|
|
|
Table 3: Style Properties Dialog
The Style
Properties dialog appears, with our settings, as shown in Illustration 33.
Illustration 33: Style Properties Dialog Box with Settings
- Click OK
to accept changes and to exit the Style Properties dialog box.
We return
to the X Axis tab, once again, where we will leave all else at default. The X
Axis tab appears, with our settings, as depicted in Illustration 34.
Illustration 34: Chart Properties Dialog Box X Axis Tab with Our Settings
We will
move to the Y Axis tab in the next subsection.
Y Axis Tab
- Click
the Y Axis tab.
We
propose doing a little more adjustment to the default settings in the Y Axis
tab than we witnessed in its X Axis counterpart in the section above. These
modifications will be primarily in the interest of presentation compactness and
clarity. They will also demonstrate, in a small way, the power we can leverage
through the use of expressions in various value definitions within Reporting
Services.
- Type (
or cut and paste) the following into the Title box on the General tab:
="Total Internet "& vbcrlf &" Sales" &""
Here we
are adding the title Total Internet Sales, using the vbcrlf keyword within
the expression to force the word Sales into a second row of the title, so
that the title takes up less vertical space.
- Click
the Style button to the immediate right of the Title box.
The Style
Properties dialog box appears, as we have seen in earlier sections.
- Make
the settings, listed in Table 4 below, within the Style Properties dialog
box:
Property
|
|
Setting
|
|
|
|
Family
|
|
Arial
|
Size
|
|
9pt
|
Style
|
|
Normal
|
Weight
|
|
Normal
|
Color
|
|
Black
|
Decoration
|
|
None
|
|
|
|
Table 4: Style Properties Dialog
The Style
Properties dialog appears, with our settings, as shown in Illustration 35.
Illustration 35: Style Properties Dialog Box with Settings
- Click OK
to accept changes and to exit the Style Properties dialog box.
We return
to the Y Axis tab.
- Ensure
that the Show labels checkbox is checked.
- Type
the following into the Format code box:
#,###
- Click
the Style button to the immediate right of the Format code box.
The Style
Properties dialog box appears, once again.
- Make
the settings, listed in Table 5 below, within the Style Properties dialog
box:
Property
|
|
Setting
|
|
|
|
Family
|
|
Arial
|
Size
|
|
9pt
|
Style
|
|
Normal
|
Weight
|
|
Normal
|
Color
|
|
Black
|
Decoration
|
|
None
|
|
|
|
Table 5: Style Properties Dialog
The Style
Properties dialog appears, with our settings, as depicted in Illustration 36.
Illustration 36: Style Properties Dialog Box with Settings
- Click OK
to accept changes and to exit the Style Properties dialog box.
We return
to the Y Axis tab, once again, where we will leave all else at default. The Y Axis tab appears, with our settings, as
shown in Illustration 37.
Illustration 37: Y Axis Properties Dialog Box General Tab with Our Settings
We will
move to the Legend tab next.
Legend
Tab
- Click
the 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 Stacked Bar 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.
- Ensure
that the Show legend checkbox is checked, in the upper left corner of the
tab.
- Ensure
that the Column radio button is selected, in the upper left corner of the
tab.
- Click
the bottom middle button underneath the Position selection diagram, to
align the Legend box underneath the Column chart area.
- 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.
- Make
the settings, listed in Table 6 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 6: Style Properties Dialog Font Tab
The Font tab
of the Style Properties dialog appears, with our settings, as depicted in Illustration
38.
Illustration 38: 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.
- 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 Legend
tab appears, with our settings, as shown in Illustration 39.
Illustration 39: Chart Properties Dialog Box Legend Tab with Our Settings
We will
move to the 3D Effect tab in the next subsection.
3D Effect Tab
- 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 Bar
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 depicted in Illustration 40.
Illustration 40: Chart Properties Dialog Box 3D Effect Tab with Our Settings
We will
examine the final remaining tab, Filters, in the next subsection.
Filters Tab
- 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.
The Filters
tab appears, with default settings, as shown in Illustration 41.
Illustration 41: Chart Properties Dialog Box Filters Tab with Our Settings
- Click OK
to accept all the settings we have made in the multi-tabbed Chart
Properties dialog box.
The Chart Properties dialog
closes, returning us to the placeholder chart item in Report Designer, Layout
tab. We will conclude our practice session in the next section, where we will
verify the operation of our new Stacked Bar chart.
Verify Operation of the Bar Chart item
Lets ascertain the
accuracy and completeness of our construction efforts. We will execute the
report with the following steps:
- Click
the Preview tab, to the right of the Layout tab atop the design surface.
- Select Bikes
within the Product Category report parameter picklist, as depicted in Illustration
42.
Illustration 42: Select Bikes as the Product Category ...
- Click
the View Report button.
The new
report generates, displaying both the original matrix and new Bar chart data
regions.
The Stacked
Bar chart data region appears as shown in Illustration 43.
Illustration 43: The Stacked Column Chart Report, Preview Tab
We can
easily verify the displayed simple Stacked Bar chart totals against the matrix
data region (by adding together each of the two years totals for any given Territory
Group / Sales Reason, and comparing that total to the corresponding total in
the matrix data region).
Our Stacked
Bar 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 / resizing, as appropriate, of the Stacked Bar chart on
the canvas), 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 Stacked Bar charts.
4.
Experiment
further with the report, if desired.
5.
When finished
with the report, click the Layout tab.
6.
Select File -> Save RS065_Stacked_Bar_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 Stacked Bar 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.
7.
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 the Reporting Services Stacked Bar chart type,
from within a copy of an existing sample Reporting Services report that we
created for this purpose. Our focus, as we stated in the introduction, was to
create a basic, working Stacked Bar 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 Stacked Bar 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 Stacked Bar chart we created here as
a starting point from which we can concentrate on in-depth procedures and
nuances 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