I have a crosstab report that retrieves names and years of information.
It is sorted by the name and the report runs in 45 seconds for multiple years. However when I try to break it out either with page breaks or sections for the name the report takes over 15 minutes to run.
I just want the page breaks to work since they come up in excel all split into tabs.
I have recently upgraded to fix pack2 for 8.4 and no changes.
Any Ideas?
Have you ever received feedback on this? I'm curious what the recommendations are.
I am not sure if this is of any help, but I had a similar problem, and after going at it for a couple of days, I decided to recheck my relationship map in the Framework Manager. And, bingo! I found an errant join.
The other thing to bear in mind is that page breaks/sectioning in a report add an extra "group by" to the generated SQL query, and you may well find the database is not set up to handle grouping/retrieval of the offending column efficiently. Have you compared the SQL of the report before/after adding the page break/section to see the difference? Also, if you have query monitoring tools for your database, you should be able to see how the database is retrieving data based on the query, and be able to get appropriate timings to see if the db is the bottleneck.
Regards,
MF.