Introducing Reporting Services Charts for Analysis Services

This month, I introduce a group of several articles
surrounding Reporting Services charts (or chart data regions)
based upon Analysis Services data sources. These related articles,
interspersed among other topics within my MSSQL Server Reporting Services series over
time, will demonstrate how to create chart reports of
various types, focusing upon Analysis Services data
sources, and how to exploit 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 cost. And the flexibility and
richness of the chart data regions 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 OLAP 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 have seen in previous articles, and as we will show in
many prospective articles within this series, Reporting
Services
enables us to present both summarized and detailed data in
colorful, easy-to-read charts of various
designs. We can choose from a number of chart layouts and
types within the Reporting Services chart data region options.
(As we have discussed in earlier articles, a data region is an area on
a report that contains data from a data source that is repeated. The general types
of data regions are list, matrix, table, and chart.)
As
we have already begun to see in earlier examinations of charts (as well as
other data regions within Reporting Services), in
articles such as Master
Chart Reports: Pie Charts in Reporting Services
and Master
Chart Reports: Track Exchange Rates in a Line Chart
, and as we shall
see through in-depth, practical exercises in coming articles, we can also
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.

The initial focus of my “charts for Analysis
Services
” articles is a full set of procedures that are designed to support
a more in-depth study of specific property settings,
and so forth. My objective is to allow a reader to quickly assemble a given chart report
(relying upon, for instance, already assembled data sets and other underlying
support within existing sample reports), and to move efficiently into targeted
reporting nuances that meet real world needs. While initial introductions will
focus more on the creation of a given type of report, this report will serve as
a basis in prospective articles, where useful, to demonstrate more detailed
intricacies that I have found useful in meeting recent business requirements of
my clients and readers. The ultimate objective, as is typically the case
within my various columns, 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 Charts for Analysis Services

As
I have noted many times within my MSSQL Server Reporting Services series, the rich charting
capabilities made available within Reporting Services are quite impressive. They
are as powerful, and as easy to use, as the options that I have found within
any enterprise reporting solution with which I have worked, among which I
include various products of Cognos, Business Objects, and many others, as well
as, for that matter, within Microsoft Office applications such as Excel or Access. In many ways, the Reporting Services chart offerings are more powerful,
largely due to their openness to the use of expressions. Much like the list, table and matrix data regions with which we
have worked in so many of the articles of this column, a Reporting Services chart item is supported by a data set and can leverage
filters, groups, query and report parameters, and much more in a similar manner
to the other three main data regions.

There
are many business and other scenarios where charts are useful. Depending upon
the needs and objectives of the report audience, and how the information will
be used, graphical information presentation – and the potential impact that can
be imparted – can be quite significant. The intended usage and the identities of the targeted
information consumers
should always be considered in report design and
creation. Moreover, the organizational, and other, roles that given consumers assume
in using the data we present (for example, whether the intent is to perform a
specific task via the information obtained, or, in a management role, perhaps,
to apply the information to broader, more complex tasks such as strategy
implementation or overall performance objective measurement) should also weigh
heavily in our report design and creation efforts. Finally, we should always
keep in mind that some information consumers, such as business analysts, may
have a need to perform ongoing exploration of the data (and have needs such as ad hoc grouping,
sorting, pivoting and others), whereas many consumers will require only a
specific, fixed format for data delivery (such as that provided by managed reports).

Managerial
and executive reports are often enhanced through the use of charts because they present
information quickly and concisely. Charts (and graphics, in general) also assist in arousing
interest and conveying meaning – especially in their ability to present “outliers” in performance (“best” and
“worst,” for example), as well as to warn of trends and the like. As we see in
the articles that deal with specific chart types within this series, column, bar, and line charts, among numerous other in Reporting Services, act as excellent tools for efficiently
giving meaning to large, often complicated, volumes of data. In helping
consumers identify patterns and trends, among other capabilities, their strengths lie within
their capacity to distill and compress what might be thousands of lines of data into a
display that supports “instant understanding” by the consumer.

Another
exciting thing about chart data regions in Reporting Services is the huge synergies we can
obtain by combining charts with the other three general report types. As I
demonstrate in other articles of the series, charts can be embedded in table, matrix, and list report items in a row, a column,
or list area, as appropriate, resulting in a repeat of the chart for every record in the
dataset – a capability that I have found to be useful in a wide range of
environments, from education (“No Child Left Behind” report cards / “Adequate Yearly Progress” reports for state schools)
to healthcare ( patient report cards / other reports, presenting various charts
as to patient statuses and readings).

In this article, we will introduce
the various chart data regions that are available to
us within Reporting Services, discussing
the general uses and characteristics of each. This will prepare us, in
general, for other articles where we employ individual chart types in
reporting from an Analysis Services data
source, and demonstrate properties (and creative ways to manipulate them within
our reports) and methods that we can employ to format and deliver information
to meet the business needs of our clients and employees. In introducing the basic chart
types
, we will:

  • 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 chart
    types from the standpoint of the existing report, noting how we add them to
    an open report in the Layout tab;
  • Perform a
    brief overview of each chart type, discussing its variants and typical
    uses.

Objective and Overview Scenario

In this article, we will
perform a relatively straightforward examination of the Reporting Services chart types,
from within a copy of an existing sample Reporting Services 2005 report
that we will create for this purpose. Our focus will be to discuss the multiple
possibilities provided by existing chart types. As we noted earlier,
this introduction will prepare us for independent articles within the series
where we employ individual chart types in reports using an Analysis Services data source. In each
article surrounding a given chart type, we will examine relevant chart properties, and get some
hands-on exposure to the manipulation of those properties to support the
presentation of information to meet the needs of our own respective business
environments.

To
provide a report upon which we can practice our exercises, we will begin with
the Sales Reason Comparisons sample report that, among other samples,
accompanies the installation of Reporting Services 2005, and which is
based upon the ubiquitous Adventure Works cube, contained within the
Analysis Services
sample database named Adventure Works DW (which is
available to anyone installing Analysis Services 2005).

Examination

Our
first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can follow along
as we examine the various chart types we will overview. We typically
work with a similar copy in the 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

Let’s
ensure we have a working data source. Many of us will 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 sample’s 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 for our chart types overview exercise. Creating a “clone” of the project
means we can make changes to select contents (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 OLAP report file within our Reporting Services 2005 Project,
and are ready for our examination of the Reporting Services chart types,
which we will begin in the next section.

Procedure: Examine Chart
Options in Reporting Services 2005

In the overview procedures that
follow, we will examine the Reporting Services chart options upon which
we can call to report upon our Analysis Services data sources.

Enter
the Newly Created Sample Report Copy to Examine Available Chart Styles

Let’s
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
(or your own choice of a similar report) in the Solution Explorer.

2. 
Select Open
from the context menu that appears, as shown in Illustration 1, as
necessary.

Opening the New Report

Illustration 1: Opening the New Report …

DBJ_OLAP_Report.rdl
opens in Layout
view.

We will
start with a larger report canvas.

3. 
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.

4. 
Pass the cursor
over the lower edge of the report body, until it becomes a “two-headed
arrow.”

5. 
Enlarge the
report canvas to about the 2-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 2.

Illustration 2:  Stretch the Canvas Downward to Create Empty Space in the Report Body

Illustration 2: Stretch
the Canvas Downward to Create Empty Space in the Report Body

Next,
we’ll 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).

6. 
Click the Chart
item, among the listed report items, within the Toolbox, to select it.

7. 
Drag the Chart
selection to the lower part of the newly enlarged report canvas on the Layout
tab.

8. 
Drop the Chart
item onto the canvas, below the existing matrix, approximately as shown in Illustration
3
.

Illustration 3:  Drag the Chart Selection to the Report Campus ...

Illustration 3: Drag the Chart Selection to the Report Campus …

The
generic Chart placeholder appears.

9. 
Right-click
the placeholder at some point on the chart graphic.

10. 
Select Chart
Type
of the context menu that appears next.

The
nine available Chart types appear for selection, and include the
following:

  • Column
  • Bar
  • Area
  • Line
  • Pie
  • Doughnut
  • Scatter
    (“XY”)
  • Bubble
  • Stock

The available chart
types appear as depicted in Illustration 4.

Illustration 4:  The Nine Available Chart Types Appear ...

Illustration 4: The Nine Available Chart Types Appear …

Each of the selections offers further, more specialized variants,
which we list within the respective sections below. (We will examine specific variants,
where useful, within independent articles focusing upon each of the individual Chart
types.) In the sections that follow, we will provide a general description and
summary of the typical uses for each of the available Chart types.

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