Jonathon

Jonathon Eve-O'Connor's Blog

Word Cloud Reports in SSRS

Whilst SSRS does not have an inbuilt utility for building Word Cloud reports (sometimes also called tag clouds) this can be achieved with a little HTML as per the sample below.

WordCloud

Essentially the concept is that you generate a HTML string which allows you to control the sizes and colours the of the text which you can then render in a Tablix or Text Box.

There are many ways of achieving this, the method that I will describe dynamically sizes the text depending on the results generated from the query, you will need to decide if this meets your needs and adapt to your requirements as appropriate but the central concepts will be the same. For this example I have used the AdventureWorksDW database, available for download from: http://msftdbprodsamples.codeplex.com/releases/view/55330

1. The first step is to construct a query in the dataset that will return the html string in the required format. To produce this report I used the query below. As noted in the comments in the query, you can amend the parameters to control the text sizing and base size.

DECLARE @max INT
DECLARE @min INT
DECLARE @base INT = 6 --This sets the base text size.
DECLARE @scalefactor INT = 60 --This controls amount of size increase.
DECLARE @multiplier FLOAT
DECLARE @divisor FLOAT

CREATE TABLE #tmpProductOrders
     (Product VARCHAR(250)
     ,Orders INT 
     )

INSERT INTO #tmpProductOrders
SELECT
       PSC.EnglishProductSubcategoryName AS Product
      ,SUM(FI.OrderQuantity) Orders
FROM [dbo].[FactInternetSales] FI
INNER JOIN [dbo].[DimProduct] P
        ON P.ProductKey = FI.ProductKey
INNER JOIN [dbo].[DimProductSubcategory] AS PSC
        ON PSC.ProductSubcategoryKey = P.ProductSubcategoryKey
GROUP BY PSC.EnglishProductSubcategoryName

SELECT @max=MAX(Orders), @min=MIN(Orders) FROM #tmpProductOrders
SELECT @divisor = CAST((@max-@min) AS FLOAT);
SELECT @divisor=IIF(@divisor=0.00, 1.00, @divisor)
SELECT @multiplier = (@scalefactor-@base)/@divisor

SELECT
          REPLACE(REPLACE(
                  (SELECT '<span style=font-size:' + CAST(u.Fontsize AS VARCHAR(10)) + 'pt;>' + u.Product + '</span><span> </span>'
                   FROM
                      (
                       SELECT
                              Product
                             ,Orders
                             ,@base + ((@max-(@max-(Orders-@min)))*@multiplier) AS Fontsize
                       FROM
                               (
                                SELECT
                                          Product
                                         ,Orders
                                FROM #tmpProductOrders
                               ) sh
                       ) u
                    ORDER BY NEWID() --This provides a random ordering
                   FOR XML PATH('') )
             , '&lt;','<'),'&gt;','>'
           ) AS KeywordCloud

DROP TABLE #tmpProductOrders

2. Once you have the query which produces the HTML in the required format the next step is to place this into a Text Box or Tablix within the report. For this example I used a Text Box and expression to populate the data. It is important to note here that unlike most browsers the HTML rendering engine in SSRS is very sensitive and you need to be careful as slight errors in syntax or use of unrecognised tags will stop the html from rendering.

image

3. The final step is to change the properties so that the render format is set to HTML

image

One can also use the HTML to change the colour or other properties of the individual words as required. I hope you find this useful!

Loading