Pages

Tuesday, February 26, 2013

100% Visualization in SSRS

Recently, Bill Jelen aka Mr Excel came up with a podcast that showcased a visualization that he termed as the “100% Visualization”. I have never seen this visualization before and I am not sure whether Stephen Few would approve of it. But I found the concept interesting and decided to exercise my grey cells by creating this in SSRS. Read on for the solution.

100% visualization in SSRS

1) Create a new report with a simple dataset having Category name and percentage value

SELECT     'Bikes' AS Category, 82 AS Pc
UNION ALL
SELECT     'Accessories' AS Category, 49 AS Pc
UNION ALL
SELECT     'Textile' AS Category, 24 AS Pc
UNION ALL
SELECT     'Furniture' AS Category, 99 AS Pc

Dataset creation

2) Make a simple tablix with 10 columns and 10 rows. Then fill the cell values from 1 to 100 as shown below

Making the 10 * 10 matrix

It is good to make all the cells of the same size and squares (for eg, size = 0.6,0.6)

3) Now make the border color as white, border style as solid, border width as 4pt and FontSize as 2pt.

formatting the 10 * 10 matrix

4) Add the custom code below

Public Shared Function ColorDWB(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal Neutral As Decimal, ByVal ColStr As String) As String

Dim ColVar1 As Integer
Dim ColVar2 As Integer
Dim ColVar3 As Integer

'Split the #RGB color to R, G, and B components
ColVar1=Convert.ToInt32(left(right(ColStr, 6),2),16)
ColVar2=Convert.ToInt32(left(right(ColStr, 4),2),16)
ColVar3=Convert.ToInt32(right(ColStr, 2),16)

'Find Largest Range
Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)

'Find appropriate color shade
Dim Shd As Decimal = 255
   
Dim iColor1 As Integer
Dim iColor2 As Integer
Dim iColor3 As Integer
Dim strColor As String

'Reduce a shade for each of the R,G,B components
iColor1 = ColVar1 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar1) / decPosRange)))
iColor2 = ColVar2 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar2) / decPosRange)))
iColor3 = ColVar3 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar3) / decPosRange)))

'Return the new color
strColor = "#" & iColor1.ToString("X2")  & iColor2.ToString("X2") & iColor3.ToString("X2")   

Return strColor
End Function


Private colorPalette As String() = { "#C85200", "#FF800E", "#5F9ED1", "#2CA02C", "#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}

Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String
   If mapping.ContainsKey(groupingValue) Then
      Return mapping(groupingValue)
   End If
   Dim c As String = colorPalette(count Mod colorPalette.Length)
   count = count + 1
   mapping.Add(groupingValue, c)
   Return c
End Function

The GetColor() is used to select new colors for each category type and the ColorDWB() is used to get a lighter shade of the selected color (you might recognize the ColorDWB function from my post on Custom Code for Color Gradation in SSRS).

5) Add the expression below for the Color and BackgroundColor property for all the cells.

=iif(Me.Value <= Fields!Pc.Value, Code.GetColor(Fields!Category.Value), Code.ColorDWB(2, 10, 1, Code.GetColor(Fields!Category.Value))  )

Adding color expressions

6) Add a textbox on the top with the Category value, and a textbox below with the Pc field value and place all three objects inside a rectangle to keep them together. You can also format it by adding a background color of Green and a border of White, if needed.

Adding textboxes

7) Now add a matrix with Category field on column group. Then, add the rectangle with the three objects inside the value field.

Adding the parent matrix

Now you can delete the row and the column for the matrix so that it looks like shown below.

row and columns deleted

8) Preview it to see the result.

End result

Isn’t that pretty? And if there are more charts than what can be fitted in the horizontal way, you might want to check this post on how to repeat it horizontally as well as vertically. As always, you can download the SQL 2012 rdl file from here.

9 comments:

  1. Amazing Jason! this is an interesting visualization - neat research work

    ReplyDelete
  2. Jason bhai, you are the champion...Jai ho Jason bhai

    ReplyDelete
  3. This is a sweet visualization. Another way to do it is to have the sql return color values for each cell. Then you just tell SSRS to use those color values for the background of a matrix grouped by row and col ids:

    -- rows
    declare @row table (row_id int identity(0,10))
    while (select count(*) from @row)<10 begin insert @row default values end
    -- columns
    declare @col table (col_id int identity(1,1))
    while (select count(*) from @col)<10 begin insert @col default values end
    -- matrix
    select p.id,p.category,p.pc,r.row_id,c.col_id,r.row_id+c.col_id cell_id,isnull(c1.clr,c2.clr) clr
    from (
    select 1 id,'Bikes' category,82 pc union all
    select 2,'Accessories',49 union all
    select 3,'Textile',24 union all
    select 4,'Furniture',99
    ) p
    cross join @row r
    cross join @col c
    left join (
    select 0 id,'#cf64a2' clr union all
    select 1,'#4f81bd' union all
    select 2,'#4faebd'
    ) c1
    on p.id%3=c1.id
    and r.row_id+c.col_id<=p.pc
    left join (
    select 0 id,'#e6cfdc' clr union all
    select 1,'#cfd9e6' union all
    select 2,'#cfe2e6'
    ) c2
    on p.id%3 = c2.id
    and r.row_id+c.col_id>p.pc
    order by
    id,cell_id

    ReplyDelete
  4. Cooool! great job, many thanks for sharing this.

    ReplyDelete
  5. @Jason,

    More on "Squaring The Pie"

    EagerEyes - Robert Koasra
    '-------------------------
    http://eagereyes.org/blog/2008/engaging-readers-with-square-pie-waffle-charts

    Juice Analytics - Chris Gemignani
    '--------------------------------
    http://www.juiceanalytics.com/writing/solving-the-pie/



    ReplyDelete
  6. Nice, I will try this and see what my customer think. Thank you!

    ReplyDelete