Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 29, 2008

Introducing Reporting Services Charts for Analysis Services

By William Pearson

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.


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


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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM