locked
SSRS report does not display when a filter is added RRS feed

  • Question

  • I have 3 filters currently connected to an SSRS report running in Integrated mode (SSRS 2008 R2 and SharePoint 2010 SP2).

    If I add one more multi-select filter, and make a connection to the SSRS report, the SSRS report does not display at all.  Pls note that it is not that the report has no rows selected, it simply does not display.   Even if I completely delete the SSRS report at this point, the dashboard does not display an error message -- again it is as if the report is not a part of the dashboard at all, it is ignored/not displayed.

    I have checked and rechecked what parameter values are being passed in, the report works as expected if run outside of the PerformancePoint dashboard.

    Any thoughts/comments appreciated.  Thanks.

    • Moved by tracycai Tuesday, December 3, 2013 9:12 AM
    Monday, December 2, 2013 12:22 PM

Answers

  • Dimitri,

    Thanks for the response.  I'm on SQL Server 2008 R2 SP2 and SharePoint 2010 SP2.

    I'm now 99.99% sure that this is a memory/limits problem within either PerformancePoint or SharePoint.  

    I can now get the report in the dashboard to display IF I only select about half of the items in the new filter -- and it does not matter which half I select.  At a certain point, if the number of filter items gets too high, the report appears to refresh (very quick, no green circle) but the numbers in the report do not actually change.  And if you go off of the dashboard page and go back in, the report does not render.  Similarly if you select all of the items in the filter and hit apply, the SSRS report does not render in the PerformancePoint dashboard.

    I have now tested this using a PerformancePoint filter going against a SQL Server table and a PerformancePoint filter using a SQL Server Analysis Services dimension -- same result in either case.  The filters work if:  1) make it a single value selection (list tree for example) or 2) make it a multi value selection but when you are actually in the dashboard just select a few number of items.  The filters do not work if:  you hit select all and pick all the items -- the report either quickly renders (but does not actually change based on what is selected) and if you click off the page and go back on, it does not render at all.  This proves in my judgment that it is a PerformancePoint / SharePoint / IIS issue.  My next step is to open a support ticket.

    I have 8 pages in the dashboard, 3 or 4 filters on each page, and one of the filters is large -- over 1,000 items to pick from.  I'll assert that the combination of pages X filters X number of items in the filters eventually breaks it.

    On a separate SSRS thread I did run in to a similar limit outside of a PerformancePoint dashboard involving an SSRS report running in integrated mode, fixed it by following the thread's answer and editing the SharePoint web.config file.  It would be great if there was a similar web.config entry to get around what seems to be a similar memory/limit problem in PerformancePoint.

    --Bob



    • Marked as answer by Alisa Tang Thursday, December 12, 2013 9:28 AM
    • Edited by Bob Harford Friday, December 20, 2013 3:23 AM
    Wednesday, December 4, 2013 2:05 AM
  • Hi Bob, this thread might be helpful: http://blogs.msdn.com/b/performancepoint/archive/2008/01/17/changing-the-limit-on-the-number-of-items-returned-in-a-filter.aspx

    It's also worth looking at SharePoint's limits; PerformancePoint has a limit of 1 million cells: http://technet.microsoft.com/en-us/library/cc262787.aspx#PPS


    Dimitri Ayrapetov (MCSE: SharePoint)

    • Proposed as answer by Alisa Tang Wednesday, December 4, 2013 10:48 AM
    • Marked as answer by Alisa Tang Thursday, December 12, 2013 9:28 AM
    Wednesday, December 4, 2013 2:14 AM

All replies

  • Hi Bob, that sounds like a strange bug. Are you seeing anything reported in the ULS or SQL logs? Are you running a recent patch level for SQL and SharePoint?

    Dimitri Ayrapetov (MCSE: SharePoint)

    Tuesday, December 3, 2013 2:36 AM
  • Hi Bob,

    This issue is related to SSRS. For quick and accurate answers to your questions, I will move it to SQL Server Reporting Services, Power View forum.

    Thanks.


    Tracy Cai
    TechNet Community Support

    Tuesday, December 3, 2013 9:11 AM
  • Tracy,

    It is not related to SQL Server Reporting Services in my judgment, it is related to PerformancePoint -- the filters that I am referring to are PerformancePoint filters.  Again the SSRS report runs as expected outside of PerformancePoint.

    --Bob

    Tuesday, December 3, 2013 10:47 AM
  • Hi Bob, when you say that the report runs as expected outside of PerformancePoint, are you referring to running it in a SSRS Viewer Web Part, or executing it within Business Intelligence Studio / Report Builder?

    Dimitri Ayrapetov (MCSE: SharePoint)

    Tuesday, December 3, 2013 12:44 PM
  • The SSRS report runs as expected when run directly in a SharePoint library. 

    In other words then, the PerformancePoint dashboard has filters and report items that point to SSRS reports sitting in a SharePoint library.

    In short, I believe there can be bizarre things that happen with PerformancePoint filters.  Here is my experiece:  you cannot have a numeric field as part of a PerformancePoint filter; periodically a filter will throw something like (you are not permitted/authorized to view this web part); and now this, the report simply doesn't display at all.

    I'm struggling with this particular PerformancePoint problem, my guess is that PerformancePoint has hit a limit on the number of pages/filters that can be displayed in a dashboard.  Or possibly the text description in the filter is too long.

    Tuesday, December 3, 2013 2:17 PM
  • Hi Bob, I think you're right that it's a SP problem and not an SSRS problem. The only potential cause that I could find was related to the data types not matching between the PerformancePoint Parameter and the SSRS parameter.

    If you are running the latest patch of SQL and SharePoint, I would recommend opening a ticket with Microsoft.


    Dimitri Ayrapetov (MCSE: SharePoint)

    Wednesday, December 4, 2013 1:37 AM
  • Dimitri,

    Thanks for the response.  I'm on SQL Server 2008 R2 SP2 and SharePoint 2010 SP2.

    I'm now 99.99% sure that this is a memory/limits problem within either PerformancePoint or SharePoint.  

    I can now get the report in the dashboard to display IF I only select about half of the items in the new filter -- and it does not matter which half I select.  At a certain point, if the number of filter items gets too high, the report appears to refresh (very quick, no green circle) but the numbers in the report do not actually change.  And if you go off of the dashboard page and go back in, the report does not render.  Similarly if you select all of the items in the filter and hit apply, the SSRS report does not render in the PerformancePoint dashboard.

    I have now tested this using a PerformancePoint filter going against a SQL Server table and a PerformancePoint filter using a SQL Server Analysis Services dimension -- same result in either case.  The filters work if:  1) make it a single value selection (list tree for example) or 2) make it a multi value selection but when you are actually in the dashboard just select a few number of items.  The filters do not work if:  you hit select all and pick all the items -- the report either quickly renders (but does not actually change based on what is selected) and if you click off the page and go back on, it does not render at all.  This proves in my judgment that it is a PerformancePoint / SharePoint / IIS issue.  My next step is to open a support ticket.

    I have 8 pages in the dashboard, 3 or 4 filters on each page, and one of the filters is large -- over 1,000 items to pick from.  I'll assert that the combination of pages X filters X number of items in the filters eventually breaks it.

    On a separate SSRS thread I did run in to a similar limit outside of a PerformancePoint dashboard involving an SSRS report running in integrated mode, fixed it by following the thread's answer and editing the SharePoint web.config file.  It would be great if there was a similar web.config entry to get around what seems to be a similar memory/limit problem in PerformancePoint.

    --Bob



    • Marked as answer by Alisa Tang Thursday, December 12, 2013 9:28 AM
    • Edited by Bob Harford Friday, December 20, 2013 3:23 AM
    Wednesday, December 4, 2013 2:05 AM
  • Hi Bob, this thread might be helpful: http://blogs.msdn.com/b/performancepoint/archive/2008/01/17/changing-the-limit-on-the-number-of-items-returned-in-a-filter.aspx

    It's also worth looking at SharePoint's limits; PerformancePoint has a limit of 1 million cells: http://technet.microsoft.com/en-us/library/cc262787.aspx#PPS


    Dimitri Ayrapetov (MCSE: SharePoint)

    • Proposed as answer by Alisa Tang Wednesday, December 4, 2013 10:48 AM
    • Marked as answer by Alisa Tang Thursday, December 12, 2013 9:28 AM
    Wednesday, December 4, 2013 2:14 AM
  • Hi Bob,

    Please click “Preview” icon in the Report Editor dialog box. To check if you can get the correct report. Based on my search, “Show Toolbar” option may be affect parameters/filters. Did you get some error message when you deploy to SharePoint?

    There is a similar thread, please refer to it.
    http://social.technet.microsoft.com/Forums/en-US/cd743608-5367-4c16-8f9c-dd1cb8736417/embed-ssrs-report-into-dashboard?forum=ppsmonitoringandanalytics

    Hope this helps.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Wednesday, December 4, 2013 10:49 AM
  • I opened up a support ticket with the Microsoft PerformancePoint team, and this thread probably refers to a known issue, and it was caused via a regression error (in effect a recent CU / SP broke it).

    The issue now is that there is about a ~2k character limit on the URL that is constructed via the interaction of the PerformancePoint filter and the connected SSRS report.  If you have 500 items in one of your filters, and each item has 6 or 7 characters, and if you hit "select all visible" enough, that ~2k character limit will be exceeded, and the report either won't render or the filter items will not be incorporated.

    As I understand it, a CU fix may be out in February 2014


    • Edited by Bob Harford Monday, December 23, 2013 1:53 PM typos
    Friday, December 20, 2013 10:09 PM
  • Bob, thanks for following up. Just to clarify, do you mean the Feb 2014 CU?

    Dimitri Ayrapetov (MCSE: SharePoint)

    Saturday, December 21, 2013 6:50 PM
  • Dimitri,

    Yup, that was a typo:  as of now CU expected for this fix in February 2014

    Monday, December 23, 2013 1:54 PM
  • I have this exact issue. The combination is SSAS, SSRS 2012 Integrated, SharePoint 2010 and PerformancePoint 2010. In my case there are 6 filters with no more than about 20 members each. Once you select on a certain number of members the report either disappears or does not refresh. Running a SQL Server Profiler shows that when the number of filter members is reached the SSRS report is not executed and thereby no SSAS query execution. I checked the Feb 2014 SharePoint update and didn't see anywhere this was addressed. Did it work in your case?
    Thursday, April 2, 2015 9:24 PM
  • No, not that I can recall
    Thursday, April 2, 2015 10:16 PM