Wednesday, February 22, 2012

Improve Data Visualization in your SSRS Charts: Part 1

Chart Areas are a feature of SSRS Chart objects that can be utilized in enhancing data visualization in your charts.  I demonstrated this in a recent webinar, but I thought documenting the process might make it clearer on how to set this up to get several more visualizations under your belt. Chart Areas allow you to turn a single chart into a Trellis chart, a Correlated Bar Graph (coined term from Stephen Few's book Show Me The Numbers), or even a Bullet Graph.

Prepare the Data and Initial Chart
Let's start off with a common dataset from the Contoso BI Dataset Cube that we'll use throughout this post. The following query results in the 2009 Monthly Actual Sales and Sales Forecast for Contoso. First create a new SSRS report in Business Intelligence Development Studio (BIDS) with a Data Source pointing to the Contoso cube. Create a new DataSet, and copy/paste the following into your Query text:

SELECT NON EMPTY { 
[Measures].[Sales Quota Amount], 
[Measures].[Sales Amount] } ON COLUMNS, 
NON EMPTY { 
([Date].[Calendar Month].[Calendar Month].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM ( SELECT ( { [Scenario].[Scenario Name].&[3] } ) ON COLUMNS 
FROM ( SELECT ( { [Date].[Calendar Year].&[2009] } ) ON COLUMNS 
FROM [Sales])) 
WHERE ( [Date].[Calendar Year].&[2009], [Scenario].[Scenario Name].&[3] ) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
If you're used to using the Query Designer, your dataset should look something like this:
Figure 1. Query Designer with the layout of Contoso's 2009 Actual/Forecast Sales
In Query Designer, drag and drop the fields highlighted yellow into the Data section, and drag the Date/Calendar Year, and Scenario/Scenario Name attributes into the filter section as shown in the figure picking the Filter Expressions shown (Year 2009 & Forecast). Once completed either through Query Text or Query Designer, press the 'Refresh Fields' button in the Dataset properties. If there are no errors, then you can now proceed with creating the initial chart.
Our initial chart will be a column graph of the Sales Amount vs. the Calendar Months. To do this, insert a chart onto the design surface, drag and drop the Calendar_Month field to the Category Groups in the Chart Data pane, and then drag and drop the Sales_Amount column into the 'Ʃ Values' section of the Chart Data pane. Delete the Legend and your Axis Titles on the Vertical Axis and Horizontal Axis. Change the Chart Title to read '2009 Monthly Sales' and the resulting chart and data pane should like Figure 2.
Figure 2. Initial Chart
Now just a couple more things to fix on this initial chart:

  • Right-click on the Horizontal Axis and select Horizontal Axis Properties... Set the Interval Property to 1.


  • Set the months to sort naturally (Jan, Feb, Mar) instead of alphabetically. Right-click on the Category Group Calendar_Month and choose Category Group Properties. Click on the Sorting tab and hit the function (fx)key. Enter the following expression: =MONTH(Fields!Calendar_Month.Value & " 1").
  • Set the formatting of the Vertical axis by right-clicking on the Vertical Axis and selecting Vertical Axis Properties... Select the Number tab and identify it as Currency, check the boxes for "Use 1000 seperator" and "Show Value in:" select Millions. Also select "Show symbol after value". Click OK.



Now when you preview your chart, it should like Figure 6 with the months in order and the vertical axis formatted. In this case, I went into the Format property and replaced the $ sign with an M. Got that from looking at how Power View formats numbers in the Millions which I think keeps it simple and easy to understand.
Figure 6. Intial resulting chart.
Creating a Trellis Chart
At this point, you can add a new Chart Area to create the effect of a single column trellis chart. However, its important to note that each chart area you add needs its own Series. So to add a new series, simply drag and drop another column from your Dataset into the Chart Data pane. In our case, this will be the Sales_Quota_Amount column. After you do this, you will see multiple columns and a chart data pane that looks like Figure 7.
Figure 7. Adding a Series to a Column Chart
Now right-click on an empty area in the main Chart and you should see a selection for Add a New Chart Area. Select it and you will see an empty chart area appear underneath your main chart. It's empty because we must assign the second series (Sales_Quota_Amount) to that chart area by right-clicking on the series in the Chart Data pane, going to Series Properties, and change the Chart Area from Default to Area1.

Step 1: Add New Chart Area
Step 2: Go to Series Properties
Step 3: Update Chart Area for that series.
Now add a the Vertical Axis titles back to the top and bottom Chart Areas, and you get a nicely aligned trellis chart (or at least the beginning of one).
Figure 11. Single column Trellis Chart.
Creating a Correlated Bar Graph
Now from this point, you can go straight into another visualization called a Correlation Bar Graph which data visualization expert Stephen Few designed and demonstrates in his book Show Me The Numbers. This involves superimposing two data series as columns to correlate two different scales. However, in our case, we will try to use the same scale since we're talking about Forecast vs. Actual in the Millions of dollars. Not exactly what Steve had in mind, but it gets the point across of how you can construct one of these in SSRS, nonetheless.
We start the process by making one of the series' column widths shorter and turning off the grid lines. To do this, you have to click on the top chart series and look at the Properties pane which should be on the right hand side in BIDS. Locate the Custom Attributes group and expand it to find the PointWidth Property which by default is set to .8. Set it to .5 for this exercise and hit enter. You'll notice the width of the bars in the top chart area will be narrower.
Figure 12. PointWidth property controls column width

Now here's the tricky part. Select the 2nd Chart Area (the one on the bottom named Area1) and locate the Custom Positioning property in the Properties pane of BIDS. Expand it and set the Enabled property to True. Then pick the Default Chart Area and set its Custom Positioning/Enabled property to True also. Your graphs will look exactly like they did before. However, now that you've enabled Custom Positioning on both chart areas, you are able to size and locate the chart areas identically so that they overlap. Set both Chart Areas Custom Positioning  Height to 80 and Top of 20.

Figure 13. ChartAreas property of Overall Chart
Quick Tip: If you get stuck with one Chart Area in front of the other, don't worry. Click on the overall Chart object and locate the ChartAreas property in the Property Pane of BIDS. Hit the ellipse button and you can reorder which Chart Area comes in front of the other one. You will have to do this so that your Sales_Amount series is in front of the Sales_Quota_Amount series before you're finished.

When you've ordered the Chart Areas with the Sales_Amount series in front of the Sales_Quota_Amount series, you will see your Correlation Bar Graph take shape:
Figure 14. Correlated Bar Graph
Now before you think this is pretty cool, there is a catch. With this data set filtered for 'Year 2009', it just so happens that the vertical scale for both Series are identical. However, in the real world, they will likely be off, especially at the start of each month. To demonstrate the issue, change the Calendar Year filter in the dataset from 'Year 2009' to 'Year 2008'. Notice the vertical scale will be off like so.

Figure 15. Correlated Bar Graph for 2008 with incorrect scale
So to correct this issue to be able to "correlate" the series in tandem, you have to set the Maximum of each Chart Area's vertical scale to the greatest number in either series. You can do this with the following SSRS expression entered into the Vertical Axis Maximum property of each Chart Area:

=IIF(Max(Fields!Sales_Amount.Value)>Max(Fields!Sales_Quota_Amount.Value),
Max(Fields!Sales_Amount.Value),
Max(Fields!Sales_Quota_Amount.Value))

Remember that each chart area has a Vertical Axis now. So to get to the Vertical Axis in the rear, use the ChartAreas property of the main chart to reorder the Chart Areas, enter the SSRS Expression, and then switch the chart area order back. If you do it right, you should get a graph like the one in Figure 15.
Figure 16. Correlated Bar Graph for 2008 sales, correct scale.
Now its clear: when comparing 2008 results (Fig. 16) with 2009 (Fig. 14), you'll notice that Contoso didn't meet their 2008 Forecasts, but in 2009, they beat their Forecasts every single month. To make this even clearer and more dynamic, make the title dynamic and add a legend. Add the Calendar Year to the Dataset and then into the Title with an SSRS Expression:
=Fields!Calendar_Year.Value & " Monthly Sales"
Figure 17. Custom Legend Text
Add a legend and dock it to the top of the chart. Change the legend text for each series by right-clicking on the series in the Chart Data pane, selecting Series Properties, selecting the Legend tab and filling in the Custom Legend Text  property.

Once completed, you should have a nice looking graph like the one in Figure 18.



Figure 18. Correlated Bar Graph with Legend and Dynamic title
Creating a Bullet Graph
Even though a Bullet Graph exists elsewhere in SSRS, I want to extend this chart capability to get us thinking outside of the box a little bit. I was excited when I got this far with charts, I think you'll be happy when you get through this next step as well. Besides, we're almost there. Just one more series to overlap and we'll be done.
To add another series, we need to add a calculated measure to our MDX query. The easiest way to do this when you're this far is by opening the Dataset properties and clicking on the Query Designer button. Once you're there, hit the Calculated Member button at the top of the designer:


Then for the Name of the Member, type in 'Budget', and for the Expression, type in ([Measures].[Sales Quota Amount],[Scenario].[Scenario Name].&[2]) as shown below:

Figure 19. Calculated Member Builder for adding Budget Column
Drag and Drop that into the Dataset and accept all the prompts.

Now drag and drop the new Budget column from our Dataset into the Chart Data Pane under our Sales_Quota_Amount Series. Also, add a new Chart Area as before. It should line up automatically with the other two chart areas, but you must still assign the new Budget series to the new Chart Area. So right-click on the Budget series, go to Series Properties, and select the Axes and Chart Area tab and change the Chart Area property to Area2.

Now to get the target of Budget to show up like it would on a Bullet Graph, we need to change the chart type of Area2 from a Column Chart to a Column Range Chart. Right click on the Budget series and select Change Chart Type...

You will get the Chart Type selection dialog where you will scroll down to the Range-type charts and pick the Range Column chart.
Figure 21. Choosing Range Column chart type for the Target
Now after selecting this type of chart, you will notice in the Chart Data Pane, two fields under the series which stand for the high and low of the range. The High will simply be the SUM([Budget]) value, and the low we'll set manually to something a little bit smaller. In this case, click on the arrow next the word "Low" and choose expression. Type in the following SSRS Expression:
=SUM(Fields!Budget.Value -5)
Preview the graph and you should get a Bullet Graph like so:

Figure 22. Bullet Graph
Conclusion
I have to give credit for this entire post back to Stephen Few because I got this whole concept based on his idea of a Correlated Bar Graph. It started with a simple column chart, and all we did was add one or two more chart areas and overlap them with custom positioning, and voila! We get much better column graphs then we've ever thought possible. The extra visualization helps to correlate two related measures in a way that is easy to understand and brings a strong message to the end user.

In my next post, I will extend this capability of charts to make them interactive with the end user. Much like Power View does, and we will filter and highlight our data with a single click of the mouse. Stayed tuned for Part 2.

7 comments:

  1. Amazing article....cheers!

    ReplyDelete
  2. First, thank you for your great article!

    Unfortunately I have a problem. I don't have a finished table as input like this example has, instead the values of my series are calculated
    e.g. sum(Fields!Field1.Value)+sum(Fields!Field2.Value)

    So the max-function doesn't work.

    Is there a possibility to access to the dates which comes from the Chart Data? Because to create the chart, the program first have to calculate the values.

    ReplyDelete
    Replies
    1. Hi Betty. Thank you for reading!

      Might a calculated field in the dataset help? If you open the dataset properties, click on 'Fields', and add a field at the bottom of the set using the same SSRS Expression, you can use this new calculated field for the MAX function I used in the article.

      I'm unclear about your 2nd question regarding the dates in your Chart Data. I believe the answer is yes, if you use custom code in your report to somehow create a new dataset. If this is what you want to accomplish, I think your work will be hard to maintain down the road. With SSRS, it's always best to format and pre-calculate your data in the source before connecting it in your dataset. 

      Sounds like you may be embedding your chart in an application which creates a record set object that you want to use as the source for your report. If so, I advise temporarily creating a database table that will resemble that object with the data formatted as shown at the beginning of my post (or similar). Develop your report off of that table, and then embed it in your app and bind it to the recordset object.

      Sorry if I am misunderstanding the question. Hope that helps. :/

      Delete
    2. Hi Angel. Thanks for your time!

      Yes, the solution to my first question will be to add a new field to my dataset.

      But this is the point - I don't like to - only because RS is not able to display it on a logical way ;-)
      I would prefer a command like

      Chart1!ChartSeries1.Value - to use it for the max-function.

      But I think, I expect to much from SSRS ;-)

      Thank you for your help!

      Delete
    3. Ah yes. Now I gotcha. I definitely agree. One place this would be very beneficial for me is with dial gauges. When showing more than 1 (because how can you show just one dial gauge) and getting all the scales to match. I wish there was a Gauge1!Scale.MaximumValue, but alas, I expect too much as well. :)

      Appreciate the comment very much Betty. Good luck in your report development and thanks for leaving a comment.

      Delete