Adatis BI Blogs

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. 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: 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. 3. The final step is to change the properties so that the render format is set to HTML 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!