What happened to Report Builder 2.0 ?

When SQL Server 2008 RC0 was released , you will have noticed that the Report Designer tool was not part of the installation. It was renamed Report Builder 2.0, and you could get it, for a brief period, from the Sql Server Feature Pack page.

 

Not any more though. The link to download it has been removed and many people have asked me about this , especially since I spoke about this during my Tech-Ed session. Well , MS has pulled the link becuase officially, Report Builder 2.0 will ship as part of the updated feature pack to be released later this year. An RC1 release of Report Builder 2.0 will be made available soon, tentatively scheduled for the end of August. For more information , check out the SQL Server Reporting Services Team Blog.

Data Visualization in RS 2008 : Part 1 – The Gauge data region

A picture is worth a thousand words. I realized this the other day when I gazed upon an image of the new Alfa Romeo 8C Competizione, ( although I won’t mention which words entered my head at that point ), but anyway, with this in mind, it brings us to Reporting Services 2008, and the new data visualization elements available to us.

Knowing full well that dashboards and scorecards are becoming more and more popular, the team have added Dundas gauges to the mix, and I suspect that this object will be used extensively. ( If you consider the cost of competing reporting tools with features like these, a few more choice words enter the mind ). But how easy is it to use ?

The short answer is VERY. We have 2 types of gauges, radial and linear. Each gauge has a number of sub-areas, as you’ll no doubt notice from the diagram above, but the ones to be interested in initially are the scale and the pointer.

The scale defines what the gauge measures, and by default it is set to a minimum of 0 and a maximum of 100. If you’re using the gauge to display KPI’s , you might want to reset the scale to a range of 0-3 or 0-5.

The pointer is the next thing to focus on. Not because of the possibility changing the physical pointer to a wide range of different types ( and you can ) , but because this is where you link your data to the gauge itself. So lets assume we had the following query in the dataset, running against the Adventure Works database.

SELECT ST.Name, Sum(SOH.TotalDue) AS Sales FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesTerritory ST ON SOH.TerritoryID = ST.TerritoryID GROUP BY ST.Name

Create a simple matrix of sales per territory ( with Territory as the Row Group, SalesAmount as the data field and no Column group ). It should look like the one below. Remember that although you’re starting off with a matrix, it’s actually a tablix underneath.

1) Right-Click anywhere on the report and select Insert – Gauge.
2) Select the very first Radial gauge from the list. Leave it on the canvas.

3) Right -Click inside the header of the second column and Select Insert Column – Inside Group Right.
4) Drag the gauge object into the new cell. You may have to increase the row height in order to see the gauge clearly.

What needs to be done now, is to calculate what percentage of the overall sales constitutes the sales for a particular territory. To do this we divide the sales for a territory ( in other words the sales for every row ) by the total sales amount. This is achieved by using the following expression :

=(Sum(Fields!SalesAmount.Value)/Sum(Fields!SalesAmount.Value,"DataSet1"))*100

A simple expression. Select the pointer of the gauge, Right -Click and select Pointer Properties. On the Value field, you can select Fx and input the above expression. Now when you run the report, you will find that the gauge indicates this percentage quite nicely, for example the Southwest Territory was responsible for about 22% of all sales.

This example illustrates how to use the gauge within a tablix, but remember, you don’t have to use it like this. You can use it as a standalone object , but then it has to bind to a single data value. Also, remember that if the data value is greater than the maximum value of the scale, the gauge will read as the maximum possible value of the scale.