Recently Timo Klimmer, a Microsoft colleague, brilliantly extended SSRS to enable the use of R code and graphics using Custom Report Item capabilities. He called this project, the "R Graphics Device for Reporting Services" and was kind enough to share this wonderful project publicly on CodePlex.
Custom Report Items can be used to extend base SSRS features and visualizations in SharePoint Mode or Native Mode. They have been around at least since the SQL Server 2005 release. Several sample Custom Report Item Visual Studio projects were included with Microsoft’s box release code samples. In typical reporting environments, Custom Report Items are rarely used due to the steep .NET learning curve of creating these add-ins. You are more likely to find them packaged as a third-party add-in solutions or within application development shops. However, if the Custom Report Item code has already been written, compiled and installed, it is quite easy to use them within an SSRS report. You can simply drag and drop an installed Custom Report Item just as you would a Chart or Table from the SSRS Toolbox.
With R Graphics Device installed, you can experiment with a plethora of different data visualizations that R graphics libraries support. Popular R visualization libraries like ggplot2 are now an option to use in classic SSRS reports. To add an R data visualization, copy and paste you R code snippets into the R Graphics Device properties window in SSRS. The current version also has SSRS to R parameter support.
R visualization libraries are abundant with classic charts, statistical plotting and even sophisticated scientific graphing. Not only can you draw a basic chart, often you can add annotations, shading or lines for showcasing confidence intervals or quartiles. A few examples of R visualizations include:
- Heat Maps
- Tree Maps
- Word Clouds
- Histograms
- Density Plots
- Boxplots
- Network Diagrams
- Sankey Diagrams
- Decision Trees
- Surface Plots
If you want to learn more about R visualization libraries and scripting, the R Graphics Cookbook by Winston Wang or ggplot2 by Hadley Wickham are highly recommended. Alternatively, you could search the internet for R visualization examples and tutorials.
In addition to data visualizations, you could also use the R Graphics Device 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.
If you want to install and test the free R Graphics Device, the Visual Studio 2013 solution code can be downloaded from https://rgraphicsdevice.codeplex.com/. Keep in mind that the Visual Studio version needed for compiling the R Graphics Device dll for your environment is not the same Visual Studio BIDS version where you develop SSRS reports. The two Visual Studio flavors can be installed side-by-side. To generate the R Graphics Device install dll, you need a developer version of Visual Studio 2013. You will also need to be comfortable changing Visual Studio project settings to reference your specific SSRS environment libraries. If you do evaluate the R Graphics Device project, install and test it initially on a development server. Do not test this project in a production environment; it does not have dedicated technical support.
The SSRS Custom Report Item R Graphics Device project requires the following prerequisites:
- Installing 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 Microsoft.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 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 into a different Visual Studio version, Visual Studio Business Intelligence Development Studio (BIDS) where you develop SSRS reports
For more ambitious .NET C# developers, feel free to further extend this code for other purposes. If you do add more functionality, please publicly share your enhancements on CodePlex for the benefit of the global Microsoft business intelligence community to enjoy.
