R Chart into SSRS (ggplot2 package in SQL Server 2017)-Part 2

“In the last post, you have seen how we can create a chart by importing data from SQL Server 2017. By doing this, we have a reliable code for drawing chart. Now, we sure our codes work fine, we can put the codes in SQL Server 2017.

In this post, I am going to show how we can have the same chart in SSRS.

I already explained in Post about writing code in SQL Server how to run the R codes inside SQL Server 2017 using the external stored procedure.

ggplot2 Package in ML Services

you able to see the name of installed packages in ML services using some R codes.

EXECUTE sp_execute_external_script @language=N'R' 
 ,@script = N'str(OutputDataSet); packagematrix <- installed.packages(); 
 NameOnly <- packagematrix[,1]; OutputDataSet <- as.data.frame(NameOnly);'
  ,@input_data_1 = N'SELECT 1 as col' 
  WITH RESULT SETS ((PackageName nvarchar(250) ))

the function to check the name of installed packages in R scripts is “installed.package()”.

when I run this for the first time there is no package name “ggplot2”

I need to install this package to work with it,

The new question comes up:

How to install new Packages in SQL Server 2017 R services?

there are many ways:

use command install.packages in external scripts store procedure as below

the first approach

EXECUTE sp_execute_external_script @language=N'R' 
 ,@script = N' install.packages("ggplot2");'
  ,@input_data_1 = N'SELECT 1 as col' 
  WITH RESULT SETS ((PackageName nvarchar(250) ))

the above codes may work but sometimes not because of the administrator permission, you may get below message “Warning in install.packages(“arules”) :
‘lib = “C:/Program Files/Microsoft SQL Server/MSSQL14.ML2017/R_SERVICES/library”‘ is not writable
Error in install.packages(“ggplot2”) : unable to install packages
Calls: source -> withVisible -> eval -> eval -> install.packages

Solution

Run as admin Rugi.exe to install the package. but you need to specify where to install the packages. As a result, we need the path of the ML services for R in SQL Server. there is a function in R: .libPaths() that show the location of installed packages.  so you need to run this code in SQL Server 2017 to find out the R library path.

EXECUTE sp_execute_external_script  @language = N'R'
, @script = N'OutputDataSet <- data.frame(.libPaths());'
WITH RESULT SETS (([DefaultLibraryName] VARCHAR(MAX) NOT NULL));
GO

the result will be

after that, you able simply run the below codes to install packages

lib.SQL <- "C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\R_SERVICES\\library" 
  install.packages("ggplot2", lib = lib.SQL)

still, you may get the error!

Another problem 

you install ggplot2, but this package need other packages to work properly, so you need to install other related packages as well.

Easy but not reliable approach: copy the whole package list you have in your local Rstudio into SQL Server location.

this approach is not a trusted one, but most of the time will work just be careful about the package versions and also the not replace the one that already exists.

by run the below code you will be sure ggplot2 has been installed:

exec sp_execute_external_script  @language =N'R',  
@script=N'OutputDataSet<-InputDataSet; library("ggplot2")',    
@input_data_1 =N'select 1 as hello'  
with result sets (([hello] int not null));  
go

the result should be

Now I am sure I have access to the ggplot2 package.

I am able to create chart inside SQL Server. IN the next post I will show how to shows the chart in SSRS, or show as an HTML chart.

Leila Etaati

Trainer, Consultant, Mentor

Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.