Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
#BI 39
BRH 39
#MSBI 29
SQL Server 28
#SSRS 22
#SQL Server 21
Reporting 19

Archive · View All
April 2010 8
May 2010 7
January 2013 6
November 2012 5
March 2012 5
July 2012 4
January 2012 4
October 2011 4
June 2010 4
February 2013 3

Some Random Thoughts

100% Visualization in SSRS

Mar 3 2013 7:51PM by Jason Thomas   

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.


Jason Thomas
19 · 9% · 2997


Your Comment

Sign Up or Login to post a comment.

"100% Visualization in SSRS" rated 5 out of 5 by 1 readers
100% Visualization in SSRS , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]