Crystal XI report with a subreport taking a long time to complete (2h

[olrac]

Hi,

I'm new to this forum and appreciate any help. I created a main Crystal report using SQL query plus a subreport via an SQL view. The link is via employee number. Separately the reports take no more than a few minutes but linked via a subreport ... it goes on for hours.
Question:
1 - Does referencing the subreport triggeer the subreport to refresh for every employee reference
2 - Can the subreport be set up to only run once

Thanks

 

[hilfy]

If you are running the subreport for each individual employee record, then the query in the subreport will be run once for each employee - subreports do not cache data, they run the full query every time they're called.

If the subreport needs to show data for each person, then there is no way to run it once.

What are you doing that you specifically need a subreport for? Is there any way to combine the data from your subreport and the main report in a single query so that you can get rid of the subreport?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[olrac]

Thanks hilfy. Your response answers a lot of questions.

What I'm trying to do is track employees in our PR system that got multiple entries in error then report which timecard it happened in. If I cross the original SQL queries I'll get multiples of the original enteries and this will be confusing to the user (in the case below I'll get 4 entries that add up to 30 instead of 15).

example:

MAIN REPORT..
ee # work date code hrs
1234 02AUG2011 211 7.50
1234 02AUG2011 211 7.50
TOTAL: 15.00

SUB REPORT:

EE TIMECARD_ENTRY_DATE PAID_ON
1234 04AUG2011 12AUG11
1234 15SEP2011 23SEP11

I first analyze the data and look for all employees that have coding >= 15 hours. I then report the days worked and when they were paid.

Does this help?
Thanks

 

[hilfy]

Other than the employee number, is there any link between the rows in the main report and the rows in the subreport?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[olrac]

Hi Dell,

No, the subreport is linked to the main report on employee number on the detail level. I tried running it at the group level(main) to detail(sub) and it takes just as long.

Thanks

 

[lbass]

But how do YOU know that multiple entries have been entered in error? In your sample you show two entries that are exactly the same and you seem to be saying they are both legitimate. I'm guessing there are other relevant fields that would clarify this.

How do you what timecard entries relate to what workdates in the main report? Is there a general rule about this, e.g., the timecard entry date is always a Friday?

-LB

 

[olrac]

Hi,

I solved this problem by not using a sub-reports. It just took too much time to re-calculate every request to the subreport. I re-did my SQL query (nesting all the queries instead of using tables) and now it's very quick (40 seconds). Thanks for the help