One of my first blogs here was based on the Rich Text functionality in SSRS 2008. However I found very little use of that in my projects and did not explore that area much. However a recent question in the forums made me rethink on the way I thought about the rich text functionality.
The question was whether we could create tag clouds in SSRS. A tag cloud (word cloud, or weighted list in visual design) is a visual representation for text data, typically used to depict keyword metadata (tags) on websites, or to visualize free form text. 'Tags' are usually single words, and the importance of each tag is shown with font size or colour. This format is useful for quickly perceiving the most prominent terms and for locating a term alphabetically to determine its relative prominence. I have attached an image of my blog’s tag cloud below:-
Follow the steps below to reproduce the solution:-
1) Create a new report and use the query below for the dataset so that we can get the keywords as well as the count
SELECT 'SSRS' AS Keyword, 36 AS Cnt
SELECT 'SSAS' AS Keyword, 26 AS Cnt
SELECT 'MDX' AS Keyword, 20 AS Cnt
SELECT 'Interview Questions' AS Keyword, 18 AS Cnt
SELECT 'Personal' AS Keyword, 17 AS Cnt
SELECT 'Activities' AS Keyword, 16 AS Cnt
SELECT 'SQL' AS Keyword, 15 AS Cnt
Name the dataset as DataSet1
2) Click on Report on the top menu, and then click on Report Properties. Select the code tab and then paste the code given below
Dim public SMax as Integer = 7
Dim public SMin as Integer = 1
Dim public HtmlTag as String = ""
Dim Public FontSize as Integer = 5
Public Function GetFontSize(ByVal NMin AS Integer, ByVal NMax AS Integer,ByVal Num AS Integer) AS Integer
FontSize = (SMin + ((Num-NMin) * (SMax-SMin)/(NMax-NMin)))
Public Function BuildHtmlTag(ByVal Keywrd as String, ByVal Num as Integer) AS String
HtmlTag = HtmlTag & "<font size=""" & FontSize & """>" & Keywrd & " (" & Num & ") </font>"
Public Function DisplayHtml() as String
It should look like below once that is done
3) Drag and drop a table to the layout. Enter the header of the first column as KeywordCount and in the expression for the data value, enter the code below
=Code.GetFontSize(Min(Fields!Cnt.Value, "DataSet1"), Max(Fields!Cnt.Value, "DataSet1"), Fields!Cnt.Value)
Now, enter the header of the second column as Keyword and in the expression for the data value, enter the code below
Make sure to place the table in the top left corner and ensure it looks like below
4) Change the Border Style property for both the columns to None from Solid. Then resize the columns and rows of the tablix to be as small as you can (but still in the top left corner so that it is the first report item to be evaluated when the report runs). The end result should look like shown below.
Ideally, after this the tablix should be hidden when the report is previewed. You might also want to set the font colour to white and delete the headers in case the tablix is still shown. Now when you view the deployed report in Internet explorer, the tablix might push other report items down or to the left. So care should be taken to align your report items in rectangles.
5) Drag and drop a textbox wherever you want into the report and set the width of the textbox as required. Then enter the following expression
6) Click on OK. Then select the expression and right click as shown in the image below.
7) Click on the Placeholder Properties and then select the HTML option as shown in the image below.
8) With that last step, we are done and on clicking preview, we should see our neat little tag cloud.
The tag cloud can also be sorted on the basis of keywords or keyword counts also by just sorting the results of the tablix. The code can also be modified to show different colours for each of the keywords and do further more stuff, as all you need to do is to generate the html tags. An example is given in the original forum post. Now I really deserve a mug of beer after this!
Note: I don’t really know much of VB.net coding, so optimization tips for the code part are welcome in the comments section.