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
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
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
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
=Code.DisplayHtml()
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.
Related
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.!!
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
Good One Jason!!
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.
Could you show us an example?
So I’ve followed through this several times and cannot seem to get it to work. I tried in both 2010 and 2013. I’ve never used custom code in SSRS before but what you described looks straight forward, and I have the information EXACTLY as you do. But for some reason, when I do my expression for Keyword Count, “GetFontSize” gives me a red squiggle and will not run. It actually gives the squiggle for “BuildHtmlTag” as well but it will run if I only use that expression, but the other causes it to error with:
“The value expression for the textrun “Textbox2.Paragraphs[0].TextRuns[0]’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.”
Any insight on this by chance? Thanks!
Did you name the dataset as Dataset1, as said at the end of step 1? The error has something to do with the scope parameter, and that should be the name of your dataset in the KeywordCount measure
I did indeed. Well, DataSet1, anyway. All the other expressions work fine, just not that one for some reason.
Can you send me an email at jason143@gmail.com with the report rdl and some more explanation? I can take a look at the issue if you want
If you copied the code from html page, reference the quotes used for the “DataSet1”.
I my trying to replicate this in SSRS 2016. The table with Count and Keyword shows data properly, but nothing is available on the textbox with a placeholder calling =Code.DisplayHtml().
can you please let me know, is this related to SSRS 2016 version?
The only difference here is that the Count value for me is a decimal value, so I changed the code accordingly
Dim public SMax as Single = 7
Dim public SMin as Single = 1
Dim public HtmlTag as String = “”
Dim Public FontSize as single = 5
Dim Public FF as integer=5
Public Function GetFontSize(ByVal NMin AS Single, ByVal NMax AS Single,ByVal Num AS Single) AS Integer
If NMax NMin then
FontSize = (SMin + ((Num-NMin) * (SMax-SMin)/(NMax-NMin)))
FF=CInt(FontSize)
End If
return Num
End Function
Public Function BuildHtmlTag(ByVal Keywrd as String, ByVal Num as Single) AS String
HtmlTag = HtmlTag & “” & Keywrd & ” (” & Num & “) ”
Return Keywrd
End Function
Public Function DisplayHtml() as String
return HtmlTag
End Function
Got it, I was placing the Text box before the table.
Thanks for the Awesome post. Hope to have this resolved in 2016, where we can plug in R code in ssrs and show the word cloud as Image