SQL Server Reporting Services Returns Too Many Records

Question

I have 3 really big tables in SQL Server 2012 all related by phone number. When I create a report with the Visual Studio BI Reporting Wizard I receive the following error:

"An error occurred during local report processing. An error has occurred during report processing. Exception of type 'System.OutOfMemoryException' was thrown."

I assume the report is returning too many records if it runs. How can I stop the report from automatically running? Or other suggestions?

The query used in the report is below.

SELECT table1.customer_phone, table2.PhoneNumber, table3.ANI
FROM LMIRSessions INNER JOIN
                  table1 ON table2.PhoneNumber = table1.customer_phone INNER JOIN
                  table3 ON table2.PhoneNumber = table3.ANI

Do a select count(1) to get the number of records returned to give everyone an idea of what you mean by "big" tables. That might help with the responses — Lereveme

J0e3gan J0e3gan · Accepted Answer · 2014-02-17T23:33:57

Consider adding one or more parameters to the SSRS report to limit the number of records returned:

A parameter could narrow down PhoneNumber values considered - by exact or pattern matches.
Maybe there are sensible parameters to narrow the report's results by date and/or time too (judging by the table name LMIRSessions, which I expect may have columns like SessionStart and SessionEnd).
Lastly (also judging by the table name LMIRSessions), maybe there are potential parameters to narrow the report's results by session type(s) or outcome(s).

It might also make sense to consider paging report results. Even without domain-specific filtering parameters like I have suggested above, you can employ paging to limit the number of records returned (and related resources required).

SQL Server Reporting Services Returns Too Many Records

1 Answers