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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 21, 2009

Stacked Bar Chart for Analysis Services Data

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date