|
I have a report that takes 4 parameters and return data into a straight forward table, no aggregation, no calculation. the table is quite wide though, about 30 columns. If i run the report for a week, my data returns and my report renders. if i run it for a month, it never renders. the problem is not on the data side. if i run the month query through ssms, it takes about 3 minutes. if i run the report through the front end and i check the logs, i can also see the rows returned and the table states rsuccess. but the report just never renders. I have tested it for different scenario's, even limiting the report to only 5 columns. thing is, the proc returns about 64000 rows when i do it for a month, can that be an issue? I also changed the stored procedure to make use of local variables, as that was one of the suggestions i read. this did not fix my issue :( any ideas?
(comments are locked)
|
|
Some reporting limitations are documented here: http://msdn.microsoft.com/en-us/library/ms156002.aspx. There are various links from that page to, for example, pages on Processing Large Reports http://msdn.microsoft.com/en-us/library/ms159638.aspx Things I would be looking at in addition to the suggestions from MS in the above pages...
(comments are locked)
|
|
How long does the SSMS method take when you run it for a week of data? Are all 64k rows to be displayed in the report? Thats a report that, even if you can fit 40 lines per page, will be 1600 pages long... Will that report be of any use to anyone? for a week it takes less than a minute. I think it is just the amount of data that is causing the issue. i am setting up a meeting with the client to see how they use the report, because that amount of data does nto make sense - maybe we can have something at a higher/aggregated level. i just find it strange that the ExecutionLog table updated the status of the report to success, and yet the report does not render.
Nov 07 '11 at 03:39 AM
bytegirl
I would suggest recommending to them that they have a summary report with suitable aggregation on the data and then the ability to drill into a detail report that shows part of the 64k rows, for a specific sub-sector of the data. These two reports, supported by two parameterised stored procedures will be more efficient for the SQL Server to process and return data much more quickly than at present
Nov 08 '11 at 04:24 AM
Fatherjack ♦♦
(comments are locked)
|
