A More Advanced Pie Chart for Analysis Services Data

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:

  • Simple Pie
  • Exploded Pie

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.

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