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: 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 ‘ + CAST(u.Fontsize AS VARCHAR(10)) + ‘pt;>’ + u.Product + ‘ ‘
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(”) )
, ‘<‘,‘<‘),‘>’,‘>’
) 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!
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr