Stacked Column Chart for Analysis Services Data

Friday Mar 20th 2009 by William Pearson
Share:

BI Architect Bill Pearson leads the hands-on creation of a Stacked Column chart based upon an Analysis Service data source.

This article focuses upon Stacked Column 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, 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. Among the types offered, the Stacked Column chart type is perhaps one of the most popular. (We introduced the simple Column chart in another article in this series, Column Chart for Analysis Services Data.

In this article, we will introduce the Stacked Column 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 Column 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 Column 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 Column Charts for Analysis Services

In Introducing Reporting Services Charts for Analysis Services, we learned that the Column chart type is available in the following variants:

  • Stacked Column
  • Stacked Column
  • 100% Stacked Column

In this article we will focus upon the Stacked Column 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 Column charts are typically used to compare values between categories. In generally describing the type, we observed that the Column chart presents values and series groups as sets of vertical columns that are grouped by category. Values, within the Column chart type, are represented by the height of the columns (as measured by the y-axis). Category labels are displayed on the x-axis. By contrast, a Stacked Column chart displays all series, stacked into a single column, for each category. The height of each column is determined by the total of all series values for the respective category.

In this article, we will introduce the Stacked Column 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 Stacked Column chart, we will:

  • Perform a brief review of the general Column chart type, discussing its variants and typical uses;
  • Introduce the Stacked Column chart variant, comparing and contrasting, where useful, features of the Stacked Column chart and the simple Column chart types;
  • 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 Stacked Column 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 a Stacked Column 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 Stacked Column 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 Column 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 Stacked Column 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, Stacked Column chart report. The group has stated that they want to leverage this “conversion” process to learn more about the construction and characteristics of Stacked Column 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 Column chart report to satisfy the information consumers.

Practice

Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we will perform the addition and setup of the Stacked Column Chart data region, for the reasons noted above. (We typically work with a similar report copy in 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 session 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:

Open the Sample Report Server Project

Ascertain Connectivity of the Shared Data Source

Let’s ensure we have a working data source. Some of us may 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:

Ascertain Connectivity of the Analysis Services Data Source

Create a Copy of the Sales Reason Comparisons Report

We will begin with a copy of the Reporting Services 2005Sales Reason Comparisons” OLAP report, which we will use as a basis for our Stacked Column chart type practice exercise. Creating a “clone” of the report means we can make changes to select components (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:

Create a Copy of a Sample OLAP Report

With a clone Analysis Services report file within our Reporting Services 2005 Project, we are ready to begin our hands-on practice session with the Stacked Column chart type, which we will accomplish in the next section.

Preparation: Modify the Analysis Services Report Clone for Use within Our Practice Session

We will next make a few modifications to prepare the report for our practice session. 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 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.

Filter the Primary Dataset to Limit the Data Retrieved

First, we will modify the primary dataset underlying the existing report, called ProductData. We will simply place a filter on returned data to limit the size of our ultimate report to two (versus four) Calendar Years’ activity.

1.  Click the Data tab.

2.  Ensure that the ProductData dataset appears within the data selector.

3.  On the Metadata tab, within the pane that appears under the dataset selector (which now indicates ProductData), expand the Date dimension by clicking the “+” sign to its immediate left.

4.  Expand the Calendar folder that appears underneath the newly expanded Date dimension.

5.  Click the Date.Calendar Year hierarchy to select it.

6.  Drag the Date.Calendar Year hierarchy to the Query pane, dropping it to the immediate left of the Sales Reason column that is already in place.

The Query pane, with the newly added Calendar Year column, appears as partially presented in Illustration 2.

The Query Pane with the Newly Added Column (Partial View)
Illustration 2: The Query Pane with the Newly Added Column (Partial View)

7.  Click the Edit Selected Dataset (“...”) button to the immediate right of the Dataset selector, as depicted (circled) in Illustration 3.

Editing the Dataset ...
Illustration 3: Editing the Dataset ...

The multi-tabbed Dataset dialog opens.

8.  Click the Filters tab.

9.  In the first row of the Filters box, within the leftmost Expression column, select =Fields!Calendar_Year.Value, as shown in Illustration 4.

Select =Fields!Calendar_Year.Value in the Expression Column ...
Illustration 4: Select =Fields!Calendar_Year.Value in the Expression Column ...

10.  In the Operator column (to the immediate right of the Expression column), select the “>=” operator.

11.  Type (or cut and paste) the following into the Value column (to the immediate right of the Value column):

="CY 2003"

Because we are stating that we want only Calendar Years “greater than or equal to CY 2003,” we know we will retrieve only two years’ data into the dataset (the Adventure Works cube contains Calendar Years 2001 through 2004).

The Filters tab of the Dataset dialog appears as presented in Illustration 5.

The Filters Tab of the Dataset Dialog with Our Additions ...
Illustration 5: The Filters Tab of the Dataset Dialog with Our Additions ...

12.  Click OK to accept our addition, and to dismiss the Dataset dialog.

Modify the Report Layout to Accommodate Our Focus

Next, we will make some changes to the report layout to more easily accommodate the focus of our practice session. We will start with a larger report canvas.

1.  Click the Layout tab atop the Report Designer.

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

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

4.  Enlarge the report canvas to about the 3-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 6.

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

Because, from this point, we will be performing procedures that relate only to the chart type with which we are working in this article, and because we create similar files for different chart types in other articles, let’s rename the .rdl file to clearly associate it with this article and the Stacked Column chart type.

5.  Select File from the main menu.

6.  Select Save DBJ_OLAP_Report.rdl As ....

7.  Type the following into the File name box of the Save File As box that appears next:

RS063_Stacked_Column_Chart

8.  Click the Save button in the lower right corner of the Save File As box.

The Save File As box is dismissed and we see the new name appear in the Solution Explorer. We are now ready to begin the procedural section of our practice session.

Procedure: Create a Stacked Column Chart within the Report

Add a Chart Control to the Report Canvas

At this point, we will place 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). Once the general control is placed on the report canvas, we will adjust it, setting properties as appropriate to meet the report specifications.

1.  Select View from the main menu.

2.  Select Toolbox from the menu, which appears as partially shown in Illustration 7, to place the Toolbox within easy reach (if it already appears, simply disregard this step).

Calling the Toolbox to View (Partial View Menu Shown)
Illustration 7: Calling the Toolbox to View (Partial View Menu Shown)

The toolbox window should appear similar to that depicted in Illustration 8. Mine is pinned to the upper left corner of the design environment, where I find it most convenient. This is, of course, subject to your own choice. (Note also that I dock my Fields and Server Explorer panes in this area to maximize design real estate, as an aside.)

The Toolbox, Pinned to the Upper Left Corner of the Design Environment
Illustration 8: The Toolbox, Pinned to the Upper Left Corner of the Design Environment

3.  Click the Chart item (at the bottom of the Toolbox – Report Items pane).

The cursor becomes a small chart icon in combination with crosshairs when held above the layout. This indicates that we can click to “anchor” the point, from which we wish to draw the box that the chart will inhabit.

4.  Click on the canvas, below the existing matrix, approximately as shown in Illustration 9.

Placing the Chart Selection onto the Report Campus ...
Illustration 9: Placing the Chart Selection onto the Report Campus ...

The chart item appears, in its generic manifestation, as depicted in Illustration 10.


Illustration 10: The Generic Chart Item Appears

NOTE: Should you accidentally place or drop the chart item into a position that is not satisfactory, you can simply move the item by clicking inside the chart item, then pointing to the now shaded border of the chart item, to drag it to a new location.

Clicking again on the border will also allow you to expand / contract the chart shape. (The nuances are easy to learn with a little practice.) Double-clicking the chart item will make the “drop regions,” seen above, appear.

5.  Pass the cursor over the left border of the chart item, until it becomes a four-arrow icon.

6.  Click and drag the chart item to the left side of the report canvas.

7.  Pass the cursor over the right border, and over the small square that appears about midway down the right side, until the cursor becomes a double-headed arrow.

8.  Click and drag the right border of the chart item, widening the chart (along with the underlying canvas) to approximately the “11” point on the ruler appearing atop the Layout tab.

9.  Pass the cursor over the bottom border, and over the small square that appears about midway along the bottom side, until the cursor becomes a double-headed arrow, once again.

10.  Click and drag the bottom border of the chart item, heightening the chart (along with the underlying canvas) to approximately the “8” point on the ruler appearing along the left of the Layout tab.

The chart item is now in place, and we are ready to specify its “Stacked Column chart” nature, as well as to populate it with the primary dataset that is already in place.

Populate the Stacked Column Chart item to Meet the Business Requirements

While our next steps might focus upon simply dragging fields from the Fields pane of the Report Designer, we will populate our chart from inside the multi-tabbed Chart Properties dialog. Because few charts I encounter in the business environment can be built completely through the “drag-and-drop” process anyway (expressions are often involved in combination with the date fields, as an example), I long ago developed the habit of working from the Properties dialog. (The practice of tabbing through the Properties dialog can often remind us of other settings we need to make, additional options that are available in meeting client / employer needs, and so forth, as an another benefit.)

11.  Right-click the generic chart item.

12.  Select Properties from the context menu that appears, as shown in Illustration 11.

Select Properties from the Context Menu ...
Illustration 11: Select Properties from the Context Menu ...

The Properties dialog appears, defaulted to the General tab, where we will begin our examination of the chart properties in the subsections that follow.

General Tab

1.  Type the following into the Title box on the General tab:

Sales Reason Comparisons

2.  Click the Style button, depicted (and also magnified) within the respective red squares in Illustration 12, to the immediate right of the Title box.

Click the Style Button for the Title Box ...
Illustration 12: Click the Style Button for the Title Box ...

The Style Properties dialog box appears.

3.  Make the settings, listed in Table 1 below, within the Style Properties dialog box:

Property

Setting

Family

Arial

Size

12pt

Style

Italic

Weight

Bold

Color

Black

Decoration

None

Table 1: Style Properties Dialog

The Style Properties dialog appears, with our settings, as shown in Illustration 13.


Illustration 13: Style Properties Dialog Box with Settings

4.  Click OK to accept changes and to exit the Style Properties dialog box.

We return to the General tab.

5.  Ensure that Column is selected in the Chart type selection list.

6.  Ensure that the middle selection, Stacked Column chart, is highlighted) among the context-sensitive Chart sub-type selections to the immediate right of the Chart type selection list.

7.  Click the Chart Area Style... button, which appears in the bottom left corner of the General tab.

We can manipulate settings for chart borders and lines via the Borders and Lines tab, depicted with default settings in Illustration 14.

Style Properties Dialog Box – Border and Line Tab (with Default Settings)
Illustration 14: Style Properties Dialog Box – Border and Line Tab (with Default Settings)

8.  Click the Fill tab to expose the chart Fill settings, which appear (with defaults) as shown in Illustration 15.

Style Properties Dialog Box – Fill Tab (with Default Settings)
Illustration 15: Style Properties Dialog Box – Fill Tab (with Default Settings)

9.  Click the OK button at the bottom of the Style Properties... dialog, to dismiss the dialog with all properties settings at default.

We return to the General tab, once again.

10.  Click the Plot Area Style... button, which appears to the immediate right of the Chart Area Style... button at the bottom of the General tab.

The two-tabbed Style Properties dialog that appears is identical to that which we saw when we clicked the Chart Area Style... button in the immediately preceding steps. Since we will be leaving these settings, too, at their defaults, we will dismiss the Style Properties dialog at this point.

11.  Click the OK button at the bottom of the Style Properties dialog, to dismiss the dialog with all properties settings at default.

We return to the General tab, once again, which appears, with our settings, as depicted in Illustration 16.

Chart Properties Dialog Box – General Tab with Our Settings
Illustration 16: Chart Properties Dialog Box – General Tab with Our Settings

We will move to the Data tab in the subsection that follows.

Data Tab

1.  Click the Data tab.

2.  Using the downward pointing arrow on the right side of the Dataset name box atop the Data tab, display and select the ProductData dataset, as shown in Illustration 17.

Select the ProductData Dataset ...
Illustration 17: Select the ProductData Dataset ...

3.  Click the Add... button to the right of the Values list box, found immediately underneath the Dataset name selector we populated above.

The values in a Value series determine the height of the columns in that series. Value labels appear on the y-axis (we will examine format settings for these in our subsection on the Y Axis tab below). Each Value series appears as individual columns.

The Edit Chart Value dialog appears, defaulted to the Values tab.

4.  Type the following into the Series label box, atop the Values tab:

Internet Sales

5.  Click the function (fx) button to the right of the Value box, depicted within a circle in Illustration 18.

Click the Function Button for the Value Box ...
Illustration 18: Click the Function Button for the Value Box ...

The Expression Editor opens.

6.  Click the Fields (ProductData) option in the bottom left corner pane of the Expression Editor.

The field values of the ProductData dataset appear within the pane to the bottom right of the Expression Editor.

7.  Double-click Internet_Sales_Amount to place this selection into the main pane atop the Expression Editor.

The Expression Editor appears, with our input, as shown in Illustration 19.

The Expression Editor with Our Input ...
Illustration 19: The Expression Editor with Our Input ...

8.  Click OK to accept our input and to dismiss the Expression Editor.

We are returned to the Edit Chart Value dialog, which appears with our input as depicted in Illustration 20.

The Edit Chart Value Dialog, with Our Input...
Illustration 20: The Edit Chart Value Dialog, with Our Input...

9.  Click the Appearance tab on the Edit Chart Value dialog.

10.  Click the Series Style... button in the lower left corner of the Appearance tab.

The dual – tabbed Style Properties dialog appears again, just as it did for the Chart Area Style... and Plot Area Style... buttons we examined earlier. We see the same Border and Line and Fill tabs, which we will leave at default at this point.

11.  Click OK to dismiss the Style Properties dialog, and to return to the Appearance tab of the Edit Chart Value dialog.

12.  Click the Point Labels tab on the Edit Chart Value dialog.

13.  Click the check box to the immediate left of Show point labels atop the Point Labels tab.

14.  Click the downward pointing arrow to the right of the Data label box to display data selection options.

15.  Select =Fields!Internet_Sales_Amount.Value from the options, as shown in Illustration 21.

Select =Fields!Internet_Sales_Amount.Value as the Data Label ...
Illustration 21: Select =Fields!Internet_Sales_Amount.Value as the Data Label ...

16.  Within the Format code box (just underneath the Data label selector) type the following character pattern:

#,###

17.  Uncheck the Auto checkbox to the right of the Format code box.

18.  Leaving other settings at default, click the Label Style ... button immediately underneath the Angle setting.

The Style Properties dialog box appears.

19.  Make the settings, listed in Table 2 below, within the Style Properties dialog box:

Property

Setting

Family

Arial

Size

8pt

Style

Normal

Weight

Normal

Color

Red

Decoration

None

Table 2: Style Properties Dialog

The Style Properties dialog appears, with our settings, as depicted in Illustration 22.

Style Properties Dialog Box with Settings
Illustration 22: Style Properties Dialog Box with Settings

20.  Click OK to accept settings.

The Style Properties box closes. We return to the Edit Chart Value dialog box, which now appears as shown in Illustration 23.

The Edit Chart Value Dialog Box, with Our Input
Illustration 23: The Edit Chart Value Dialog Box, with Our Input

21.  Click the Action tab of the Edit Chart Value dialog.

Here we can define Hyperlink actions, including Jump to report, Jump to bookmark, or Jump to URL. We will not be working with a drillthrough scenario in this practice session, and so will leave undisturbed the default setting of None, as depicted in Illustration 24.


Illustration 24: The Action Tab of the Edit Chart Value Dialog Box

22.  Click the Data Output tab of the Edit Chart Value dialog.

The Data Output tab affords us a means of defining XML data output options for the values in the chart. We will not leverage these capabilities within this introduction to Stacked Column charts, and will therefore leave the settings of the Data Output tab at their defaults, as shown in Illustration 25.

The Data Output Tab with Default Settings
Illustration 25: The Data Output Tab with Default Settings

23.  Click the OK button at the bottom of the Edit Chart Value dialog, to dismiss the dialog with our settings.

We are returned to the Data tab of the Chart Properties dialog. Next we will work with the Category groups area of the tab, just under the Values area. Categories are displayed as columns or groups of columns on the X-axis. Multiple groups are nested, as we shall see within our current practice example.

24.  Click the Add... button to the right of the Category groups list box, found immediately underneath the Values list box with which we worked above.

The Grouping and Sorting Properties dialog appears, defaulted to the General tab.

25.  Type (or cut and paste) the following into the Name box, atop the General tab:

chart1_Cat1_Year

26.  Click the top row of the Expression box within the Group on section that appears just underneath the Name box, to enable the selector button (the downward-pointing arrow) on the right side of the box.

27.  Select =Fields!Calendar_Year.Value from the options that appear, as depicted in Illustration 26.

Select =Fields!Calendar_Year.Value in the Top Row of the Expression List ...
Illustration 26: Select =Fields!Calendar_Year.Value in the Top Row of the Expression List ...

As we have no input for the Filters or Data Output tabs (which resemble their counterparts in other areas of the Report Designer, as we have, and shall, see), within the context of this newly added group, and as we intend to leave the Sorting at the settings naturally found within the cube structure (the Calendar Years will sort in numerical order), we will accept our input at this stage.

The Grouping and Sorting Properties dialog (General tab) appears, with our modifications, as shown in Illustration 27.


Illustration 27: The Grouping and Sorting Properties Dialog with Our Modifications

28.  Click the OK button at the bottom of the Grouping and Sorting Properties dialog, to save our settings and to dismiss the dialog.

We are returned to the Data tab of the Chart Properties dialog. Next we will add another Category group to our report.

29.  Click the Add... button to the right of the Category groups list box, just as we did before.

The Grouping and Sorting Properties dialog appears, once again, defaulted to the General tab.

30.  Type the following into the Name box, atop the General tab:

chart1_Cat2_Territory

31.  Click the top row of the Expression box within the Group on section, once again, to enable the selector button (the downward-pointing arrow) on the right side of the box.

32.  Select =Fields!Sales_Territory_Group.Value from the options that appear, as depicted in Illustration 28.

Select =Fields!Sales_Territory_Group.Value in the Top Row of the Expression List ...
Illustration 28: Select =Fields!Sales_Territory_Group.Value in the Top Row of the Expression List ...

The Grouping and Sorting Properties dialog appears, with our modifications, as shown in Illustration 29.

The Grouping and Sorting Properties Dialog with Our Modifications
Illustration 29: The Grouping and Sorting Properties Dialog with Our Modifications

As we have no input for the Filters or Data Output tabs within the context of this newly added group, and as we intend to leave the Sorting at the settings naturally found within the cube structure (the Territory Groups will sort in natural, alphabetical order), we will accept our input at this stage, just as we did with the previously added Category group, chart1_Cat1_Year.

33.  Click the OK button at the bottom of the Grouping and Sorting Properties dialog, to save our settings and to dismiss the dialog.

We are returned to the Data tab of the Chart Properties dialog, once again. Our next step will be to add another group – this time a Series group. A Stacked Column chart displays all series, stacked into a single column for each respective category to which they relate. The height of each column is determined by the total of all series values for the category involved. Each series is also displayed in the chart legend.

At this point, we will add a Series group to contain the Sales Reason (Manufacturer, On Promotion, Price, etc.) that is associated with the grouped Internet Sales totals that the report will present.

34.  Click the Add... button to the right of the Series groups list box, much as we did in making our two Category groups entries earlier.

The Grouping and Sorting Properties dialog appears, once again, defaulted to the General tab.

35.  Type (or cut and paste) the following into the Name box, atop the General tab:

chart1_Series1_Reason

36.  Click the top row of the Expression box within the Group on section, yet again, to enable the selector button (the downward-pointing arrow) on the right side of the box.

37.  Select =Fields!Sales_Reason.Value from the options that appear, as depicted in Illustration 30.

Select =Fields!Sales_Reason.Value in the Top Row of the Expression List ...
Illustration 30: Select =Fields!Sales_Reason.Value in the Top Row of the Expression List ...

The Grouping and Sorting Properties dialog appears, with our modifications, as shown in Illustration 31.

The Grouping and Sorting Properties Dialog with Our Modifications
Illustration 31: The Grouping and Sorting Properties Dialog with Our Modifications

Because we again have no input for the Filters or Data Output tabs within the context of this newly added group, and as we intend to leave the Sorting at the settings naturally found within the cube structure (the Sales Reasons, too, will sort in natural, alphabetical order), we will accept our input at this stage, just as we did with the previously added Category groups, chart1_Cat1_Year and chart1_Cat2_Territory.

38.  Click the OK button at the bottom of the Grouping and Sorting Properties dialog, to save our settings and to dismiss the dialog.

39.  We return to the Data tab, once again, which appears, with our Values, Category groups, and Series groups settings, as depicted in Illustration 32.

Chart Properties Dialog Box – Data Tab with Our Settings
Illustration 32: Chart Properties Dialog Box – Data Tab with Our Settings

We will move to the X Axis tab in the subsection that follows.

X Axis Tab

1.  Click the X Axis tab.

We advise our client colleagues that the labels assigned to the groups that we created earlier will suffice to address the visual x-axis, and that we therefore need only to deal with the “labels” settings (we can leave the Title and other settings at default).

2.  Ensure that the Show labels checkbox is checked.

3.  Click the Style button to the immediate right of the blank Format code box.

The Style Properties dialog box appears, once again.

4.  Make the settings, listed in Table 3 below, within the Style Properties dialog box:

Property

Setting

Family

Arial

Size

9pt

Style

Normal

Weight

Normal

Color

Black

Decoration

None

Table 3: Style Properties Dialog

The Style Properties dialog appears, with our settings, as shown in Illustration 33.

Style Properties Dialog Box with Settings
Illustration 33: Style Properties Dialog Box with Settings

5.  Click OK to accept changes and to exit the Style Properties dialog box.

We return to the X Axis tab, once again, where we will leave all else at default. The X Axis tab appears, with our settings, as depicted in Illustration 34.

Chart Properties Dialog Box – X Axis Tab with Our Settings
Illustration 34: Chart Properties Dialog Box – X Axis Tab with Our Settings

We will move to the Y Axis tab in the next subsection.

Y Axis Tab

1.  Click the Y Axis tab.

We propose doing a little more “adjustment” to the default settings in the Y Axis tab than we witnessed in its X Axis counterpart in the section above. These modifications will be primarily in the interest of presentation compactness and clarity.

2.  Type ( or cut and paste) the following into the Title box on the tab:

="Total Internet "& vbcrlf &" Sales" &""

Here we are adding the title “Total Internet Sales”, using the “vbcrlf” keyword within the expression to force the word “Sales into a second row of the title, so that the title takes up less vertical space.

3.  Click the Style button to the immediate right of the Title box.

The Style Properties dialog box appears, as we have seen in earlier sections.

4.  Make the settings, listed in Table 4 below, within the Style Properties dialog box:

Property

Setting

Family

Arial

Size

9pt

Style

Normal

Weight

Normal

Color

Black

Decoration

None

Table 4: Style Properties Dialog

The Style Properties dialog appears, with our settings, as shown in Illustration 35.

Style Properties Dialog Box with Settings
Illustration 35: Style Properties Dialog Box with Settings

5.  Click OK to accept changes and to exit the Style Properties dialog box.

We return to the Y Axis tab.

6.  Ensure that the Show labels checkbox is checked.

7.  Type the following into the Format code box:

#,###

8.  Click the Style button to the immediate right of the newly occupied Format code box.

The Style Properties dialog box appears, once again.

9.  Make the settings, listed in Table 5 below, within the Style Properties dialog box:

Property

Setting

Family

Arial

Size

9pt

Style

Normal

Weight

Normal

Color

Black

Decoration

None

Table 5: Style Properties Dialog

The Style Properties dialog appears, with our settings, as depicted in Illustration 36.

Style Properties Dialog Box with Settings
Illustration 36: Style Properties Dialog Box with Settings

10.  Click OK to accept changes and to exit the Style Properties dialog box.

We return to the Y Axis tab, once again, where we will leave all else at default. The Y Axis tab appears, with our settings, as shown in Illustration 37.

Y Axis Properties Dialog Box – General Tab with Our Settings
Illustration 37: Y Axis Properties Dialog Box – General Tab with Our Settings

We will move to the Legend tab next.

Legend Tab

1.  Click the Legend tab.

We advise our client colleagues that, since the default position for the legend is to the right of the chart area, and since our Stacked Column chart may be wider than expected, due to its nature and the amount of data we are presenting, placing the legend underneath the chart will offer another means of compressing the overall presentation.

2.  Ensure that the Show legend checkbox is checked, in the upper left corner of the tab.

3.  Ensure that the Column radio button is selected, in the upper left corner of the tab.

4.  Click the bottom middle button underneath the Position selection diagram, to align the Legend box underneath the Column chart area.

5.  Click the Legend Style button that appears immediately beneath the checkbox labeled Display legend inside plot area (ensure that the box remains unchecked).

The Style Properties dialog box appears, defaulted to the Font tab.

6.  Make the settings, listed in Table 6 below, within the Font tab of the Style Properties dialog box:

Property

Setting

Family

Arial

Size

9pt

Style

Normal

Weight

Normal

Color

Black

Decoration

None

Table 6: Style Properties Dialog – Font Tab

The Font tab of the Style Properties dialog appears, with our settings, as depicted in Illustration 38.

Font Tab of the Style Properties Dialog Box, with Settings
Illustration 38: Font Tab of the Style Properties Dialog Box, with Settings

We note the presence of the Border and Line and Fill tabs, but we will leave the settings within each at default, at this point.

7.  Click OK to accept changes, and to exit the Style Properties dialog box.

We return to the Legend tab, once again, where we will leave all else at default. The Legend tab appears, with our settings, as shown in Illustration 39.

Chart Properties Dialog Box – Legend Tab with Our Settings
Illustration 39: Chart Properties Dialog Box – Legend Tab with Our Settings

We will move to the 3D Effect tab in the next subsection.

3D Effect Tab

1.  Click the 3D Effect tab.

We inform our client colleagues that the 3D Effect tab offers us a means of transforming the ordinarily “flat” appearance of our Column chart to a highly customizable, three – dimensional presentation. Here we can enable 3-D visual effects (via the checkbox to the immediate left of the Display chart with 3-D visual effect label). The four variables that we can manipulate are:

  • Horizontal rotation
  • Perspective
  • Wall thickness
  • Vertical rotation

Once 3-D visual effects are enabled, a slider becomes enabled for each of these variables, which we can move to adjust each variable until we achieve just the degree of readability we desire within the chart.

We will leave the settings of the 3D Effect tab at default at this point. The 3D Effect tab appears, with default settings, as depicted in Illustration 40.

Chart Properties Dialog Box – 3D Effect Tab with Our Settings
Illustration 40: Chart Properties Dialog Box – 3D Effect Tab with Our Settings

We will examine the final remaining tab, Filters, in the next subsection.

Filters Tab

1.  Click the Filters tab.

The Filters tab is but one option we have, within Reporting Services, to filter the data that is displayed within our chart. We advise our client colleagues that we performed all desired filtering at the dataset level earlier within our practice session. When this is adequate (that is, when we can afford to filter at the dataset level for the entire report) we may achieve performance gains at report runtime, due to the overall retrieval of less data from the Analysis Services data source. But, we caution the client representatives, due consideration should be given to the various points at which we can filter within a given report, to ascertain that we optimize performance while retaining complete and accurate information for presentation.

The Filters tab allows us to choose either dataset columns or expressions to filter data at the chart level. This tab might make sense as a filter point if we were, say, using multiple data regions (charts, matrices, tables, lists, or a combination of these, perhaps) that were sharing the same common dataset(s), but where each region had different filtering requirements and needed to present different subsets of data from the underlying dataset(s). Whatever our needs, Reporting Services, once again, offers flexibility in ways to meet the challenges involved.

The Filters tab appears, with default settings, as shown in Illustration 41.

Chart Properties Dialog Box – Filters Tab with Our Settings
Illustration 41: Chart Properties Dialog Box – Filters Tab with Our Settings

2.  Click OK to accept all the settings we have made in the multi-tabbed Chart Properties dialog box.

The Chart Properties dialog closes, returning us to the placeholder chart item in Report Designer, Layout tab. We will conclude our practice session in the next section, where we will verify the operation of our new Stacked Column chart.

Verify Operation of the Stacked Column Chart item

Let’s ascertain the accuracy and completeness of our construction efforts. We will execute the report with the following steps:

1.  Click the Preview tab, to the right of the Layout tab, atop the design surface.

2.  Select Bikes within the Product Category report parameter picklist, as depicted in Illustration 42.

Select Bikes as the Product Category ...
Illustration 42: Select Bikes as the Product Category ...

3.  Click the View Report button.

The new report generates, displaying both the original matrix and new Column chart data regions.

4.  Scroll down, as required to focus upon the new Stacked Column chart data region.

The new Stacked Column chart appears as shown in Illustration 43.

The Stacked Column Chart Report, Preview Tab
Illustration 43: The Stacked Column Chart Report, Preview Tab

We can easily verify the displayed Stacked Column chart totals against the matrix data region (by adding together each of the two years’ totals for any given Territory Group / Sales Reason, and comparing that total to the corresponding total in the matrix data region).

Our Stacked Column chart meets the expressed business requirements and demonstrates many details surrounding its property settings. The client representatives express satisfaction with our efforts, and state that, with a few cosmetic changes (including the removal of the existing matrix data region; the subsequent realignment of the Stacked Column chart on the canvas; and conditional font formatting, perhaps, to make the font color vary with the background colors of the respective stacks, so as to make the values easier to read in cases where the currently fixed font color is too similar to that of the respective stack), the report will be ready for deployment to the targeted information consumer group. Moreover, they assure us that the details they have examined within the practical exercise we have undertaken can be extrapolated to their creation efforts of other Stacked Column charts.

5.  Experiment further with the report, if desired.

6.  When finished with the report, click the Layout tab.

7.  Select File -> Save RS063_Stacked_Column_Chart.rdl As ... to save our work, up to this point, to a location where it can be easily accessed for later reference.

As we can see from our examination above, Reporting Services offers a wide range of options for Stacked Column chart creation and manipulation to assist us in the delivery of information within the business environment. We extend our examination of chart types, specifically examining each type, together with the properties and methods we can manipulate for the precise presentations we seek to be able to deliver, in other articles of this series.

9.  Select File -> Exit to leave the design environment, when ready (saving as desired), and to close the Business Intelligence Development Studio.

Conclusion

In this article, we performed a relatively straightforward examination of the Reporting Services Stacked Column chart type, from within a copy of an existing sample Reporting Services 2005 report that we created for this purpose. Our focus, as we stated in the introduction, was to create a basic, working Column chart, using an Analysis Services data source (the Adventure Works DW sample OLAP database / Adventure Works cube that accompanies the installation of Reporting Services), and to discuss various characteristics of the Column chart type as we progressed.

We examined relevant chart properties, and got some 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. We noted that this article might serve as a basis for other, more advanced articles within the MSSQL Server Reporting Services series, from which we use the Stacked Column chart we created here as a platform from which to concentrate on in-depth procedures and nuances that we can use to achieve precision in meeting specific requirements and data presentation effects that we might deliver in the business environment.

About the MSSQL Server Reporting Services Series ...

This article is a member of the series MSSQL Server Reporting Services. This monthly column is designed to introduce MSSQL Server Reporting Services (“Reporting Services”), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

» See All Articles by Columnist William E. Pearson, III

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved