Archive for December, 2009

SQL R2 : Sparklines and Data Bars

December 17, 2009 3 comments

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.


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…..
Categories: Reporting Services

Sql 2008 R2 : November CTP now available

December 17, 2009 Leave a comment

The latest CTP ( November ) is now available. The download link is here. There is also another page called the MS Sql R2 Nov CTP Preview Pack. It contains additional tools like Report Builder 3 and Power Pivot. You can get it here.

So whats new ? Well there’s plenty to look forward to. In terms of Reporting Services :

  1. Sparklines.
  2. Data Bars.
  3. Indicators
  4. Naming Excel Tabs !! .
  5. New web service endpoint.

There’s a few other things as well , such as something called Shared Datasets and Cached Refresh Plans and new features in Sharepoint Integrated Mode. Of course I’m installing it as you read this , so I’ll post articles in depth on any of the new features I find exciting soon !

Categories: SQL Server