Word or Tag cloud
Hello, I have a table with say two coulmns : Keyword and Count. Now based on the count, I want to create a tag cloud or a word cloud of the keywords. I do not have Sharepoint or Performance point. Is it possible to do this in SSRS? Or, can i embed something other app into my SSRS reports? Thanks,
March 1st, 2012 4:27pm

You can write a user defined function in SQL Server which uses the table mentioned above and have a case statement to figure out the key word depending on the word count. REgards,Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
Free Windows Admin Tool Kit Click here and download it now
March 2nd, 2012 5:39am

If you are using SSRS 2008 R2, it should be possible but it is not straightforward. On a high level, you will need to write a custom code which will take the words and it's counts as input, and then generate a HTML tag with the word and it's font size as output. Now the custom code also should calculate the font size based on scaling the maximum and minimum values of the counts in the dataset. Let me try this in the weekend and will update it here. Meanwhile, if you can work upon the idea, awesome! :) If you are not using SSRS 2008R2 or above, you can't make a proper tag/word cloud as you see in the sites, but maybe you could drag and drop the fields onto a matrix with fixed columns (lets say, 4) and set the font size expression based on the count. The limitation is that the when the results are displayed, there would be extra spaces between the words or even word wraps, because they are actually displayed in columns while a real word cloud has only like a fixed amount of spaces between the words, and so it would look like a single sentence.Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
March 2nd, 2012 11:11am

If you are using SSRS 2008 R2, it should be possible but it is not straightforward. On a high level, you will need to write a custom code which will take the words and it's counts as input, and then generate a HTML tag with the word and it's font size as output. Now the custom code also should calculate the font size based on scaling the maximum and minimum values of the counts in the dataset. Let me try this in the weekend and will update it here. Meanwhile, if you can work upon the idea, awesome! :) If you are not using SSRS 2008R2 or above, you can't make a proper tag/word cloud as you see in the sites, but maybe you could drag and drop the fields onto a matrix with fixed columns (lets say, 4) and set the font size expression based on the count. The limitation is that the when the results are displayed, there would be extra spaces between the words or even word wraps, because they are actually displayed in columns while a real word cloud has only like a fixed amount of spaces between the words, and so it would look like a single sentence.Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
Free Windows Admin Tool Kit Click here and download it now
March 2nd, 2012 11:11am

Jason, thats a great idea. How do i create a a code to generate HTML.I am very new to ssrs and do not know much of html. COuld you please give me some pointers? Thanks
March 2nd, 2012 12:57pm

Jason, thats a great idea. How do i create a a code to generate HTML.I am very new to ssrs and do not know much of html. COuld you please give me some pointers? Thanks
Free Windows Admin Tool Kit Click here and download it now
March 2nd, 2012 12:57pm

I am neither an expert in HTML nor VB.net coding :), but then this is my idea - Create a string in the custom code which will have the HTML tags a) At the start, you will need a <p> and end with a </p> for the string b) You will need to generate a tag like it is given below for each Keyword. The font size should be calculated based on the scaling I told you <font size="5">Here is a size 5 font</font> You can return the generated string into the textbox from the custom code, and that should give you the tag cloud (As basically, it has got the HTML tags for entire keywords) This is just an idea and I have to develop it, and since I am not an expert in both HTML and custom code, I have to search it out. But I think it should work An example of how to different font sizes are used in same HTML string are given below HTML Code: <p><font size="7" face="Georgia, Arial" color="maroon">C</font>ustomize your font to achieve a desired look.</p> Beauty: Customize your font to achieve a desired look. Edit : The C was supposed to be of a lot bigger, it didnt get displayed Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
March 2nd, 2012 1:19pm

I am neither an expert in HTML nor VB.net coding :), but then this is my idea - Create a string in the custom code which will have the HTML tags a) At the start, you will need a <p> and end with a </p> for the string b) You will need to generate a tag like it is given below for each Keyword. The font size should be calculated based on the scaling I told you <font size="5">Here is a size 5 font</font> You can return the generated string into the textbox from the custom code, and that should give you the tag cloud (As basically, it has got the HTML tags for entire keywords) This is just an idea and I have to develop it, and since I am not an expert in both HTML and custom code, I have to search it out. But I think it should work An example of how to different font sizes are used in same HTML string are given below HTML Code: <p><font size="7" face="Georgia, Arial" color="maroon">C</font>ustomize your font to achieve a desired look.</p> Beauty: Customize your font to achieve a desired look. Edit : The C was supposed to be of a lot bigger, it didnt get displayed Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
Free Windows Admin Tool Kit Click here and download it now
March 2nd, 2012 1:19pm

Ok did a quick PoC to check it and it works :) Will send you the technique during the weekend when I have more time, a bit busy nowCheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
March 2nd, 2012 4:02pm

Ok did a quick PoC to check it and it works :) Will send you the technique during the weekend when I have more time, a bit busy nowCheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
Free Windows Admin Tool Kit Click here and download it now
March 2nd, 2012 4:02pm

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 LastKeywrd 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, Last(Fields!Keyword.Value,"DataSet1"),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). Also set the visibility of the tablix so that it is hidden. The end result should look like shown below. Ideally, after this the tablix should be hidden when the report is previewed. You might want to set the font colour also to white in case the tablix is still shown 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. Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
March 2nd, 2012 8:22pm

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 LastKeywrd 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, Last(Fields!Keyword.Value,"DataSet1"),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). Also set the visibility of the tablix so that it is hidden. The end result should look like shown below. Ideally, after this the tablix should be hidden when the report is previewed. You might want to set the font colour also to white in case the tablix is still shown 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. Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
Free Windows Admin Tool Kit Click here and download it now
March 2nd, 2012 8:22pm

Jason , this is AWESOME. I just need one more thing. Can i change the color of the font? Is that possible? I am an amateur but was thinking of something like this, the way you are getting font size in the code, you get a color. Say you have an array with different color codes, and then you select one for each keyword. I just dont know how to code this. Please help again. Thank you so much.
March 3rd, 2012 12:09am

Jason , this is AWESOME. I just need one more thing. Can i change the color of the font? Is that possible? I am an amateur but was thinking of something like this, the way you are getting font size in the code, you get a color. Say you have an array with different color codes, and then you select one for each keyword. I just dont know how to code this. Please help again. Thank you so much.
Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2012 12:09am

1) Replace step 2 with the code 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, ByVal FColor as String) AS String HtmlTag = HtmlTag & "<font size=""" & FontSize & """><font color="""& FColor & """>" & Keywrd & " </font></font>" End Function Public Function DisplayHtml() as String return HtmlTag End Function Private colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#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 2) Replace the the second column (Keyword) expression for the data value as below =Code.BuildHtmlTag(Fields!Keyword.Value,Fields!Cnt.Value, Code.GetColor(Fields!Keyword.Value)) You can change the colors to whatever you want by modifying the below variable in code Private colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"} Feel free to contact me at jason143@gmail.com if you have further doubts on this :) Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
March 3rd, 2012 6:55am

1) Replace step 2 with the code 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, ByVal FColor as String) AS String HtmlTag = HtmlTag & "<font size=""" & FontSize & """><font color="""& FColor & """>" & Keywrd & " </font></font>" End Function Public Function DisplayHtml() as String return HtmlTag End Function Private colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#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 2) Replace the the second column (Keyword) expression for the data value as below =Code.BuildHtmlTag(Fields!Keyword.Value,Fields!Cnt.Value, Code.GetColor(Fields!Keyword.Value)) You can change the colors to whatever you want by modifying the below variable in code Private colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"} Feel free to contact me at jason143@gmail.com if you have further doubts on this :) Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2012 6:55am

This is great.Thank you so much Jason.
March 3rd, 2012 2:20pm

Hi Jason, the word cloud looks great. Can we make the words clickable. As in, if we click on a particular word, it should take to to the details of that word. Can we pass that word as a parameter to another report? Since this is an old post, should I create a new one for this? Thanks,
Free Windows Admin Tool Kit Click here and download it now
March 23rd, 2012 1:01pm

Hi Jason, the word cloud looks great. Can we make the words clickable. As in, if we click on a particular word, it should take to to the details of that word. Can we pass that word as a parameter to another report? Since this is an old post, should I create a new one for this? Thanks,
March 23rd, 2012 1:01pm

You can modify the code to include the HREF tags for hyper-linking. Now, you should pass as input (or at least declare a variable) the report's link and just concatenate the ReportParameterName in the urlCheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
Free Windows Admin Tool Kit Click here and download it now
March 23rd, 2012 3:03pm

You can modify the code to include the HREF tags for hyper-linking. Now, you should pass as input (or at least declare a variable) the report's link and just concatenate the ReportParameterName in the urlCheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
March 23rd, 2012 3:03pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics