Pages

Monday, July 2, 2012

SSRS Chart Issues in SharePoint Integrated Mode

Have you ever faced a problem that you had almost given hopes on, and then pushed yourself to that one last hopeless try which succeeded? Well, it happens a lot with me, I just seem to be very lucky in such scenarios. I have always felt that men try to over-romanticize the idea of discovering something by accident (remember the Nobel laureate Albert Szent-Györgyi saying – “A discovery is said to be an accident meeting a prepared mind”?). I don’t know how prepared I was but it sure was an accident that made this discovery. I had been dabbling with some SSRS reports in the SharePoint Integrated mode and was facing a lot of issues with the inline charts that I was using. This post will talk about the issues that I encountered as well as the solution which I discovered accidentally.

SSRS Chart Issues in SharePoint Integrated Mode

To give an idea of the environment which had the issues, I was using SQL Server 2012 Reporting Services in integrated mode with SharePoint 2010. To recreate the issues, follow the steps below:-

1) Create a simple report with some charts embedded within a table or a matrix. I have created a data bar, bar chart, bullet chart as well as a line chart as an example.

SSRS Report with inline charts

2) Implement some actions as well as tooltips on the charts. As an example, I have implemented an action on the first databar to go to http://www.bing.com and tooltip expressions on all of the 4 charts. You can also implement actions and tooltips on some of the regular tablix columns without the charts. Preview it and make sure the tooltips as well as the chart actions are working.

Preview the SSRS Report

3) Now deploy this report in Sharepoint and preview the report.

Issues with the SSRS Chart when previewed in SharePoint

Even though it is not that evident from the image, there were quite some issues that I faced with this report and I am listing them below:-
     a) The actions that were defined on the frist bar chart was not working for some bars.
     b) The tooltips defined for the second bar chart was not working.
     c) The tooltips defined for the bullet chart was not working.
     d) The tooltips defined for the line chart was not working. In addition to that, the lines appeared to be jaggy and seemed to have some pixels cut off from them. Click and expand the image above to see jagged effect on the line chart.
Also, the report seemed to take more time in displaying the charts in SharePoint than when I had previewed it in the SSDT environment. It is to be noted that the actions as well as the tooltips in the tablix columns without the charts worked fine.

4) Now to solve the issue, all you have to do is to enclose each chart in a rectangle before using it in the cell of the tablix.

How to enclose chart within rectangle in SSRS

5) Once you have done the above step for every chart, save and deploy the report to SharePoint. You can preview the report and see that now the tooltips as well as the actions are working for the charts. Also, the line charts seemed to be smooth and devoid of the jagged line effects seen earlier. Performance also seems to be much better than before.

SSRS Report when previewed in SP

Moral of the story – If you are using inline charts in SSRS SharePoint Integrated mode and facing similar issues, then try to use the charts within a rectangle. This might save you some headaches later.

6 comments:

  1. Hi Jason, Thank you for the excellent post. I have been struggling with SSRS reports in SP-integrated mode as well, but I have the exact opposite problem. SSRS reports with graphs are running with acceptable performance, but all of my reports with a data grid (i.e. text only) are super slow. It's only in SP that it runs slow - in Native SSRS viewer it runs fast. Any ideas?

    ReplyDelete
  2. Hi Noah,
    Which version of SSRS and SP are you using? Also, what is the SQL Server version for SP database?

    ReplyDelete
  3. Jason bhai, you are my champ.

    ReplyDelete
  4. Hi Jason, We're using SP 2010 based off SQL Server 2008 R2 Standard, and SSRS 2008 R2 Standard.

    Thanks,
    Noah

    ReplyDelete
    Replies
    1. I know that there was an issue with SP2010 based off SQL Server 2008 R2 when you are embedding indicators/images/charts within your grids (or tablix). But I am not able to replicate the performance issues with just a tablix and some dataset fields. How many pages does the report span, so that I can try to reproduce it in my environment? Also, how many rows and columns does it have? Can you make sure that the latest service packs / updates are also applied?

      Delete
    2. http://connect.microsoft.com/SQLServer/feedback/details/781611/ssrs-2008-r2-databar-and-sparline-slow-performance

      Delete