100% Visualization in SSRS

Share this...

Twitter

Linkedin

Facebook

Pinterest

Reddit

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.

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

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

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.

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))  )

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.

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

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

8) Preview it to see the 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.