After several long weeks on the road, I am finally back home and digging into open-source R again. A few days ago there was a post by TIOBE Software showing R ranked 12th in the most popular programming languages. I noticed the market shifting towards R back in 2011. Today R is hard to miss if you love working with data. R is literally everywhere – it is baked into quite a few database, predictive analytics and data discovery tools including Microsoft’s Azure ML. Thus when I came across a solution for using fantastic R data visualizations within Microsoft Reporting Services (SSRS), I couldn’t resist diving in to see how it works.
Timo Klimmer, a Microsoft colleague, brilliantly extended SSRS to enable the use of R code and graphics with Custom Report Item capabilities – in SharePoint Mode as well as in Native Mode. He called this project, the “R Graphics Device for Reporting Services” and was kind enough to share this wonderful project publicly.
Custom Report Items are a little known feature for adding custom features and visualizations to out-of-the-box SSRS. They have been around since at least SSRS 2005 and were included in Microsoft code samples. I am a bit surprised that we have not seen more of these in the wild since SSRS is still extremely popular. The .NET code to make Customer Report Item extensions is most likely too high of a bar to leap over for mainstream report developers. However if the Customer Report Item code has already been written like it has been for the R Graphics Device, using it in an SSRS report can be a simple, drag-and-drop from the SSRS toolbox.
The code R Graphics Device code can be downloaded from https://rgraphicsdevice.codeplex.com if you want to give this a try yourself. If you do play with this project, please install and test the bits on a development server before trying it in a production environment. Also note that this solution does not have dedicated technical support. You will need to be comfortable opening up Visual Studio Professional, changing Visual Studio project reference settings and recompiling the SSRS.CustomReportItems.dll for this solution to work with your specific SSRS environment and Business Intelligence Development Studio (BIDS).
The R Graphics Device extension SSRS Custom Report Item project requires the following prerequisites:
- Installing a flavor of R (r-project.org, Revolution Analytics, or other) with your desired ggplot2 or other R visualization packages on the SSRS server and/or development machine
- Referencing your own version of SSRS library ReportingServices.ProcessingCore.dll (SSRS 2005, 2008, 2008R2, 2012, or 2014) within the R Graphics Device Customer Report Item .NET project code using Visual Studio, compiling SSRS.CustomReportItems.dll and then copying that newly compiled SSRS.CustomReportItems.dll to your SSRS library folder according to the included instructions that are packaged with the code
- Adding the R Graphics Device to your SSRS Toolbox in Business Intelligence Development Studio (BIDS)
Once you have R Graphics Device installed, the real fun begins! You can now drag-and drop the R Graphics Device tool in a SSRS report to add R visualizations. Popular data visualization libraries like ggplot2 now open up a whole new world of visualization types for classic SSRS! All you need to do is copy and paste you R visualization code snippets into the R Graphics Device properties window in SSRS. You can even pass in parameters.
Using R Graphics Device, you can freely extend the visualizations that are possible with SSRS today. The open-source R visualization libraries are abundant with sophisticated charts, statistical plotting and even scientific graphing. Here are a few other examples for inspiration:
If you want to learn how to code R scripts or get a better feel for what is available in R visualization libraries, check out R Graphics Cookbook by Winston Wang, ggplot2 by Hadley Wickham, Lattice: Multivariate Data Visualization with R R by Deepayan Sarkar or simply run a simple web search for R data visualization examples.
In addition to data visualizations, you could also use this solution to call R’s advanced data mining and machine learning computation algorithms from an SSRS report. For example you could apply Principal Component Analysis, Clustering, Time Series Forecasting, Social Network Analysis and many other applications. For folks that are .NET C# developers, you could also further extend this code for other purposes. If you do add to it, please publicly share your enhancements on CodePlex for the benefit of the global Microsoft business intelligence community to enjoy.
One last note…just in case you were wondering or heard rumors, I can confirm that SSRS is not dead or being sunset. At the 2014 PASS BA Conference, Microsoft executives shared a few planned updates on the roadmap. Please contact me directly if you hear other claims being made.