Tip #629: If your CRM report comes back empty

We observed this behavior on CRM 2013 SP1 and and CRM 2015 Update 0.2 on-premises. The report runs fine for small ranges (e.g. dates) but, as the range (and number of records to be processed) increases, report comes back blank, as if it didn’t run at all. No errors in CRM, SQL Server or SSRS logs. Both versions, SSRS and fetchxml, choked.

While we were unable to get to the bottom of this behavior, everything points out to the SSRS simply running out of some resources and quietly discarding the report.

To avoid excessive resources consumption:

  • If your report uses aggregates, move whatever aggregates you can to SQL Server. SQL Server will do the job of calculating your SUMs and AVGs better and much faster.
  • Avoid references and formulas involving report cells. Instead of ReportItems!foobar.Value use Fields!foobar.Value where possible. To get correct values from the cells, SSRS needs multiple passes over your report, slowing things down and consuming resources.
  • The above especially true when any grouping is involved. In other words, avoid aggregates over ReportItems like a plague.

Our results of following our own advice:

  • Original report for 3 months – 8 seconds, 12 months – blank.
  • Enhanced report for 3 months – .5 seconds, 12 months – 2 seconds.

Leave a Reply

Your email address will not be published. Required fields are marked *