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.

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

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


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.

the result will be

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

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:

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
Dr. Leila Etaati is Principal Data Scientist, BI Consultant, and Speaker. She has over 10 years’ experience working with databases and software systems. She was involved in many large-scale projects for big sized companies. Leila has PhD of Information System department, University of Auckland, MS and BS in computer science. Leila is Microsoft Data Platform MVP.

Leave a Reply

Your email address will not be published. Required fields are marked *