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.
Amazing Jason! this is an interesting visualization – neat research work
Jason bhai, you are the champion…Jai ho Jason bhai
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
Cooool! great job, many thanks for sharing this.
@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/
Thanks, good links!
Superb Jason!!!
Nice, I will try this and see what my customer think. Thank you!
Great Post
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 🙂