SQL Server 2016 R Services: Display R plots in Reporting Services

By:   |   Comments (13)   |   Related: > SQL Server 2016


Problem

SQL Server 2016 comes with the integration of the popular language R into the database engine. This feature has been introduced in SQL Server 2016 preview CTP3.0 and is called SQL Server R Services. In previous tips we explained how to set-up the client and server machines, how to leverage the in-database computation engine for executing R scripts and how to execute R code using T-SQL. In this tip, we are going to combine all that knowledge to embed data visualizations created with R into Reporting Services (SSRS) reports.

Solution

SQL Server 2016 preview

As mentioned earlier, at the time of writing SQL Server 2016 is still in preview (at the time of writing CTP 3.1 has been released). This means that the features of R Services can still change and that functionality might change, disappear or be added.

Prerequisites

To completely understand this tip and to follow the examples along, please read the following tips first:

The most important tips are the server configuration and how you can execute R code in SQL Server using T-SQL.

Creating an R plot using T-SQL

Using the stored procedure sp_execute_external_script we can use an R script to create a data visualization, also called a plot. In the following example, we are going to create a histogram plot of the various ages in the AdventureWorks customer dimension. The script looks as follow:

EXEC   sp_execute_external_script
      @language = N'R'
     ,@script = N'	df <- inputDataSet; #read input data
				image_file = tempfile(); #create a temporary file
				jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
				hist(df$Ages); #plot the histogram
				dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
				#file() opens a file, in this case the image. rb = read binary
				#readBin() reads binary data. what = described the mode of the data. In this case, it''s raw data. n = maximum number of records to read.
				#data.frame converts the data to a data frame, which is required as output by SQL Server. The result is written to the OutputDataset variable.
				OutputDataset <- data.frame(data=readBin(file(image_file,"rb"),what=raw(),n=1e6));
					'
	,@input_data_1 = N'SELECT Ages = DATEDIFF(YEAR,[BirthDate],GETDATE())
						FROM [AdventureWorksDW2014].[dbo].[DimCustomer];'
	,@input_data_1_name = N'inputDataSet'
	,@output_data_1_name = N'OutputDataset'
WITH RESULT SETS ((plot varbinary(max)));

Let's go over the different steps of the script:

  1. First read the results of the query passed as input. The name given to this result set is inputDataSet. The result is stored in a data frame with the name df.
  2. Next a temporary file is created. This file will act as a placeholder for the JPEG image of our plot.
  3. The temporary file is defined as a JPEG graphic device, with width and height both equal to 500 pixels.
  4. Then the histogram is created over the Ages column of the df data frame using the hist function.
  5. If you would run the code inside a client environment, print(hist(x)) would create a separate window containing the data visualization. This is called a graphic device. dev.off() shuts down this device. Leaving this statement out of the script will result in an NULL value being returned, because dev.off() needs to return the number and name of the last active device.
  6. Finally the binary raw data of the temporary image file is being read using the functions readBin and file. The result is converted to a data frame with the data.frame function since SQL Server expects a data frame. The result is stored in the variable OutputDataset, which is defined as the output result set using the @output_data_1_name parameter of the stored procedure.
  7. In the WITH RESULT SETS clause, the output is described as a plot of data type varbinary(max).

When we run the stored procedure, we can see a binary string is being returned:

Executing stored proc

Embedding the plot in SSRS

The hard work has already been done. Using an INSERT ... EXEC statement, we insert the binary string in a table. In order to do this, the WITH RESULT SETS clause must be omitted, since the INSERT ... EXEC statement doesn't support this clause. First create a table to store the binary data:

IF NOT EXISTS (SELECT 1 FROM sys.objects
				WHERE	object_id = OBJECT_ID(N'[dbo].[Plots]')
					AND [type] IN (N'U'))
BEGIN
	CREATE TABLE [dbo].[Plots](
		[plot] [varbinary](MAX) NULL
	);
END

Now insert the data into the table using INSERT ... EXEC.

Storing the results into a table

Now let's create a new report. Create a data source pointing to the database where the dbo.Plots table is located. Then create a dataset with the following SQL statement embedded:

SELECT TOP 1 plot FROM dbo.Plots;

The dataset looks like this:

SSRS dataset

Next create an image on to the report canvas.

insert image

In the Image Properties dialog, set the image source to database. Use the field plot of the dataset and set the MIME type to jpeg.

set properties

In the Size pane, set the display size to Original size.

set size properties

When the report is run, it will show the jpeg file.

set size properties

Note: directly using the stored procedure in the dataset doesn't work properly, as the SSRS reports creates report parameters for each parameter of the stored procedure. You can either select the results from a table - as we did before - or wrap the stored procedure inside another stored procedure.

parameters be damned

Another example

In the blog post R You Ready For SQL Server 2016? of Jen Underwood you can find another example created by the Microsoft product team. The example uses a famous sample data set about flowers. The final (and slightly adjusted) script is as follows:

INSERT INTO dbo.Plots(plot)
EXEC   sp_execute_external_script
  @language = N'R'
 ,@script = N'
	library(ggplot2); #import ggplot2 library
	image_file = tempfile(); #create a temporary file
	jpeg(filename = image_file, width=600, height=800); #create a JPEG graphic device
	#qplot = quick plot, a helper function of ggplot2
	qplot(Sepal.Length, Petal.Length, data = iris, color = Species,
		xlab = "Sepal Length", ylab = "Petal Length",
		main = "Sepal vs Petal Length in Fisher''s Iris data");
	dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
	#file() opens a file, in this case the image. rb = read binary
	#readBin() reads binary data. what = described the mode of the data. In this case, it''s raw data. n = maximum number of records to read.
	#data.frame converts the data to a data frame, which is required as output by SQL 1Server. The result is written to the OutputDataset variable.
	OutputDataset <- data.frame(data=readBin(file(image_file,"rb"),what=raw(),n=1e6));
	'
 ,@input_data_1 = N''
 ,@output_data_1_name = N'OutputDataset';

The R script makes use of the ggplot2 package, a well-known package with powerful data visualization methods. It's possible you have to import this package first on your machine before you run the R script. When the same steps as in the previous section are followed, we get the following chart in SSRS:

flower parade

It's easy to see that with R you can quickly generate powerful data visualizations, some of which are real hard to create in SSRS with out-of-the-box capabilities.

Note: it's possible that the chart has the right size in the preview in Report Builder, but when you actually run the report in Report Manager it's a lot smaller. If that's the case, try setting the size property to Fit Proportional and resizing the image object on the report canvas to the desired size.

Conclusion

By using the graphic capabilities of the R language and the possibility to execute R scripts using T-SQL, it's straight forward to embed compelling data visualizations into SSRS reports. These graphs can fill the void of chart types who currently are not available yet in SSRS or which are hard to create.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, August 2, 2018 - 3:30:44 AM - Koen Verbeeck Back To Top (76940)

Hi James,

yes, you can probably modify the scripts to make it more secure. SELECT TOP 1 FROM dbo.plots isn't exactly a best practice :)
This tip was meant as an introduction to the concept of using R images in SSRS, so the code can use some optimization.

Regards,
Koen

Wednesday, July 18, 2018 - 3:16:15 PM - James Holland Back To Top (76671)

 I'm curious, would it be more secure to insert the plot binary into a temp table?  In order to prevent a plot that was created by another user to show up in a different report?

 

Wednesday, November 15, 2017 - 3:02:28 AM - Koen Verbeeck Back To Top (69755)

The error message is quite clear: the sp_execute_external_script procedure excepts the parameter @input_data_1 to be of data type nvarchar(max), while my script specifies VARCHAR(1000).

Tuesday, November 14, 2017 - 12:00:50 PM - Mario Back To Top (69720)

I KOEN...

thanks a lot for your attention.  Unfortunately  :-( 

---IF I TRY TO EXECUTE  THIS ------  (5 is parameter value)
USE AdventureWorksDW2014
GO 
dbo.Test 5         

---- I HAVE THIS ERROR -----
Msg 214, Level 16, State 179, Procedure sp_execute_external_script, Line 1 [Batch Start Line 40]
Procedure expects parameter '@input_data_1' of type 'nvarchar(max)'.

 

Tuesday, November 14, 2017 - 3:21:41 AM - Koen Verbeeck Back To Top (69706)

Mario,

you forgot to remove the semicolon after the FROM clause.
I would create a stored proc like this (I haven't tested it because I don't have SQL Server Machine Learning Services installed on my current machine):

 

CREATE PROC dbo.Test

@Age INT = 100

AS

BEGIN

 

DECLARE @SQL VARCHAR(1000) = 'SELECT Ages = DATEDIFF(YEAR,[BirthDate],GETDATE())

FROM [AdventureWorksDW2014].[dbo].[DimCustomer]

WHERE Ages < @external_parameter;';

 

SET @SQL = REPLACE(@SQL,'@external_parameter',@Age);

 

EXEC   sp_execute_external_script

      @language = N'R'

     ,@script = N'    df                 image_file = tempfile(); #create a temporary file

                jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device

                hist(df$Ages); #plot the histogram

                dev.off(); #dev.off returns the number and name of the new active device 

                #file() opens a file, in this case the image. rb = read binary

                #readBin() reads binary data. what = described the mode of the data.

                #data.frame converts the data to a data frame, which is required as output by SQL Server. 

                OutputDataset                     '

    ,@input_data_1 = @SQL

    ,@input_data_1_name = N'inputDataSet'

    ,@output_data_1_name = N'OutputDataset'

--WITH RESULT SETS ((plot varbinary(max)));

END

Monday, November 13, 2017 - 8:16:20 AM - Mario Back To Top (69657)

Hi Koen.

Below I copy your same sp_execute_external_script. I want add a parameter inside the @input_data_1.
This parameter will be added by the report user.

In SSRS reports I want to create this report parameters (where users can set the Age limit values) . example minus than 70 years old.


DatasetProperties
Choose query parameter values
parameter Name = @Ages


------------------------------------- your script (whit my parameter WHERE Ages < @external_parameter)---------------------------------------
EXEC   sp_execute_external_script
      @language = N'R'
     ,@script = N'    df                 image_file = tempfile(); #create a temporary file
                jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
                hist(df$Ages); #plot the histogram
                dev.off(); #dev.off returns the number and name of the new active device
                #file() opens a file, in this case the image. rb = read binary
                #readBin() reads binary data. what = described the mode of the data.
                #data.frame converts the data to a data frame, which is required as output by SQL Server.
                OutputDataset                     '
    ,@input_data_1 = N'SELECT Ages = DATEDIFF(YEAR,[BirthDate],GETDATE())
                        FROM [AdventureWorksDW2014].[dbo].[DimCustomer];

                        WHERE Ages < @external_parameter'

    ,@input_data_1_name = N'inputDataSet'
    ,@output_data_1_name = N'OutputDataset'

--WITH RESULT SETS ((plot varbinary(max)));

-----------------

Note.

If age a complitcated because a derived field .. I 'll apreciate also an example on a varchar field..

Thaks a lot. Mario

Friday, November 10, 2017 - 8:21:54 AM - Koen Verbeeck Back To Top (69518)

Alright Mario, what do you have so far? Which part of the code are you stuck on?

Koen

Friday, November 10, 2017 - 7:49:51 AM - mario Back To Top (69517)

 Koen please can you help me with an example of report parameter ?

Thanks

 

Wednesday, November 8, 2017 - 12:11:05 PM - Mario Back To Top (69441)

 Thanks Koen for your answer.

Unfortunately I am not very good at all.

If you have some minuts please can you continue your example adding the user parameter of Age ?

You would make me a great pleasure.

Mario

 

Wednesday, November 8, 2017 - 9:21:26 AM - Koen Verbeeck Back To Top (69436)

Hi Mario,

you can wrap the call to EXEC sp_execute_external_script in another stored procedure.

There you can perhaps create the SELECT statement using the passed parameters of SSRS and store it in a variable, which you pass to the @input_data_1 parameter.

Wednesday, November 8, 2017 - 7:11:50 AM - mario Back To Top (69429)

 Hi. really good article.

I haven't understand if is possible add an interactive filter (parameter) in my report SSRS based on R script.

For example in the first example of your article I want that the report users can filter the Age (df$Ages) or easily values from source table.

Thanks a lot.

Mario

 

Monday, February 6, 2017 - 3:24:16 AM - Koen Verbeeck Back To Top (46047)

Hi Justin,

thanks for your comment. Useful info.

Regards,

Koen

Saturday, February 4, 2017 - 8:05:38 AM - Justin J Davies Back To Top (45950) 
Note that the code in the second example (may) require you to wrap the call to qplot in a print() to ensure the ggplot is printed to the device.

print( qplot(Sepal.Length, Petal.Length, data = iris, color = Species,
		xlab = "Sepal Length", ylab = "Petal Length",
		main = "Sepal vs Petal Length in Fisher''s Iris data") );

It appears later versions R services on MSSQL (potentially) don't automatically push ggplot calls to the device - see http://stackoverflow.com/questions/6675066/ggplots-qplot-does-not-execute-on-sourcing and














get free sql tips
agree to terms