Home > Reporting Services > SQL R2 : Sparklines and Data Bars

SQL R2 : Sparklines and Data Bars


Well , after installing the November CTP I find that there’s a whole host of new things to discuss. Lets start with Sparklines and Data Bars. Now many of you will remember that you could create sparklines in RS 2008 , but there was a trick. You had to take a line chart, delete the axes , legend and any title and place it into an empty cell of the table or matrix object. The chart will then render for every detail row , and the data will be filtered at group level.
 
Anyway , life is much easier now with RS 2008 R2. In the November CTP , there are a few new options on the toolbox for us. In addition to the Map, we now have the Data Bar, Sparkline and Indicator.
 
 
 
So in order to try out these new controls, I started out by creating a standard matrix chart off the Adventure Works database , showing Sales by Product. In the dataset I have also included the month field – we can use this to draw a sparkline indicating the sales for a particular product by month, thus indicating the spread of the total sales. This is why sparklines are so useful, they deliver more insight into the figures that we see. So the matrix itself started out looking like this :
 
 
So the next step is to add an adjacent column inside the column group. Of course , the tablix allows us to do exactly this. Right click on the cell where the sales amount is displayed ( in design mode ) , and select Insert Column – Inside Group – Right. This will create another column for us. The next step is to select the Sparkline object from the toolbox and drag it into the empty cell. The moment you do this , a window opens asking you to select the type of sparkline to be used.
 
Now you’ll see that these are very similar to the charts we have, and I suspect that they are actually derived from the Dundas charts. In fact you’ll see later on that once you create a sparkline you have the option to convert it into a chart. But for now, lets get the sparkline finished. Select the simple line chart and click OK.
 

New Data Pane

Now you need to double click the sparkline object itself to bring up the Chart Data dialog box. This is actually one of the changes in R2, when working with normal chart objects the data dialog box will also look like the one below.
 
 
 
It’s actually easy to decipher – on the first pane you drag the field that will provide the actual value ( in this case Sales Amount ). The second pane called Category Groups will define the Y-Axis Category for our "line chart" sparkline, in this case the Month field. You even have the option of a series grouping on the 3rd pane. I’m not sure that this would be a good idea on the line chart , but you would need this for a 100% stacked bar chart, for example. And with that , you can actually run the report – very straightforward.
 
 
Now theres a few things I’ve added up there. The second matrix simple illustrates a different type of sparkline. I’ve changed the "Fill" option to a "Top – Down" gradient to give it a nice effect. The third matrix uses the new "Data Bar" object. Now this works in a similar manner to the spark line , you even get the same data pane. The exception is that although you CAN use the month field as a Category , I haven’t here cos the aim of a Data Bar is to show us the larger and smaller figures at a glance. In fact this is very similar to what we have in Excel 2007 , except I haven’t figured out how to superimpose the actual figure on top of the data bar.
 

Indicators

And what of the new Indicator object ? Well this is where it gets exciting for people who create scorecards. You can drag the indicator object into the cell as well, and set it to use the SalesAmount field , and the result will be the following :
  
It will take each value as a percentage of the overall total , and will display an indicator based on the banding. To define the actual banding , double click the indicator object for the Data Pane dialog to come up , then click the Properties button ( the first one ) , and then select Values and States. You will see the following screen
 
 
Now this really excites me as it means we can create proper scorecards in Reporting Services !!. In my next post I will talk about shared data sets…..
 
 
 
Advertisements
Categories: Reporting Services
  1. C
    January 25, 2010 at 6:33 am

    Very exciting new features with R2. Do you know when these will be available for those who use 2008 SP1 with BI and Reporting Services?

  2. Thavash
    January 26, 2010 at 3:22 pm

    Well as far as I know these features are for R2 only and if you upgrade your reports you should be able to add these to existing items. Only Microsoft knows if there will be a service pack allowing you to do this in 2008 , but I doubt it.

  3. Peter
    January 29, 2010 at 10:00 pm

    Yes, great new features in R2. I have been trying design a report using Report Builder 3 where measures are y axis and month x axis (see below) but little success. Do you have any suggestions.ThanksP Sidi x-axis months jan feb mary-axism1m2m3

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: