Unable to create report. Query produced too many results

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.

November 19th, 2010 10:02am

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

 

Free Windows Admin Tool Kit Click here and download it now
November 19th, 2010 11:36pm

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

 

November 19th, 2010 11:42pm

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.

Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2010 5:31am

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

 

November 23rd, 2010 7:55pm

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.

Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 8:44am

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

May 19th, 2011 3:12pm

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

Free Windows Admin Tool Kit Click here and download it now
July 21st, 2014 4:56pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics