This article focuses upon XY charts, and
extends 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, perhaps 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 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. In this article, we will introduce the basic XY (Scatter) 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 / drill through to see the details behind 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 XY 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 an XY 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 XY Charts for Analysis Services
In
Introducing
Reporting Services Charts for Analysis Services, we learned that the XY (Scatter) chart type is available in the
following variants:
- Simple Scatter
-
Scatter /
Lines -
Scatter /
Smooth Lines
In
this article we will focus upon the Simple Scatter variant,
although we take up the other variants within relevant contexts in sister articles
of the MSSQL
Server Reporting Services series. We
noted in our introductory article that XY charts are
typically used to compare values
across categories. In
generally describing the type, we observed that the XY chart
presents series as a set of points. Values
are represented by the position of the point within the chart space. Categories
are represented by different points in the chart.
In this article, we will introduce
the basic XY 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 Simple XY chart, we will:
-
Perform a
brief review of the general XY chart type, discussing its variants and
typical uses; -
Introduce the Simple
XY chart variant; -
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 Simple
XY 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 filters
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 a Simple XY 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 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 Simple XY 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 Simple XY 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
the Simple
XY 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 Simple
XY chart report. (They state that they
wish to work with only a limited time span, and only a single Sales Reason’s
data, to make the process quicker and the ultimate chart less crowded, at least
for purposes of working together to create a prototype.) The group has stated
that they want to leverage this “conversion” process to learn more about the
construction and characteristics of Simple XY 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 the accuracy and completeness of
the new report (a capability 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 Simple XY chart report to satisfy the information
consumers.