none
Unable to create report. Query produced too many results

    Question

  • Hi All,

    Does someone knows how to avoid the message "Unable to create report. Query produced too many results" in Grid Report Type in PerformancePoint 2010. When the mdx query returns large amount of data, this message appears. Is there a way to get all the large amount in the grid anyway?

    I have set the data Source query time-out under Central Administration - Manager Service applications - PerformancePoint Service Application - PerformancePoint Service Application Settings at 3600 seconds.

    Here Event Viewer log error at the server:

    1. An exception occurred while running a report.  The following details may help you to diagnose the problem:
    Error Message: Unable to create report. Query produced too many results.
            <br>
            <br>
            Contact the administrator for more details.
    Dashboard Name:
    Dashboard Item name:
    Report Location: {3592a959-7c50-0d1d-9185-361d2bd5428b}
    Request Duration: 6,220.93 ms
    User: INTRANET\spsdshadmin
    Parameters:
       
    Exception Message: Unable to create report. Query produced too many results.
    Inner Exception Message:
    Stack Trace:    at Microsoft.PerformancePoint.Scorecards.Server.PmServer.ExecuteAnalyticReportWithParameters(RepositoryLocation analyticReportViewLocation, BIDataContainer biDataContainer)
       at Microsoft.PerformancePoint.Analytics.ServerRendering.OLAPBase.OlapViewBaseControl.ExtractReportViewData()
       at Microsoft.PerformancePoint.Analytics.ServerRendering.OLAPBase.OlapViewBaseControl.CreateRenderedView(StringBuilder sd)
       at Microsoft.PerformancePoint.Scorecards.ServerRendering.NavigableControl.RenderControl(HtmlTextWriter writer)
    PerformancePoint Services error code 20604.

    2. Unable to create report. Query produced too many results.

    Microsoft.PerformancePoint.Scorecards.BpmException: Unable to create report. Query produced too many results.
       at Microsoft.PerformancePoint.Scorecards.Server.Analytics.AnalyticQueryManager.ExecuteReport(AnalyticReportState reportState, DataSource dataSource)
       at Microsoft.PerformancePoint.Scorecards.Server.PmServer.ExecuteAnalyticReportBase(RepositoryLocation analyticReportViewLocation, BIDataContainer biDataContainer, String formattingDimensionName)
       at Microsoft.PerformancePoint.Scorecards.Server.PmServer.ExecuteAnalyticReportWithParameters(RepositoryLocation analyticReportViewLocation, BIDataContainer biDataContainer)
    PerformancePoint Services error code 20605.

    Thanks in advance for your help.

    Friday, November 19, 2010 7:02 AM

Answers

  • Hello,

    Another thing you can try is to increase the AnalyticQueryCellMax value

     

    Set-SPPerformancePointServiceApplication -identity "PerformancePoint Service Application" -AnalyticQueryCellMax 10000000

     

    Here is more information around using PerformancePoint Services cmdlets:  http://technet.microsoft.com/en-us/library/ee906547.aspx

    Thanks
    Heidi Tr - MSFT


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, May 19, 2011 12:12 PM
    Moderator

All replies

  • Hello,
    I would like you to try the following to adjust your readerquotas.

    Change the values of the parameters listed below to a larger value. We recommend that you double the value and then run the query to check whether the issue is resolved. To do this, follow these steps:

    1. On the SharePoint 2010 server, open the Web.config file. The file is located in the following folder:
      \Program Files\Microsoft Office Servers\14.0\Web Services\PpsMonitoringServer\
    2. Locate and change the the below values from 8192 to 16384.
    3. Open the Client.config file. The file is located in the following folder:
      \Program Files\Microsoft Office Servers\14.0\WebClients\PpsMonitoringServer\
    4. Locate and change the below values from 8192 to 16384.
    5. After you have made the changes, restart Internet Information Services (IIS) on the SharePoint 2010 server.

     

    <readerQuotas

                  maxStringContentLength="2147483647"

                  maxNameTableCharCount="2147483647"

                  maxBytesPerRead="2147483647"

                  maxArrayLength="2147483647"

                  maxDepth="2147483647" />


    Thanks
    Heidi Tr - MSFT

     


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, November 19, 2010 8:36 PM
    Moderator
  • Hello,

    I noticed you were getting an error code 20604, which is tied to ADOMD.NET.  I would suggest installing AMO package on the machine running SharePoint. Be mindful of your SQL version and platform though.  You can get the package for SQL server 2008 SP1 from the SQL server 2008 feature pack from april 2009 available here http://www.microsoft.com/downloads/details.aspx?FamilyID=b33d2c78-1059-4ce2-b80d-2343c099bcb4&displaylang=en

    THanks
    Heidi Tr - MSFT

     


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, November 19, 2010 8:42 PM
    Moderator
  • Hello Heidi,

    Thank you for your response, I have tried your solution above, after restart the IIS and I try to browse large data on my Cube using PerformancePoint, the error still appear :(

    I also have installed AMO package you suggested, after install the AMO package, the error still show, is there any other solution for this problem?

    FYI, I use SQL Server 2008 R2 and SharePoint 2010

     

    Thanks,

    Denny S.P.

    Monday, November 22, 2010 2:31 AM
  • Hello,

    How long does the query take to run in SSAS?  How large is the amount of data that you are trying to pull into the Analytical Grid?  Have you tested narrowing down the scope of the MDX query to see if you are able to retrieve any data in the report.

    I am looking to narrow down the threshhold on when you see the error.

    Thanks

    Heidi Tr - MSFT

     


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, November 23, 2010 4:55 PM
    Moderator
  • I am having the same problem guys, its really annoying. PerformancePoint 2007 does not have this issue. Any Suggestion or maybe some hotfix availiabel for this issue??.

    Thanks In Advance.

    Wednesday, May 18, 2011 5:44 AM
  • Hello,

    Another thing you can try is to increase the AnalyticQueryCellMax value

     

    Set-SPPerformancePointServiceApplication -identity "PerformancePoint Service Application" -AnalyticQueryCellMax 10000000

     

    Here is more information around using PerformancePoint Services cmdlets:  http://technet.microsoft.com/en-us/library/ee906547.aspx

    Thanks
    Heidi Tr - MSFT


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, May 19, 2011 12:12 PM
    Moderator
  • Hello Heidi,

    I have tried all of your suggestions in this blog but am still experiencing this same issue that others have reported. Is there anything else I can try?

    Thanks,
    Kelly

    Monday, July 21, 2014 1:56 PM