Reporting Services 2.0 Tip - Pimp up your report with data bars

If you are stuck into Report Builder 2.0 world because you are still using SQL Server 2008 and not the shiny new SQL Server 2008 R2. Check out the following tip.
--> Report Builder 3.0 works only in conjunction with SQL Server 2008 R2

My problem

image
Bad: Boring data with numbers that are hard to compare - Sorting is on another column not shown here

 

The SQL query to generate that, looks something like this

SELECT AuctionID, COUNT(*) AS HowManyTimes
FROM [dbo].[Log4Net]
GROUP BY AuctionID              

 

 

Data Bars would be a nice feature to pimp up my report but they are only available in Report Builder v3.
You cant even copy the generated XML for Data Bars (from the RDL file) to your Version2 Report.

image
Figure: New feature in Report Builder v3 - "Data Bars"

 

#1
One way to pimp up your report would be by using a Rank() function and show a nice graph
Recommended! But that doesn't work so easily without creating sub queries in my case (count(*) )

What I want is an easier way

#2
By repeating a special character as often as your numeric value is, you get a "nice data bar" graph as well

Use the yellow

SELECT AuctionID, COUNT(*) AS HowManyTimes, REPLICATE('-',COUNT(*)) as HowManyTimesDataBar 
FROM [dbo].[Log4Net]
GROUP BY AuctionID

and get this

image
Good: The dashes give us a nice overview what the biggest number is!  I sort on another column, not shown here 

 

What's next

  1. Play around with different characters in your "data bar"
    http://www.asciitable.com/
  2. Use different fonts and styling to get a better effect of a bar
  3. Check our Rules to better SQL Reporting Services

Latest Posts

Popular Posts