Running Highcharts within SSRS (or any JS Graph Library)

22 Jan
January 22, 2013

In a previous post I described how to convert an SSRS graph into a Highcharts graph by consuming the XML output of the report from the SSRS Web Service and converting that to an input for a Highcharts graph.

That article seemed to be very popular (in fact was the top most popular for a while), so I decided to take this concept a step further, In this article I will show you how, using JavaScript injection into SSRS reports, you can display a Highcharts graph from within SSRS itself (just like any other SSRS report) when the SSRS report is rendered into HTML.

I have created a working example of the SSRS and Highcharts integration (1 RDL 1 JS file), I will be referencing this code throughout the article, and also show which bits of the code you will need to edit to get Highcharts to show on your own SSRS setup.

The concept is very simple and is based on the fact that the SSRS report authoring schema and the SSRS rendering engine supports embedding a limited number of HTML tags, which we can use to execute click-driven JavaScript and control the placement of our Highcharts (or any JS based graphing library) with the SSRS report container.

The two HTML elements used within the SSRS report in the example are the <a> and the <span> tags.

SSRS JavaScript Injection through <a> (Link) Tag

There is an excellent article by Oud-medewerkers that demos how to inject JavaScript into SSRS via an Link tag, the main idea is simple:

  • Add a Textbox element to an SSRS report.
  • In the Value expression of the Textbox, add an HTML Hyperlink element <a> with the href attribute performing a JavaScript function (as shown in the image below). Be sure to escape your code properly.
  • In the Placeholder Properties of the Selected Text inside the TextBox, select the HTML radio button under the Markup Type section.

And that is it really, once you click on the link (in the HTML rendered SSRS report), the JavaScript will be executed.

To get Highcharts on run in SSRS, we need to use the technique above to add a reference to a JS file on the server in the Head section, this JS file will have all the necessary code to create a placeholder for the Highcharts graph, and render it on the SSRS report. The expression in the Value field looks like this:

=" javascript:eval( unescape
('
function addScript(scriptFile) {
var head = document.getElementsByTagName(\'head\')[0];
var script = document.createElement(\'script\');
script.setAttribute(\'type\', \'text/javascript\');
script.setAttribute(\'src\', scriptFile);
head.appendChild(script); }

addScript(\'http://ai-prd-ssrs-1/highchartdemo.js\');
') )
 "
">SHOW HIGHCHART</a>"

You’ll need to replace “http://ai-prd-ssrs-1/highchartdemo.js” in the code above with your local path.

Highcharts SSRS Placeholder using <span> Tag

The <div> tags are used by Highcharts (in the RenderTo:<div-id> parameter) as a placeholder of where the chart should be rendered on the page, but since SSRS does not allow a <div> tag to be rendered, we have to get around the issue by adding a placeholder <span> tag in SSRS as an intermediate placeholder which, using JavaScript, we will find and append the required <div> tag onto.

You can add a <span> tag in SSRS using the same technique described above, the expression in the Value field looks like this:

="<span style=""width: 800px; height: 400px; margin: 0 auto"">SPAN</span>"

JavaScript for Loading Highcharts in SSRS Reports

The JS file is really self explanatory, the main structure is divided into 3 section, the first section is the latest JQuery library code, the second section is the latest Highcharts library code, and the last section is some custom written JavaScript to write the <div> placeholder and load the Highchart graph, I have combined all JS into a single file so everything can be loaded at once, and I wouldn’t need to add code to handle waiting on JS files to be retrieved from the server before executing certain functions.

// JQUERY LIBRARY CODE HERE //

//-----------------//

// HIGHCHARTS LIBRARY CODE HERE //

// START OF HIGHCHARTS CUSTOM DEMO CODE //

//function to generate demo highcharts graph
function show_chart() {
    var chart = new Highcharts.Chart({
        chart: {
            renderTo: 'container' //render to div tag with id container
        },
        xAxis: {
            categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
        },
       
        series: [{
            data: [29.9, 71.5, 106.4, 129.2, 144.0, 176.0, 135.6, 148.5, 216.4, 194.1, 95.6, 54.4]        
        }]
    });
}

//function to add a div tag child to the tag we originally added in SSRS, as a placeholder for the Highcharts graph.
function span_to_div()
{
    var el = document.getElementsByTagName('span');

    for (var nr=0; nr<el.length; nr++)
    {
        if (el[nr].innerHTML == "SPAN"){
            el[nr].innerHTML = "DIV ADDED";
            var div = document.createElement("div");
            div.id = "container";
            div.setAttribute("style","width:600px");
            el[nr].appendChild(div);}
    }
}

//function to add a div tag child to the tag we originally added in SSRS, as a placeholder for the Highcharts graph.
function show_highchart_on_ssrs()
{
    span_to_div();
    show_chart();
}

show_highchart_on_ssrs();

And that is it really! now you can render your Highcharts graphs from within SSRS itself. This concept could obviously be extended, for example I use a demo chart in the JS file, but instead you could make a call to the SSRS Web Service and grab the XML data as input to generate the Highcharts graph. I’ve demoed this technique in the previous article.

* * * * ½ 5 votes
Tags: , , ,
14 replies
  1. Anuj says:

    do you have working demo for this?

    Reply
    • Links Naji says:

      I’ve been meaning to release something, definitely loads of people asked about a working example, just not had the time.

      Watch this space though, I do have 2 weeks off coming up, might just be the incentive I need to get something out there :)

      Reply
      • Anuj says:

        I really cannot follow instructions here. can you email me your code please I thanks you a bunch. I really need this.

        Reply
  2. Jeff Allen says:

    Great article… I was getting the impression it wasn’t possible to use JavaScript to this extent in SSRS (aside from some basic pop-up/window navigation stuff). What I haven’t been able to envision yet is how – instead of using the static dataset embedded in your show_chart function – to use an SSRS dataset to retrieve a recordset from SQL Server. Any thoughts?

    Regards,
    Jeff

    Reply
    • Links Naji says:

      Thanks Jeff, really once you turn this into a “web development” problem, rather than staying confined within the SSRS framework, there are so many awesome ways you could take this concept further, on one project I ended up adding a place-holder link on each row of a table, when the user hovers over the link, a box will appear with a few Highcharts visualization elements embedded within it, which provides further insight on the data in that particular row (sort of an on-hover data drill-through). Another time I ended up overlaying the default SSRS column chart type, whenever they appeared in any report, with a Highcarts interactive column charts housing the same underlying data (by reading it from the HTML page via JS).

      Anyway to answer your question, you can use JQuery to make an AJAX call to the SSRS report (that retrieves the SQL Server data), with the SSRS URL parameter to export the report in ATOM format, then you can use the result set to build your chart, so essentially the show_chart function will contain an AJAX call similar to this:

      var $graphUrl = getURLParameter(‘graph’);
      $.get(‘HTTP://[report-server-domain]/ReportServer?[Report-Name]&[Report Parameters]&rs:ParameterLanguage=&rs:Command=Render&rs:Format=ATOM&rc:DataFeed=xAx0x0xCx0′, function(xml) {

      // Split the lines
      var $xml = $(xml);

      //process $xml data into the Highcharts axis

      Obviously you need to replace anything between [] with your own server/ssrs details (which you can find by hitting that SSRS report via Report Manager, and looking at the URL), and thats it really!

      You should take a look at the JS code in the Convert SSRS Charts to Highcharts (Dynamic) Graphs article, the code is pretty self explanatory, it initializes a chart, grabs the data from an ATOM SSRS export, and pushes it into the chart’s axis… simples!

      Thanks for your comment!

      Reply
  3. Anuj says:

    Dude it has been long provide the solution please. I have waited long time

    Reply
    • Links Naji says:

      Hi Anuj,

      I am really sorry man, but this is no longer a project that I am pursuing, and it is very unlikely that I will be able to get a demo running and posted. My involvement with SSRS is currently minimal, as I have started to focus much more on predictive analysis and Big Data technologies.

      If you are really keen on someone providing an example, or a clearer explanation, of the methods presented in this article, then I am sure someone at the Microsoft SSRS Technet forums will be able to lend you a hand.

      Again, I am very sorry for this.

      Cheers,

      Reply
  4. Antoon says:

    Great stuff,

    I was wondering why you chose to inject the javascript via a link (meaning that you have to click a button before the functionality becomes available), and didn’t add it to the ReportingServices.js file?

    Isn’t that an option or am I missing something?

    thx
    Antoon

    Reply
    • Links Naji says:

      Hi Antoon,

      Thanks!
      You are absolutely correct, there is no reason why you can’t make the chart show up on document load by injecting some code into the ReportingServices.js file, the only reason I didn’t do that here is because the requirements I had was for a click-based interaction.

      Cheers,

      Reply
  5. Krishna says:

    Great article, I playing with d3js and SSRS for quite some time, this demo is great tho. Thinking SSRS as a web platform opens up whole new dimension!

    Reply

Trackbacks & Pingbacks

  1. [...] article is a part 1 of 2, in the next article I give a working example of how to inject and display a Highcharts graphs right into an SSRS report, allowing you [...]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>