• Home
  • About
  • Resume

Using FusionCharts in SSRS reports

By , 01/30/2017 12:11 PM

Microsoft’s SSRS is pretty advanced reporting system with multitude of advanced features. SSRS also has charting capabilities, but it’s somewhat lacking compared to more advanced desktop or web charting suites

On the other hand FusionCharts offers very cool charting package with gazillion of chart types and very cool features. But it uses JavaScript engine and renders charts client-side only!

What if there was a way to marry the two technologies together – to render cool FusionCharts in advanced SSRS repots?

There is a way and it involves something called “headless browsers”. A headless browser is a type of applications that can render a web page – including all the images, styles and client-side code in any environment it’s designed to run in and output that rendered page in a given format (image, PDF etc.). So here is the plan:

  1. Render a page with FusionChart in a headless browser and output the chart into a PNG file
  2. Insert the PNG file into a varbinary field in SQL Server table
  3. Use that field as a database-supplied image in SSRS report

I’ve decided to go with FusionChart’s recommended PhantomJS as a headless browser for this project. It’s very flexible and is scriptable with its own JS API. Here’s a piece of code that will tell PhantomJS to render a web page and save it as a file:

var page = require('webpage').create(),
system = require('system'),
webpageURLWithChart  = system.args[1],
outputImageFileName = system.args[2],

delay = 1000;
 
page.open(webpageURLWithChart, function () {
   window.setTimeout(function () {

      page.render(outputImageFileName);
      phantom.exit();
 
   }, delay);
 
});

The code accepts 2 parameters: Source URL and output filename. So if you save this code as “fusioncharts.js” and call it in console, for example as:

phantomjs fusioncharts.js https://www.google.com/ c:\temp\google.png

it will render current appearance of Google homepage into PNG file.

With PhantomJS set and working it’s time to call it from server-side code. Let’s assume that the page that renders your FusionChart is located at “http://myserver/renderchart.aspx?param=[some param]”. It accepts some parameter and renders chart accordingly.

The following code will pass this page to PhantomJS for rendering and save output as a file:

 Dim sURL As String = "http://myserver/renderchart.aspx?param=" & Request("param")
Dim sPhantomJSpath As String = "C:\phantomjs\bin\"
Dim sImagePath As String = "c:\temp\chart.png"

Dim generateChart As New ProcessStartInfo(sPhantomJSpath & "phantomjs.exe ", _
    sPhantomJSpath & "fusioncharts.js " & sURL & " " & sImagePath)
generateChart.WindowStyle = ProcessWindowStyle.Hidden
Process.Start(generateChart).WaitForExit()

This code will start a new process to call PhantomJS executable, pass URL of chart source and path of destination for generated chart image. Ordinarily this process is asynchronous, but we need to wait for chart to finish rendering so we can insert completed image into DB:

 Dim sSQL As String = "INSERT INTO CUSTOM_IMAGES (image_id,image_blob) SELECT 1, BulkColumn FROM Openrowset( Bulk '" & sImagePath & "', Single_Blob) as img"
Dim oConn As New SqlConnection("[your connection string];") : oConn.Open()
Dim oComm As New .SqlCommand(sSQL, oConn)
oComm.ExecuteNonQuery()
oConn.Close()

With the image in the table you can easily insert it into your report from respective dataset:
Fusion Chart in SSRS Report

Leave a Reply

Panorama Theme by Themocracy