Tim

Tim Kent's Blog

SSRS 2008 – Icon Charts

In my last post I had a bit of a moan about the lack of new data visualisation features in PerformancePoint Services but to be fair MSFT have really turned up the dial on the data visualisation functionality with the release of Excel 2007 and SSRS 2008.  SQL 2008 R2 sees further advances with features such as Sparklines, Data Bars and indicators being far easier to create in SSRS.  There’s still a few things missing from the stack though when compared against some of the pureplay visualisation vendors such as Tableau and Spotfire.

Icon Charts (I’m not actually sure that’s their real name!) are another form of Tufte’s “small multiple” that combines the concepts of lattice/trellis charts and heatmaps.  This type of visualisation utilises the user’s perception of colour (hue) and form (size) to allow analysis of multiple categories with multiple quantitative measures.

I was hoping that it might be possible to do something with the new indicators in R2 and though you can use custom images there’s no simple way to hook them up to the data in terms of size and colour.

In this solution (workaround/hack?) I’m using the matrix object again but rather than using an inline chart I’m simply using a square symbol (letter n) from the wingdings font and then setting the colour (with a sequential palette) and size according to the data.  My example uses hard-coded values in a switch statement in the expression but you could be cleverer than this and make the values percentages of the maximum in your dataset for example.

image

Add a couple of legends so that the viewer can clearly see what they are looking at.  It’s easy to pick out that SouthWest in Q3 had good revenue but low profit.  Is there a Problem? Add a drillthrough to detail report to allow the user to find out.

image

As always the sample report can be downloaded from here: IconCharts.rdl (81.36 kb)

Alternatively, e-mail devteam(at)adatis(dot)co(dot)uk for a copy.

Win - Loss Graphs in Reporting Services 2008

As I mentioned previously, SSRS 2008 allows us to use some nice visualisation techniques in our reports.  Win\Loss graphs are used to show (wait for it......) the trend of wins and losses over time.  The usual example is the performance of a sports team during a season, but they are also useful for showing trends over time such as the performance of a KPI.  Here's another quick how-to on building this type of chart in SSRS 2008.

So first we need a dataset with three fields: Series, Time and Value.  The first two are fairly self-explanatory, for the third you need to transform your value so it appears as either a 1, -1 or 0 (win, lose, no result/tie).  The AdventureWorks built-in cube KPI's work well here so I'm going to shortcut slightly and use these.  If you're going to use a relational source you may need to do something a little cleverer.  For example:

SELECT
    [Month]
    ,[Country]
    ,CASE 
        WHEN [SalesAmount] > 10000
        THEN 1
        WHEN [SalesAmount] < 10000
        THEN -1
        WHEN [SalesAmount] IS NULL
        THEN 0
    END AS Winlose
FROM 
    [AdventureWorksDW].[dbo].[FactInternetSales]

We'll be using AdventureWorks (SSAS) "Internet Revenue" KPI.  Don't forget that when using an AS data source in SSRS you basically have to trick it into thinking it's relational and make sure you have your measures on Columns (as Chris Webb discusses here).  Here's our very simple MDX statement:

SELECT 
    KPITrend("Internet Revenue")
ON COLUMNS, 
    [Date].[Calendar].[Month].members 
    * 
    [Customer].[Customer Geography].[Country].members
ON ROWS FROM 
    [Adventure Works] 

Now lets add a chart - pick the standard column chart and format as follows:

  1. Right click and delete the chart title and legend;
  2. Right click on both the axis titles uncheck Show Axis Title
  3. Right click on the Y (vertical) axis and select Axis Properties.  Under Axis Options set Minimum to -1, Maximum to 1 and Cross At property to 0 (this makes sure our baseline runs between the "wins" and "losses").  Click OK;
  4. Right click again on the Y axis and uncheck Show Axis;
  5. Right click on the X axis and select Axis Properties.  Under Labels  check Hide Axis Labels and do the same for Major and Minor Tick Marks. Under Line set the width to 0.5 and the colour to light grey or similar.  Click OK.

You should now have something like this:

image

Now lets add some data:

  1. Drag your Time field from the Report Data pane into the category area;
  2. Drag your Value  field into the Data Field area;
  3. Right click on your series and select Series Properties.  Under Fill set the colour suitably.  In our example we're going to show wins as black and losses as red using a switch statement in an expression:
  4. =Switch
    (
        Fields!Internet_Revenue_Trend.Value = 1, "Black",
        Fields!Internet_Revenue_Trend.Value = -1, "Red"
    )

If you preview the chart now it should look something like this:

image

To finish off we need to add the chart inline to a table:

  1. Add a table and delete the third column;
  2. Set the DataSetName property to the same as your chart;
  3. Right click on the second row and add a group (using the adjacent above option).  Select your Series value (in our case: Country) from the drop down;
  4. Delete the bottom row;
  5. Drag your Series field into the First column, second cell;
  6. Drag your chart into the second column, second cell.

Finally add a bit of formatting to your table and you're done!

image

As usual the sample report can be downloaded here: WinLose.rdl (24.08 kb)

This also works in SSRS 2005 - though doesn't render quite a cleanly.

How to: Bullet Charts in Reporting Services 2008

Unlike SSRS 2005 where creating bullet graph takes a bit of jiggery-pokery, Reporting Services 2008 almost has them built-in (thanks the the acquisition of Dundas technology).  I say almost as it's actually using a linear gauge object which in the wrong hands allows you to put the likes of a thermometer on your reports to show just how "hot" your business is!  Luckily, there is a built-in bullet graph template for the gauge but it still requires a bit of customisation to make it look like a bullet graph as per Stephen Few's specifications.  However it's almost all formatting/setup.  Here follows a quick(-ish) how-to.

In most cases you'll be using this type of chart inline in a table, however it's easier to develop the graph object outside of the table then drop it in once you've got the formatting right.  To start with create yourself a datasource and build a dataset that has the following fields and one row for each bullet graph you want to display:

  • Category Name
  • Actual Value
  • Target Value
  • Bad Zone Max
  • Satisfactory Zone Max
  • Good Zone max
  • Optionally you may need a bad zone min if you have negative values

Next drop a gauge object onto your report and select the bullet graph template. 

image

You'll notice that the default is lots of jazzy shading and spangly borders.  We particularly need to get rid of these if we are using the graph inline as they will make the graph very hard to read when scaled down (and of course add no value in terms of visualising the key information anyway).  We'll also want to remove the scale if we are using inline - we can add it back into our column header later.

Right click on the graph and select gauge properties.  Select the Back fill section and set Gradient to solid and the colour to no colour.  Then select the Frame section and set the style to none.  Close the properties pane. Looking better already:

image

Next lets remove the scale - right click on the graph again, this time selecting Gauge and then Scale Properties.  Select the Labels section then check the Hide Scale Labels checkbox.  Then select the Major Checkmarks section and check the Hide Major checkmarks checkbox.  At this point also set the start and end margins to 1 in the Layout section and set the Maximum value of the scale to your good zone max field.  Close the properties panel again.  

Now lets deal with each component of the graph.  You'll notice that the orange bar is actually made up of two objects known as linear pointers.   Lets tidy them up and assign our Actual and Target values to those pointers.  First make sure you've set the relevant datasetname property for the graph.  Then right click on your graph again, gauge sub-menu and Pointer(LinearPointer1) properties.   Set the value to be your actual field from your dataset.  The go to the Pointer Fill section, set the fill style to solid and pick a suitable colour for your "bullet".  Set the Pointer width to 30 and finally select the shadow section and set the shadow offset to 0.  Do the same for your target pointer (LinearPointer2) but this time setting a width of 8 and an length of 80.

image

Now set the relevant properties for each of the zones.  This time select Range(LinearRange1) properties from your Gauge sub-menu.  Set the Start Range to 0 and the End range to your Bad Zone Max field.  Whilst you have the properties pane open, get rid of the range borders by setting the border style to None and the start and end widths to 60.  Repeat for range two but this time setting the Start Range to Bad Zone Max and the End Range to your Satisfactory Zone Max. The same for zone three but this time with Satisfactory Zone Max and Good Zone Max.

Now add a table to your report, set the datasetname to the same dataset as your graph.  Delete the third column and drop your Category field into the second row, first column and then add a grouping on the Category Field (right click on second row, group properties, add).  Then drop your chart into the second column, second cell.  You should have something that looks like this in design view:

image

Almost there.  One of the quirks of any inline chart is that it overrides the border of the cell it's placed in so you need to set the border for the GaugePanel to the same as your cell borders.  Finally lets add a scale to the header of column two.  Copy your chart from the cell and then paste it the header.  Remove all the components except the scale from the gauge panel by carefully highlighting and deleting.  Right click the Gauge in your header and select scale properties from the sub-menu.  Uncheck the Hide Scale Labels checkbox and the Hide Major checkmarks checkbox.  For the max value of the scale you need to make sure you use the max grouping for you good zone max rather than the default sum (=Max(Fields!Zone3.Value)).  In the layout section, set the position in gauge to 90, in the labels section set the placement to inside and distance from scale to 30.  Set the major tick mark placement to inside.  Phew!

Tidy up your fonts and general layout and you're good to go:

image

That's about it - if all that seems like a lot of work download the sample report and use that instead :)

HowTo2008Bullets.rdl (41.22 kb)

HowTo: Bullet Charts in Reporting Services 2005

In my last post, I showed a dashboard in SSRS 2005 that included sparklines, inline bar charts and bullet charts.  As I mentioned there is good MSDN article on how to do inline charts but the Bullet chart took a little more thought.

image

As one of my colleagues has pointed out, these aren't bullet charts in the truest sense that Stephen Few designed them as they don't show the "bullet's path" running through them, however I think they serve the same purpose albeit in a simplistic way

The charts are made using 100% stacked bars with three data values for each "zone" of your chart:

  • The section below your marker;
  • The marker itself (which of course only shows if it falls in that zone); and
  • The section above your marker.

So in the Speciality Bike Shop row in the example above, the three values that add up to the first zone of 25 are 11, 1 and 13 with the colour set as Dark Grey, Black and Dark Grey respectively.  Whereas as Value Added Reseller is made up of three zones with values 0, 0 and 25

Likewise, the 2nd zone for Speciality Bike Shop is made up of 0, 0 and 64 whereas VAR is 42, 1 and 22 this time with light grey, black, light grey

So how do we come with the numbers? It's just a case of calculating the size of the zones as a percentage of the whole and deciding whether the marker should show.  Lets follow the MDX query through to explain

First lets define our key measures: the zone limits, and the actual value for our marker.  I'm using constants for the zone sizes but you could easily have this coming from your data:

WITH 
//replace this constant with your Zone 1 measure    
MEMBER [Measures].[Zone1] AS
     300000

//replace this constant with your Upper Zone 2 measure
MEMBER [Measures].[Zone2] AS
     1067000

//replace this constant with your Upper Zone 3 measure
MEMBER [Measures].[Zone3] AS
     1200000
    
//Actual measure
MEMBER [Measures].[Actual] AS
    [Measures].[Reseller Sales Amount]

Next we need to calculate which zone the marker will fall in - we set a boolean for each which is also used as the marker for the chart.

//Are we in the zone 1
MEMBER [Measures].[ShowZone1Marker] AS
    IIF(
        [Measures].[Actual] < [Measures].[Zone1],
        1,
        0
    )
//Are we in Zone 2
MEMBER [Measures].[ShowZone2Marker] AS
    IIF(
        ([Measures].[Actual] >= [Measures].[Zone1]
        AND
        [Measures].[Actual] < [Measures].[Zone2]),
        1,
        0
    )

//Are we in Zone 3
MEMBER [Measures].[ShowZone3Marker] AS
    IIF(
        [Measures].[Actual] >= [Measures].[Zone2],
        1,
        0
    )

Then we need to calculate the size of the lower and upper parts of each zone, lower first: 

MEMBER [Measures].[Zone1a] AS
    (([Measures].[Actual]) * [Measures].[ShowZone1Marker]
    /
    [Measures].[Zone3]
    * 
    100)
    - 
    ([Measures].[ShowZone1Marker] * [Measures].[ShowZone1Marker])

the logic is:

The actual value subtract the previous zone limit (not required in zone 1),  multiplied by the boolean value of whether or not the marker falls in this zone as a percentage of the whole chart.  The bottom line removes one from the value to allow for the marker.

The upper part of zone 1:

MEMBER [Measures].[Zone1b] AS
    ([Measures].[Zone1] - ([Measures].[Actual] * [Measures].[ShowZone1Marker]))
    /
    [Measures].[Zone3]
    * 
    100

this time the logic is:

the zone size subtract the actual value multiplied by the boolean value of whether or not the marker falls in this zone as a percentage of the whole chart.

Repeat the same for subsequent zones and then bring it all together in a select statement on your cube

Just in case I've explained all this as badly as I think I have, you can download the sample project and have a look yourselves: BulletCharts.zip (7.42 kb)

And the scale - errr OK in case you hadn't already guessed, it's an image on the column header - hey what more do you want?the moon on a stick? ;) You can size the chart to fit the scale or create your own image.  The image is also included in the sample project.

All criticism/suggestion for improvement welcome.

Bullet Charts and Sparklines in Reporting Services 2005

Here at Adatis we're big fans of Reporting Services.  Yep, it has it's quirks and frustrations (don't talk to me about excel export!) but doesn't deserve all the criticism it gets in our opinion.  You can create some great looking reports with not a lot of extra effort over the defaults (though how many times have you seen a "slate" style report called "Report1" go into production!)

We're also fans of what some might call the more "trendy" visualisation techniques such as sparklines and bullet charts and with Microsoft acquiring Dundas technology for use in SQL 2008, SSRS has some much improved functionality in this area.  I'll be taking a look at some of these new features in a future post.

Whilst SSRS 2005 does have it's limitations, with a little effort you can still create some very effective information visualisations.  The dashboard below is built using completely standard functionality

image

Adding sparklines and in-table bar charts is very simple (and pretty clever IMO).  This MSDN Article covers how to do it in detail but in essence you simply add a line or bar chart into a cell in your data table.

Whilst the bullet chart (in the Sales Channel Performance region of the dashboard) may be simplistic compared to those available in SSRS 2008 or MicroCharts from XLCubed (from whom I nicked the styling for our dashboard) it still provides a very clear visualisation of the data.  This required a little more trickery in the underlying query but is also just a chart in a table cell - this time a 100% stacked bar.  I've explained how this works here

PPS Monitoring - Missing Parameters using a Reporting Services report in SharePoint integrated mode

If you're running Monitoring with SP1 applied and working with a Reporting Services report in your dashboard that comes from a report server in SharePoint integrated mode, you may experience an issue where your parameters don't appear in the Report Parameters section.

image

There's a hotfix available for this issue upon request from MS support:

http://support.microsoft.com/kb/956553/en-us

Oooh - See the pretty report ;)

Users love pretty things! Especially upper management who have minions to actually make sense of the numbers behind the reports for them ;)  Out of the box Reporting Services reports look anything but pretty! Here's few tips to make your bosses happy :)

  • Use a background image as a border for charts. The image behind the chart below is a simple png file of only 8kb in size.  To make this work you need to use a rectangle object with the background image set to your image and the chart dropped in the rectangle

  • Use custom palettes for your chart series.  To do this set up a new table with two columns, one that relates to the key's in your data source and one with the hex codes of the colours you want to use.  Then in your recordset join the id to your data.  Once you are returning the hex value for each row to your RS dataset you can use this to define the series colour:

1) Right click on chart -> Properties

2) Select the data tab then edit the value

3) Select the appearance tab then the series style button

4) Select the fill tab then use an expression to

5) Set the color property to your dataset field

6) Click OK, OK, OK, OK (phew!)

I've uploaded a sample project to show how this is done. 

  • Use black borders and lines sparingly (silver is always a good one).
  • Put RS in Sharepoint Integration mode - the toolbars and filters are much nicer and auto-hide (sort of!).  Make sure you consider the limitations of this mode first though.
  • Avoid using 3d perspectives in your charts - this is the easiest way to make your charts fugly (IMO)!

ProClarity and Reporting Services reports in a PPS Monitoring Dashboard

So I was going to do a post on displaying a reporting services report in a PPSM Dashboard but then realised then that the PPS team had already done that:

http://blogs.msdn.com/performancepoint/archive/2007/07/20/how-do-i-integrate-ssrs-reports-with-dashboard-filters.aspx

And then I was halfway through a post on how a Proclarity view works in PPSM when the PPS Team posted one on just that!

http://blogs.msdn.com/performancepoint/archive/2008/02/22/using-pas-views-in-performancepoint-monitoring-dashboards.aspx

Hey guys - leave some for us!! ;) Just so you haven't completely wasted your time reading this, here's a couple of additional points:

  • Multi-select filters don't work with multi-value parameters in RS. It only passes the first item selected through to RS.
  • There is no concept of cascading filters in PPSM v1 - The dev team know this is much requested feature. So far we've got around this by using a multi-select tree filter and the promise of a fix in the next version!!
  • Your don't need to define anything particularly in your Proclarity to be able to connect a PPSM filter.  Just connect a filter that contains valid members from any dimension in your cube.  I know Alyson has mentioned this in her post but quite a neat feature I reckon
  • If you have a slicer on the background - this will still display within your PAS view as shown below:

Proclarity Slicer

  • Remember to consider RS/Proclarity security and kerberos related implications depending on your PPSM security config and environment (Another post coming here i think!)

Using these two report types lets you get around a few of the "still to come" features of the built-in analytic chart and grids such as:

  • Customise chart colors - (PC/RS)
  • Use a secondary Y axis on a chart (PC)
  • Use SQL stored procedures as a datasource (RS)

Lastly Nick B has just done a great post on how parameters work with a web page report. This opens up pretty much endless possibilities...

Reporting Services features not supported in Sharepoint Integration Mode

The latest version of SQL Books Online details the features of Reporting Services that are available in Sharepoint Integration mode and, more importantly, the features that aren't!!  Be careful if you plan to migrate as there are some surprising omissions:

  • URL addressing is different in SharePoint integrated mode. SharePoint URLs are used to reference reports, report models, shared data sources, and resources. The report server folder hierarchy is not used. (This basically means no querystring parameters!!!)
  • Reporting Services custom security extensions cannot be deployed or used on the report server. The report server includes a special-purpose security extension that is used whenever you configure a report server to run in SharePoint integrated mode. This security extension is an internal component, and it is required for integrated operations.
  • Report Manager or Management Studio cannot be used to manage a report server instance that is configured for SharePoint integration.
  • Data-driven subscriptions are not available. This applies to all editions.
  • The rs.exe command line utility is not supported. The utility does not support the SOAP endpoint used for programmatic access to a report server that runs in SharePoint integrated mode.
  • Linked reports are not supported.
  • My Reports is not supported.
  • Job management features that allow you to stop a long-running report process are not supported.
  • Batching methods will not be supported.
  • I haven't tested it yet but I have a feeling this might mean that the PerformancePoint Planning operational reports may not work in this mode

    The online (!) Books Online article is here