A Report that never stops calculating

[sumthin_Missin]

I have a somewhat complex report that will appear okay in layout or design
mode, but the access status bar keeps displaying "calculating" and giving me
an hourglass pointer and it doesn't ever stop.

The main report contains a subreport. Within that subreport is another
nested subreport. The nested subreport is also filtered by another field on
the main report.

Does something here inherently raise a flag or do i need to describe the
fields and relations on the report?

 

[aaron.kempf]

Access is obviously too slow for you.

I would reccomend upsizing to SQL Server.
With SQL Server, you can always run profiler to see what is happening
on the db server-- with Access you have no choice but to sit around
and wait and hope.

 

[BruceM]

I'm sorry to say I don't have an answer to your question, but you need to
ignore the bitter and meddlesome troll Aaron. His answer is always the
same, and almost never addresses the question at hand.

I think you need to describe things in greater detail. Is your subreport
based on a table that is related to the main report? Is the next subreport
based on a table that is related to the first subreport's table? If Yes to
both questions, do you even need the filter? The relationship should take
care of returning the correct records. If No, how are you applying the
filter, and how are the data connected?

 

[Tony Toews [MVP]]

I have a somewhat complex report that will appear okay in layout or design
mode, but the access status bar keeps displaying "calculating" and giving me
an hourglass pointer and it doesn't ever stop.

The main report contains a subreport. Within that subreport is another
nested subreport. The nested subreport is also filtered by another field on
the main report.

One level of subreports can be a huge performance problem. Nesting
two levels of subreports can be much, much worse.

"On the reports with subreports I ended up putting all the data back
on one report and displaying the main report fields only when the data
changed. I did not want to depend on the "Hide Duplicates" property of
controls as they could legitimately be the same from one master record
to another. What went from 20 to 30 seconds before splitting to 20
or 30 minutes after splitting went back to 20 or 30 seconds."

How to speed up complex forms and reports with many records each with
subreports.
http://www.granite.ab.ca/access/splitapp/performance.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[sumthin_Missin]

Thanks for the tips -- I figured out a way to make the report work with only
one level of subreports and now it only calculates for a few seconds.

Only problem is now I have multiple duplicate rows on the main report, each
with one different matching subreport value, instead of one main report row
with multiple subreport rows.
Thanks

 

[Tony Toews [MVP]]

Thanks for the tips -- I figured out a way to make the report work with only
one level of subreports and now it only calculates for a few seconds.

Glad to hear that tip helped.

Only problem is now I have multiple duplicate rows on the main report, each
with one different matching subreport value, instead of one main report row
with multiple subreport rows.

You're going to have to use some code in the OnPrint event to make
those fields visible or not visible depending on when a primary key
changes.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/