Pages

Monday, March 5, 2012

Making a Tag Cloud with SSRS Rich Text

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.
SSRS Tag Cloud
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:-
My Blog's tag cloud
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
UNION ALL
SELECT        'SSAS' AS Keyword, 26 AS Cnt
UNION ALL
SELECT        'MDX' AS Keyword, 20 AS Cnt
UNION ALL
SELECT        'Interview Questions' AS Keyword, 18 AS Cnt
UNION ALL
SELECT        'Personal' AS Keyword, 17 AS Cnt
UNION ALL
SELECT        'Activities' AS Keyword, 16 AS Cnt
UNION ALL
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)))
Return Num
End Function

Public Function BuildHtmlTag(ByVal Keywrd as String, ByVal Num as Integer) AS String
HtmlTag = HtmlTag & "<font size=""" & FontSize & """>" & Keywrd & " (" &  Num & ") </font>"
Return Keywrd
End Function

Public Function DisplayHtml() as String
return HtmlTag
End Function

It should look like below once that is done
Report Properties 
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
=Code.BuildHtmlTag(Fields!Keyword.Value, Fields!Cnt.Value)
Make sure to place the table in the top left corner and ensure it looks like below
Table which calls the report code
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.
Resized tablix
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
=Code.DisplayHtml()
6) Click on OK. Then select the expression and right click as shown in the image below.Placeholder properties
7) Click on the Placeholder Properties and then select the HTML option as shown in the image below.
Interpret HTML tags as style
8) With that last step, we are done and on clicking preview, we should see our neat little tag cloud.
Tag Cloud in SSRS
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.

5 comments:

  1. Hi Jason,
    Saw your answer in MSDN..nice...but i have experienced that the tablix being placed at left corner will create problems as it might not be rendered before your text box having place holder..Any ideas to overcome this issue.!!

    ReplyDelete
  2. As long as the text box is after the tablix (to the right and bottom), it should be rendered after the tablix only. To be extra sure, check out the left and top properties in Location and make sure it is atleast a bit to the right and bottom. Also, if there are report items on the top or left, make sure that our tablix and textbox don't get moved around by the proper use of rectangles.
    If you still face issues, I would be interested to check it out and you can send the rdl to jason143@gmail.com

    ReplyDelete
  3. Thanks for the post! Your approach has definitely come in handy.

    I borrowed part of your solution, and mixed in the approach advocated in this thread:

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a7d59224-0ee5-491e-883b-2e5fcb3edeab

    which demonstrates how you can use a multi-valued report parameter to emulate a dataset. This approach allowed me to eliminate the tablix, but enjoy the same effects.

    ReplyDelete
    Replies
    1. Could you show us an example?

      Delete