Sub Reports 1

[brxmas]

Sql Server 2000
Crystal 9.0

I'm running a Sales Rep report. I have the main report and a subreport included. This report takes forever to run. I would like to know how to increase the processing time.

Can anyone tell me in a subreport what is the sequence of records processed when there are individual reps. Does it get all the information for one rep (Current Revenue, Last Year Revenue, etc.) then go to the next rep and do the same process?

The main report is grouped rep #, Corp Acct, Category and has Current Revenue, Last Year Revenue for each rep and their accounts (detail).

In my subreport it summarizes totals at the end of each rep totals by Category, but doesn't list their account #'s.
(see example below)

Rep 103
Current Year Last Year Current YTD Last YTD
Adjustments 40 35 78 90
Color 25 18 52 125

The link from the main report to the subreport is by rep_num. If you need more information please let me know.

Thanks in advance.

 

[kskid]

Since you are summarizing by Rep #, I can only assume that your subreport is in the Rep# Group Header or Group Footer. The subreport will execute whenever a new rep# is encountered, which is your subreport link. I retreive about the same amount of time data each month for my monthly reports and it takes about 1/2 hour to run.

In terms of reducing the overall processing time, the only way you can do that is to eliminate the subreport. You (or your DBA) could make up a view similar to the Show SQL Query of the subreport. Then you can treat the view as an additional table linked by Rep#

Another problem you may be having is the indexing of the tables you are using in your subreport. Is the Rep# on the subreport table indexed? This will definitely speed up the selection process. It help tremendously on some of my reports when an index record was created on a date field that I was selecting.

Cheers,
-LW

 

[fredp1]

As kskid mentioned... avoid using the subreport.
Why are you using the sub in the first instance?
Maybe you can avoid using it depending on the detail.
If you are not doing any filtering or the join to the Category is straight forward why do you need a sub?
Can you provide more detail?

You can also use SQL Expressions to replace sub reports when appropriate.





Cheers
Fred

 

[lbass]

You could create a crosstab-like display by creating formulas that define each "cell", e.g.:

//{@YearToDate-Adj} to be placed in the detail section:
if {table.category} = "Adjustment" and
{table.date} in YearToDate then {table.amt}

//{@LastYearToDate-Adj}:
if {table.category} = "Adjustment" and
{table.date} in date(year({table.date}),01,01) to
date(year({table.date}),month(currentdate),day(currentdate)) then {table.amt}

Then you would right click on each formula and insert a summary (sum) at the rep group level, position the results where you wish, and add text boxes as labels. I wasn't sure what your Current Year and Last Year columns were, as they had lower amount than the year to date columns--perhaps they are for the particular month? Anyway, you would make similar formulas for them.

You could do the same thing with running totals, but they are second pass and take longer to process, so conditional formulas should be faster. Can't say for sure whether this would be faster than a subreport, but it very well might be.

-LB