100% Visualization in SSRS

Bandlines in SSRS
February 19, 2013
Introduction to Data Explorer Preview for Excel
March 4, 2013
Show all

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
SELECT     ‘Accessories’ AS Category, 49 AS Pc
SELECT     ‘Textile’ AS Category, 24 AS Pc
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.


  1. giovi2012 says:

    Amazing Jason! this is an interesting visualization – neat research work

  2. Raghu says:

    Jason bhai, you are the champion…Jai ho Jason bhai

  3. Ron Smith says:

    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

  4. Ola Ta says:

    Cooool! great job, many thanks for sharing this.

  5. @Jason,

    More on "Squaring The Pie"

    EagerEyes – Robert Koasra

    Juice Analytics – Chris Gemignani

  6. Claudelin says:

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

  7. Kraaitje says:

    Really cool solution. I impressed some guys with this “new” graph 😉 Thanks Jason!

    I hope you will continue to write about hidden SSRS features, not just about Power BI 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *