Stacked Bar Chart for Analysis Services Data

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles