x

Trouble rendering report in SSRS

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?
more ▼

asked Nov 07, 2011 at 12:11 AM in Default

bytegirl gravatar image

bytegirl
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

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...

  • memory - on the server. What else is running on the SSRS server?
  • client - it's a big old HTML document you're looking to load.
  • Is the HTML actually arriving at the client and just not being displayed?
more ▼

answered Nov 07, 2011 at 12:36 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(comments are locked)
10|1200 characters needed characters left
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?
more ▼

answered Nov 07, 2011 at 01:52 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

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, 2011 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, 2011 at 04:24 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x113

asked: Nov 07, 2011 at 12:11 AM

Seen: 740 times

Last Updated: Nov 07, 2011 at 12:11 AM