Convert SSRS Charts to Highcharts (Dynamic) Graphs

21 Jan
January 21, 2012

SSRS is an awesome framework as part of a whole BI solution for report authoring, delivery and interaction. One problem though is that the reports produced through Visual Studio or Report Builder are too static, there is no hover-over effect in SSRS reports, and you cannot do dynamic filtering once the report finished rendering.

This posts goes through building a converter that takes the ATOM output of an SSRS chart (1 y-axis Line Graph), and returns a dynamic Highcharts graph the represent the same data points.

This 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.

It is important to remember that SQL Server 2012 (project name: Denali) has the ability to generate dynamic charts (delivered through Silverlight) using its latest report authoring framework which is being called PowerView (or project name: Crescent). If you not had the chance to play about with SQL Server’s new BISM Analysis Model, PowerPivot for EXCEL, and PowerView then you are really missing out on some very cool graphing features.

Now to use the ATOM output of SSRS, you will need SQL Server 2008 R2, which has the option to render the report into an SSRS extended ATOM format. You don’t need to install Highcharts or JQuery to run the sample in this post, since the JS for both can be referenced across the web, but if you are releasing this onto your production environment you will need to download each JS, as there is no guarantee the online reference will remain valid.

First you will need to build your 1 y-axis Line Graph through SSRS, as you would normally either through VS or Report Builder. Once the report is deployed onto SSRS Manager, you’ll need to figure out how to export the data from this report using SSRS REST Web Service.

Try running a sample of the Line Graph you generated, you should be able to see a button to export to ATOM (shown below), upon clicking that, you should get an XML file that contains the URL you’ll need to call to get the actual ATOM data.

This URL should have a similar structure to:
HTTP://<report-server-domain>/ReportServer?<Report-Name>&<Report Parameters…>&rs:ParameterLanguage=&amp;rs:Command=Render&amp;rs:Format=ATOM&amp;rc:DataFeed=xAx0x0xCx0

Hitting the URL above should give you back a bunch of ATOM entry tags that contain your data points for your graph, this XML will look something like this:

<entry>
        <id>uuid:278e144e-1ca4-4398-9dcf-3f03dc02be49;id=2</id>
        <title type="text"/>
        <updated>2012-01-20T15:55:43Z</updated>
        <author/>
        <content type="application/xml">
            <m:properties>
            <!-- Start of Report Paramters -->
                <d:StartDate m:type="Edm.DateTime" m:null="true"/>
                <d:EndDate m:type="Edm.DateTime" m:null="true"/>
                <d:StartDateMacro>Start of Last Month</d:StartDateMacro>
                <d:EndDateMacro>Today</d:EndDateMacro>
                <d:Report>BusinessReport_Line</d:Report>
                <d:Account m:type="Edm.Int32">-1</d:Account>
                <d:GroupBy>Daily</d:GroupBy>
                <d:LCID>en-GB</d:LCID>
                <d:CustomerID m:type="Edm.Int32">4</d:CustomerID>
                <d:CampaignID m:type="Edm.Int32">-1</d:CampaignID>
                <d:MappedColumn>Metric</d:MappedColumn>
                <d:FirstDayOfWeek>0</d:FirstDayOfWeek>
                <d:ClickType>ppc</d:ClickType>
            <!-- End of Report Paramters -->
            <!-- Start of Graph Data-->
                <d:Provider_x0020_Name_label>Provider Name</d:Provider_x0020_Name_label>
                <d:Provider_x0020_Name_grp_A_label>Dec  1 2011 </d:Provider_x0020_Name_grp_A_label>
                <d:Provider_x0020_Name_grp_A_Value_Y m:type="Edm.Double">8.95</d:Provider_x0020_Name_grp_A_Value_Y>
            <!-- End of Graph Data-->            
            </m:properties>
        </content>
    </entry>

Each entry will have all the parameters under which the requested report ran, as well as the data points for that report. Some might say this is criminally inefficient and they should put all the report parameters at the top of the XML, instead of repeating them thousands of times throughout the XML doc, Microsoft says its cool.

You can see in the SSRS URL above that using REST, we can control the rendering format (ATOM in the sample URL above) as well as all the parameters being supplied to our SSRS report, which means you can extend this functionality to your Highcharts renderer, allowing it to dynamically change report parameters as per the user’s request.

Then all you need is a bit of clever JQuery that calls the SSRS report (with the correct parameters), takes the ATOM results, and convert that into a Highcharts graph, here is a sample page that takes in the ATOM feed (using the JQuery GET command), looping over each entry and processing the SSRS ATOM data points, while building an equivalent Highchart data point.

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <title>SSRS to Highcharts Line Graph Example</title>
        <!-- 1. Add these JavaScript inclusions in the head of your page -->
        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
        <script type="text/javascript" src="http://www.highcharts.com/js/highcharts.js"></script>
        <!-- 2. Add the JavaScript to initialize the chart on document ready -->
        <script type="text/javascript">
        $(document).ready(function() {

            var options = {
                chart: {
                    renderTo: 'container',
                    type: 'line'
                },
                title: {
                    text: 'Example Chart'
                },
                xAxis: {
                    categories: []
                },
                yAxis: {
                    title: {
                        text: 'Units'
                    }
                },
                series: []
            };

            // Load the data from the XML file
            var $graphUrl = getURLParameter('graph');
            $.get('http://live-ssrs-1/ReportServer?' + decodeURIComponent($graphUrl), function(xml) {
               
                // Split the lines
                var $xml = $(xml);

                // push categories
                $xml.find('Provider_x0020_Name_grp_A_label').each(function(i, category) {

                    options.xAxis.categories.push($(category).text());

                });

                // push series
                $xml.find('feed').each(function(i, series) {

                    var seriesOptions = {

                        name: 'Metric',

                        data: []

                    };
                   
                    // push data points
                    $(series).find('Provider_x0020_Name_grp_A_Value_Y').each(function(i, point) {

                        seriesOptions.data.push(

                            parseInt($(point).text())

                        );

                    });
                   
                    // add it to the options
                    options.series.push(seriesOptions);
                });

                var chart = new Highcharts.Chart(options);

            });

        });
       
        function getURLParameter(name) {
        return decodeURI(
            (RegExp(name + '=' + '(.+?)(&|$)').exec(location.search)||[,null])[1]
           );
        }
       
        </script>
    </head>
    <body>
        <!-- 3. Add the container -->
        <div id="container" style="width: 800px; height: 400px; margin: 0 auto"></div>
    </body>
</html>

Simple as that!

You need to make sure that you pass in the full SSRS parameters in the “graph” query string, which is then being consumed by the JQuery JavaScript and appended onto the SSRS server URL (which will need to be edited to your local SSRS server).

It is important to note that is will only work if SSRS and the HTML page are on the same domain, this is because you cannot make Cross Domain requests through JavaScript. If you need to deploy your HTML code on a separate domain, you might have to consume the XML using the code behind and passing it to JQuery that way, or create a simple PHP/ASP.NET “proxy” service on your 2nd domain that essentially acts as a local resource to the HTML domain.

Now you can jazz up your SSRS graphs with fancy dynamic Highchart ones, you can also pretty much use any JS graphing library to do this, I like Highcharts because of the cool hover-over affect.

Take a look at embedding Highcharts into SSRS to further extend this concept and display your JavaScript graphing libraries within SSRS itself.

* * * * ½ 39 votes
Tags: , , ,
15 replies
  1. E says:

    Hello,

    This looks great, but I’m stuck on this step:
    “Hitting the URL above should give you back a bunch of ATOM entry tags that contain your data points for your graph,”

    When I hit the URL, I get the report.

    Thanks

    Reply
    • admin says:

      Hey E,

      This is probably because you have a different rc:DataFeed ID or you got one of the parameters wrong (essentially one of your query string parameters doesn’t sit right).

      When you get the report page, I recommend hitting the “Export to ATOM RSS” button, which is shown in the image above, to get all the ATOM feeds available for your report (you could have multiple depending on how many elements you have in your report).

      For a more thorough explanation of how SSRS generates Data Feeds, I recommend reading this DataFeed MSDN article. Definitely a must read if you are serious about building extensions ontop of SSRS’s Data Feed service.

      Let me know how you get on!

      L

      Reply
  2. A says:

    Hey, in this section:
    var $graphUrl = getURLParameter(‘graph’);
    $.get(‘http://live-ssrs-1/ReportServer?’ + decodeURIComponent($graphUrl), function(xml) {

    can you provide and example for full url and parameters?
    i have tried to put:
    var $graphUrl = getURLParameter(‘Chart1′);
    $.get(‘http://sqlsrv:8080/ReportServer?%2FSale&rs%3ACommand=Render&rs%3AFormat=ATOM&rc%3ADataFeed=xAx0x0xCx0′ + decodeURIComponent($graphUrl),

    but it doesn’t work

    Reply
    • admin says:

      Hi Alex,

      An example URL for me (on my SSRS instance) would be:

      http://live-ssrs-1/ReportServer?/Business Reports/BusinessReport_Line&StartDate:isnull=True&EndDate:isnull=True&StartDateMacro=Start of Last Month&EndDateMacro=Today&Report=BusinessReport_Line&Account=-1&GroupBy=Daily&LCID=en-GB&CustomerID=4&CampaignID=-1&MappedColumn=Metric&FirstDayOfWeek=0&ClickType=ppc&rs:ParameterLanguage=&rs:Command=Render&rs:Format=ATOM&rc:DataFeed=xAx0x0xCx0

      I’ve highlighted in bold the “Parameters” bit of the URL

      The best thing to do to figure out your URL structure is:

      • Run your SSRS report in Report Manager.
      • Once the report completed running, click on the ATOM feed service icon (shown in the image above); this should return an ATOM (XML) document that contains an HTML encoded URL version of the URL (with the parameters you ran the SSRS report under)
      • Use an HTML decoder to decode the URL, you should be able to paste that (decoded) URL in an address bar and have it return an XML file for you. If this isn’t happening then you don’t have the correct URL (and SSRS Report Manager should throw an error).

      If you follow these steps you should be able to figure out your URL, if not you should get an error message which if you paste here I can help you diagnose the issue.

      Cheers Alex,
      />L

      Reply
  3. Janusz says:

    Could you post a full working example please? I presume I can’t run this from my desktop despite the report being on the same NT domain?

    Reply
    • admin says:

      Hi Janusz,

      Sure I can! I’ve been actually trying to find some time to finalize this for a while now. It wont be today or tomorrow, but soon, and hopefully very soon :)

      In the mean-time I am happy to give you a hand with any specific setup you have, and walk you step-by-step through the process. You are absolutely correct; the code in this page in its current state cannot be ported seamlessly into your (or any) environment

      Cheers
      L

      Reply
      • Janusz says:

        Great stuff – I actually have a lot more to ask but don’t think it’s in the scope of this

        I am basically wanting to create a dashboard for SRSS reports, with each report having parameters

        Reply
        • admin says:

          I see.

          You could actually do all this natively within SSRS (i.e. without Highcharts), via communicating with the SSRS Web Service, which will give you an HTML rendered report that you can just plonk on your page, then all you need to do is add your web controls (which will control the parameters). I’ve got the beginnings of an SSRS Web Service client library to get you started, altho its currently very simple and can only be used as an introduction to the web service.

          This is even easier if you’re using asp.net and can use the ReportViewer control, which will manage the communication with the SSRS webservice on your behalf.

          That being said, I do really hate how static and stale SSRS reports look, hence the article above.

          If you want to break out of the “SSRS chrome” mold, and choose to build your paramterized dashboard using something like Highcharts, then my recommendation is to build a sturdy JQuery (JS) renderer, that takes generic SSRS ATOM and build a graph (based on some configurations), similar to the example above.

          Options options options :)

          Reply
          • Janusz says:

            Thanks again!

            If I grab the SSRS ATOM data and the charts displays nicely, what if I want to change the parameters? How would that work? Would we even need SSRS at this point, could we not just run the SQL on the page itself?

          • admin says:

            You could edit the parameters by editing the URL you are using to grab the ATOM data from the SSRS Web Service.

            There is absolutely no reason why you can’t directly connect to SQL, but I like the SSRS approach for many reasons, including:

            • SSRS allows multiple render formats (PDF, IMG, EXCEL, WORD, etc.), which means that although I can display a Highchart graph using the ATOM feed, I could also allow the user to export the graph’s data in the most convenient format for them, without having to write any further code.
            • You can use SSRS Caching and Snapshot features to speed up report data retrieval and processing
            • Subscriptions can be transparently done on the SSRS layer, without creating your own custom logic.

            As well as many other reasons :)

  4. shiladitya says:

    Hi,

    As you said you are working on a complete example, it will be ver nice and helpfull if you could provide the example.

    Reply
  5. christian says:

    could put the link to download the project please

    Reply
  6. SG says:

    You are true life saver.
    I have been trying to create column chart based on similar xml outcome and using this post and just changing the field name I got first look at my column chart. However could you please advice how I can change aboce script to group similar values from all ‘entry’ tag and create http://jsfiddle.net/sgandewar/wnb2N/ chart like this.
    Here is part of xml for reference.

      <?xml version="1.0" encoding="utf-8" ?>
    - <feed>
     - <entry m:etag=""40"">
      <content type="application/xml">
    - <m:properties>
      <d:ImpactNumber m:type="Edm.Double">1</d:ImpactNumber>
      <d:Severity>Low</d:Severity>
      <d:ScanPeriod>Dec-2013</d:ScanPeriod>
      </m:properties>
      </content>
      </entry>
    - <entry m:etag=""20"">
      <content type="application/xml">
    - <m:properties>
      <d:ImpactNumber m:type="Edm.Double">2</d:ImpactNumber>
      <d:Severity>Medium</d:Severity>
      <d:ScanPeriod>Dec-2013</d:ScanPeriod>
      </m:properties>
      </content>
      </entry>
    + <entry m:etag=""10"">
      <content type="application/xml">
    - <m:properties>
      <d:ImpactNumber m:type="Edm.Double">1</d:ImpactNumber>
      <d:Severity>Low</d:Severity>
      <d:ScanPeriod>Dec-2013</d:ScanPeriod>
      </m:properties>
      </content>
      </entry>
    Reply

Trackbacks & Pingbacks

  1. […] 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 […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply to admin Cancel 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>