none
Word or Tag cloud

    Question

  • 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,


    Thursday, March 01, 2012 9:23 PM

Answers

  • 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

    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, 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

    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). Also set the visibility of the tablix so that it is hidden. 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 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.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. 


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Saturday, March 03, 2012 1:22 AM
    Moderator
  • 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! :)

    Some Random Thoughts

    Follow me on Twitter


    Saturday, March 03, 2012 11:53 AM
    Moderator

All replies

  • 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.

    Friday, March 02, 2012 10:34 AM
  • 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! :)

    Some Random Thoughts

    Follow me on Twitter

    Friday, March 02, 2012 4:10 PM
    Moderator
  • 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

    Friday, March 02, 2012 5:56 PM
  • 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! :)

    Some Random Thoughts

    Follow me on Twitter



    Friday, March 02, 2012 6:18 PM
    Moderator
  • 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 now


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Friday, March 02, 2012 9:01 PM
    Moderator
  • 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

    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, 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

    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). Also set the visibility of the tablix so that it is hidden. 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 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.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. 


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Saturday, March 03, 2012 1:22 AM
    Moderator
  • 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.



    • Edited by CK1286 Saturday, March 03, 2012 5:33 AM
    Saturday, March 03, 2012 5:07 AM
  • 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! :)

    Some Random Thoughts

    Follow me on Twitter


    Saturday, March 03, 2012 11:53 AM
    Moderator
  • This is great.Thank you so much Jason.
    Saturday, March 03, 2012 7:18 PM
  •  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,


    Friday, March 23, 2012 4:57 PM
  • 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 url


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Friday, March 23, 2012 7:00 PM
    Moderator
  • Just an update, did the hyperlinking also for one of the community members, and so thought of updating the code here

    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 & " . " &"<a href=""https://www.google.com/search?&q=" & Keywrd &"""> <font size=""" & FontSize & """>"& Keywrd & " (" &  Num & ")" &" </font></font></a>"
    Return Keywrd
    End Function 
    
    Public Function DisplayHtml() as String
    return HtmlTag
    End Function

    You will just have to replace the url with your report url. Currently, it opens the google site for each of the clicked words


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Thursday, August 02, 2012 4:54 PM
    Moderator
  • Hi Jason,

    I now have a word cloud with hyperlinks. But when a word has an hyperlink it is underlined. I would like to have the words, which have hyperlinks, without underline. Is there any solution for it? I already tried it with the css style text-decoration: none; - but it doesn't work.

    Thanks, Sarah

    Monday, February 25, 2013 9:28 AM
  • Hi Sarah,

    Were you able to find a solution for removing the underline from hyperlinks? I noticed that only a subset of HTML tags are supported within SSRS. I also found another word cloud solution at blogs.adatis.co.uk/blogs/jeoc/archive/2013/09/06/word-cloud-reports.aspx but the issue with style formatting persists.

    Thanks, Rick.

    Friday, May 30, 2014 10:11 PM