July 2009

You are currently browsing the monthly archive for July 2009.

Here are some optimization techniques that can be used when creating charts in SQL Server 2008 Reporting Services.  These tips will probably be already known to experienced chart developers, but freshmen charters may spent some time searching how to achieve something before actually finding it, if finding it at all.  I know because I’ve been there myself when I started out and I also know because I’ve seen questions on forums related to this.

Okay, enough vague intro-words, let’s get concrete now.  In the following example I’ll be creating a regular column chart.

X-axis: show all labels

By default, a chart in SSRS will automatically position the labels on the X-axis as it best fits.  However, one of these options includes hiding labels when the chart feels there are too many to show.  As you can guess, this is not always what we want.

Column bar chart without any changes to its default settings

Have a look at the chart above and try to guess what you’re seeing.  Difficult, huh?  Let’s make some changes to it.

First we start with some basics like giving it a clear title.  This chart shows the annual revenue per state/province, split up by store specialty.  The states or provinces shown are those of the selected country.  I would call it “Annual Revenue per State/Province by Specialty for <SELECTED_COUNTRY>”.  As you probably know, the chart’s title can be edited by giving it a single-click.  However, this does not give you the option to build an expression so unless you want to type it all from memory, here’s another option.  You can right-click on the chart’s title.  This gives you a pop-up menu with Title Properties… as one of the options.

Right-click menu on chart title

Selecting that one will give you the Chart Title Properties where you have the familiar Expression Builder icon next to Title Text textbox.

Chart Title Properties

This is my expression:

=“Annual Revenue per State/Province by Specialty for ” & Parameters!Country.Value

Also, let’s move the legend to the upper middle to make extra horizontal space for all those bars.  This can be done through the Legend Properties.

Legend Properties

Lastly, now that we’ve freed up some space for the chart area, we’ll modify the X-axis properties so that it shows all labels.

When opening up the axis properties for the X-axis on a column chart you get the Category Axis Properties screen.  Like all other property screens, this is also one with several pages.  You see that one of the pages is called Labels and as you want it to show all labels, that’s were you start looking.  Well, stop looking, that’s the wrong place.  The option that you need is located in the first page, the Axis Options, and its called Interval.  This is the interval between each label on the axis, and by default it is set to Auto.  As we want all labels, change it to 1.

Category Axis Properties

Following screenshot shows what the result looks like:

Column chart showing all labels on category axis

Better, but we’re not quite there yet.

X-axis: rotate labels in all directions

Right now the labels on the X-axis are difficult to read unless we turn our screen 90 degrees clockwise.  Let’s dive again into the Category Axis Properties to put them diagonally.  On the Labels page there’s an option to specify the Label Rotation Angle.   To be able to do this you need to activate the Disable auto-fit radio button.  Putting 45 as value will give the following:

Labels rotated 45 degrees

The labels are rotated 45 degrees.  But not in the direction that I would prefer.  It’s nicer when they’re positioned from bottom-left to top-right.  So you start increasing the rotation value.  However, once you’re past 90 you notice that the labels stay vertically, so 135 degrees does not put them in the direction as you’d hoped.  The answer is quite simple, once you know it.  As we want the opposite of our 45 degrees, and it’s not 135, try –45 :-)

Category Axis Properties with Label rotation angle set to a negative value

Indeed, that gives the following result (I’ve also removed the X-axis title).

Column chart with optimized X-axis

To finalize the X-axis optimization, I’ve added sorting so that the States/Provinces are sorted alphabetically.  This is a recommended design practice to keep your different charts consistent.

To get the labels sorted, you should not look into the Axis properties but in the Category Group Properties:

Pop-up menu to get to the Category Group Properties

On the Sorting page just add the field that’s shown on the X-axis.

Category Group Properties with sorting

Y-axis: make numbers readable

So, now that we’re done with the X-axis, let’s move on to the next letter of the alphabet.  Our Y-axis (also known as Value Axis on a column chart) is not very readable at the moment.  The numbers are too large and there’s no formatting.  You don’t even see that this is a currency value.

This time we need the Number page of the Value Axis Properties.  We want:

  • no decimals
  • a thousands separator
  • the numbers divided by 1000 (that’s the “Show values in” setting)
  • a dollar sign in front of the value

To get all this, you can set the options as shown in the screenshot:

Value Axis Properties optimized for large currencies

And here’s our chart again:

Chart with optimized Y-axis

Additionally, the axis title has also gotten a clear value.  It indicates how the values should be read.

Y-axis: move to right-hand side

To conclude this article I’ll show you how to move the Y-axis to the other side of the chart.  This may seem like an odd thing to do, but sometimes the business people want to display a chart that way so you’d better have strong convincing skills or know how to do it.  Or both :-)

In fact, a chart has got two Value axes and two Category axes, a Primary and a Secondary.  The Primary axes are the ones used right now.  To move the visible axis to the other side, it’s not through the Value Axis Properties as you might think (I think you’re getting the hang of it now, right? ;-) .  No, all you need to do is move all data series to the secondary axis.  This can be done through the Series Properties.

Pop-up menu on data series

The Axis and Chart Area page is the one you’re after.

Series Properties with Secondary Value axis activated

That will give us the following end result:

Column chart with Y-axis on the right-hand side

BTW: you will need to re-do the axis formatting when you switch to the secondary axis so better start with axis activation before formatting it.

Happy charting!


Tags: , , , ,

For this example I’ll be using the Adventure Works cube running on SQL Server Analysis Services 2008 and Reporting Services 2008.

In case you don’t have the AdventureWorks databases and cubes yet, they’re available at CodePlex.


The sales department has asked for a report that displays the number of product items sold during a selected period.  As the company is active in two different markets, both the internet and reseller numbers should be shown.  The figures need to be grouped by product category, with drilldown to product level through subcategory.

Besides the period filter, it should be possible to filter on product category to limit the number of items shown.

Also, the background of the numeric cells should get a color depending on the value in the cell.  Colors range from red for low sales figures to green for high sale volumes.  The ranges are variable and should thus be configurable using 3 threshold parameters.  Following table shows the ranges as the department has requested them:

Value X Background Color
X < LowThreshold Red
LowThreshold <= MiddleThreshold Orange
MiddleThreshold <= X < HighThreshold Yellow
HighThreshold < X Green


Selecting The Data

At first sight this seems like a fairly simple report.  So you start building your MDX query using the Query Designer:

MDX Query Designer

Two filters have been specified: one of them is a date range and the other is based on product category.

Visualizing The Data

Then you drag a tablix onto the report body and play around with it until you get to the following:

Tablix in design mode

This is what it looks like when rendered, all seems to work fine:

Rendered report

To get the textbox background coloured based on the thresholds, you’ve produced an expression.  This expression is specified in Textbox Properties > Fill > Fill Color and looks like the following:

    Fields!Internet_Order_Quantity.Value < Parameters!LowThreshold.Value, "#ff0e0e",
    Fields!Internet_Order_Quantity.Value >= Parameters!LowThreshold.Value
        and Fields!Internet_Order_Quantity.Value < Parameters!MiddleThreshold.Value, "#ff922d",
    Fields!Internet_Order_Quantity.Value >= Parameters!MiddleThreshold.Value
        and Fields!Internet_Order_Quantity.Value < Parameters!HighThreshold.Value, "#fff70f",
    Fields!Internet_Order_Quantity.Value >= Parameters!HighThreshold.Value, "#5cff21"

It’s a simple Switch statement using the threshold parameters.

A Missing Field Issue

So you deploy your report to the server for the users to test.  All is quiet, until someone starts complaining that the colouring doesn’t always work, for instance when filtering on Components.  Of course, you don’t always believe what the user says and try it out for yourself:

Rendered report with missing field issue

Indeed, the background is no longer coloured for the internet sales.  On top of that, the BIDS shows a couple of warnings in its output window:

[rsMissingFieldInDataSet] The dataset ‘ProductSales’ contains a definition for the Field ‘Internet_Sales_Amount’. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The dataset ‘ProductSales’ contains a definition for the Field ‘Internet_Sales_Amount’. The data extension returned an error during reading the field. There is no data for the field at position 4.

Hang on, but I am selecting the field in my dataset, how can it be missing?  Except, this is MDX and OLAP, not SQL and OLTP.  By default, the MDX Query Designer uses NON EMPTY in the SELECT statement.  This means that the rows where there are no values for the selected measures will not be contained in the result set.  It also means that the complete measure will be omitted in the case that there are no values for it in any of the rows, which is the reason for our problem.

You could choose to not use NON EMPTY in the query.  To achieve this using the designer, right-click in the results pane and click the Include Empty Cells item.

MDX Query Designer result pane popup menu

Keep in mind that this will result in more rows in your result set because you’re now selecting all the empty measure cells as well.  Depending on your report requirements this may not be the desired effect.  On the other hand, it could be exactly what you want.  If our sales department had asked that the report should always show all products, even when there are no sales for the period, then we’d need to query the cube in this way.

For the sake of the example (and to save some trees in case the sales department is going to print the report :-) ) we will not choose this option.

Attempt to fix #1

As the field does not always exist, you decide that it’s a good idea to test for its existence.  A field in a resultset has an IsMissing property which serves that purpose.  So you adapt your expression to the following:

=IIF(Fields!Internet_Order_Quantity.IsMissing, Nothing,
        Fields!Internet_Order_Quantity.Value < Parameters!LowThreshold.Value, "#ff0e0e",
        Fields!Internet_Order_Quantity.Value >= Parameters!LowThreshold.Value
            and Fields!Internet_Order_Quantity.Value < Parameters!MiddleThreshold.Value, "#ff922d",
        Fields!Internet_Order_Quantity.Value >= Parameters!MiddleThreshold.Value
            and Fields!Internet_Order_Quantity.Value < Parameters!HighThreshold.Value, "#fff70f",
        Fields!Internet_Order_Quantity.Value >= Parameters!HighThreshold.Value, "#5cff21"

However, when filtering on Components the same problem still occurs.  How can this be?  Expressions in SSRS are built using Visual Basic where expressions are evaluated completely.  In our case both the True and the False part of the IIF function are evaluated even when it will always be true.

On to another attempt to get this working.

(Attempt to) fix #2

The previous fix attempt has shown that it’s not possible to use an expression for the field validity test.  At least, not in the way we’ve tried until now.  Let’s try using custom code.

Custom code can be added to a report through the Code page in the Report Properties dialog box (accessible through the menu Report > Report Properties… or by right-clicking the report’s yellow background).

Report Properties > Code dialog box

Let’s start with a small extra requirement.  When a measure is not present in a row, such as the Internet Order Quantity for the products in the Components category, the report should display a zero instead of blank space.  To get this done we again need to test on whether or not the field exists in the result set.

The following Visual Basic function accepts a Field object and returns the value of the field when the field exists or zero when the field does not exist.

'returns the field's value or zero if the field does not exist
Public Function GetValue(field as Field) as Long
  If (field.IsMissing) Then
    Return 0
  ElseIf (IsNothing(field.Value)) Then
    Return 0
    Return field.Value
  End If
End Function

This function can now be used in an expression anywhere in the report.  Here’s what the expression looks like for the Value of the textbox that shows the Internet Order Quantity:


The same expression is used for the textboxes that display the sum values:


Attention: the function calls above are passing the actual Field object, not the Value property of the field, so not Fields!Internet_Order_Quantity.Value.

So, on to getting our coloring working as required.  For this we need a function that returns the right color for the given amount.  Something like this:

Public Const ColorLow As String = "#ff0e0e"      'red
Public Const ColorLowMid As String = "#ff922d"   'orange
Public Const ColorMidHigh As String = "#fff70f"  'yellow
Public Const ColorHigh As String = "#5cff21"     'green

Public Function GetColor(field as Field, low as Integer, mid as Integer, high as Integer) as String
  If (field.IsMissing) Then
    Return ColorLow
  ElseIf (IsNothing(field.Value)) Then
    Return ColorLow
    Select Case field.Value
      Case Is < low
        Return ColorLow
      Case Is < mid
        Return ColorLowMid
      Case Is < high
        Return ColorMidHigh
      Case Is >= high
        Return ColorHigh
    End Select
  End If
End Function

This function accepts a field plus the three threshold values.  Depending on the value of the field and the thresholds, the expected color string is returned.  The red color is returned as well when the field does not exist.

As a good coding practice I’ve created constants for the color strings.  This method allows you to define constants that are available in the whole report – could be interesting if the same colors are used in different parts of a report for instance.

This is the expression used for the BackgroundColor property of the TextBox:


Again the actual Field object gets passed as first parameter, not just the value.

If we now run the report with a filter on Components, the warnings will still appear in the Output window, but the report will function as expected as the following screenshot shows.  (No Photoshop was used in the making of this screenshot.)  Instead of empty cells the report shows zeroes and the background is coloured even when there are no sales.

Rendered report - fully functional

Extra info can be found in the MSDN page about Using Dataset Field Collection References in Expressions.


Tags: , , , , , , ,

© 2008-2019 BI: Beer Intelligence? All Rights Reserved