With the release of SQL Server 2016 CTP3 the inclusion of R has been a powerful and welcome addition. As R can now be directly queried from inside SQL Server Management Studio (SSMS) one can now use the data outputs from R in SQL Server Reporting Services (SSRS) thereby utilising the power of R in the convenience of SSRS.
This blog will guide you through the process of creating a simple report in SSRS using data from R.
As demonstrated in a previous blog, it is very easy to begin using R within SQL Server and this is no different.
First of you will need your SQL R Script, for which I’m producing a simple K Means cluster of employees in the Adventure Work Data Warehouse. Then you will want to wrap that query inside a stored procedure.
CREATE PROCEDURE dbo.spKMeansEmployee
AS
BEGIN
EXECUTE sp_execute_external_script
@language = N’R’,
@script = N’ClusterCount <- 4;
df <- data.frame(InputDataSet);
ClusterFeatures <- data.frame(df$BaseRate, df$VacationHours, df$SickLeaveHours, df$SalaryFlag);
ClusterResult <- kmeans(ClusterFeatures, centers = ClusterCount, iter.max = 10)$cluster;
OutputDataSet <- data.frame(df, ClusterResult);’,
@input_data_1 = N’SELECT
EmployeeKey,
BaseRate,
VacationHours,
SickLeaveHours,
CAST(SalariedFlag AS VARCHAR(1)) AS SalaryFlag
FROM dbo.DimEmployee;’
WITH RESULT SETS (( EmployeeKey INT NOT NULL,
BaseRate MONEY NOT NULL,
VacationHours INT NOT NULL,
SickLeaveHours INT NOT NULL,
SalaryFlag VARCHAR(1) NOT NULL,
ClusterResult INT NOT NULL
));
END
The next step is to create a new report in Visual Studio and add a new Data Source.
Then create a dataset.
And link that dataset to a new report.
Then build the report how you want, using that dataset. This is the quick output I’ve opted for as you can quickly analyse employees based on the cluster they are in.
As you can see, and hopefully reproduce, it’s a very quick and relatively easy process that allows you to make business decisions by utilising the combined powerful capabilities of R and SQL.
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