Subreports in a group footer printing for all values of a parameter

[Gnarfan]

Hello everyone

In Crystal 8.0, I am working on Commissions Statements for a group of Sales Reps. I have a main report with its selection formula based solely on the Sales Rep's name, which is selected by the user through the parameter ?RepName.

Table1.SALESREP_NAME = ?RepName

Within the main report, I have a single group on the Sales Rep's name. The header contains the person's name and some other info, and the group has several footers, all of which contain a different subreport showing different aspects of the rep's performance. ?RepName is linked to a parameter of the same name in all of the subreports.

Currently, when I run the report for just Jenny SalesRep, the main report and all of the subs print fine.

GH1: Jenny SalesRep
GF1a: Jenny's overall performance sub
GF1b: Jenny's deals sub
GF1c: Jenny's commissions sub

If I try to select more than 1 rep, all of the potential values for the ?RepName parameter are passed to the subreport, and the subreport runs for each of those values every time the GF containing it prints. So, if I run the report for Jenny and John, I see in the main report

GH1: Jenny SalesRep
GF1a: Jenny's overall performance sub
John's overall performance sub
GF1b: Jenny's deals sub
John's deals sub
GF1c: Jenny's commissions
John's commissions

GH1: John SalesRep
GF1a: Jenny's overall performance
John's overall performance
GF1b: Jenny's deals
John's deals
GF1c: Jenny's commissions
John's commissions

I worked around this problem by using the Group By selection formula in the subreports, and a series of formulas in Crystal syntax used to step through the potential values of ?RepName.

=-=-=-=-=-=-=-=-=-=-=-=-=-=
@initilizeVars
// In the main report's header
global booleanvar evaluate := TRUE;
global numbervar i := 1;
global numbervar countReps := count({?RepName});

=-=-=-=-=-=-=-=-=-=-=-=-=-=
@whichRep
// In the main report GH
global booleanvar evaluate;
global numbervar i;
shared stringvar whichRep;

if evaluate
then
(
whichRep := {?RepName}
)
;
whichRSD;

=-=-=-=-=-=-=-=-=-=-=-=-=-=
@counter
// In main report's GF1d, after all of the other GF1s
// containing subs

evaluateafter({@whichRep});
global numbervar i;
global numbervar countReps;
global booleanvar evaluate;

if (evaluate AND (i < countReps))
then
(
i:= i +1;
evaluate := TRUE;
)
else
evaluate := FALSE;
=-=-=-=-=-=-=-=-=-=-=-=-=-=

In each subreport, there is a group based again off of the rep's name. The sub's group selection formulas follow:

Table2.SALESREP_NAME = shared stringvar whichRep
//for the sub in GF1a

Table3.SALESREP_NAME = shared stringvar whichRep
//for the sub in GF1b

Table4.SALESREP_NAME = shared stringvar whichRep
//for the sub in GF1c

This solution allowed me to select multiple Sales Reps. However, it is only a good solution where the report can be run for a small group of Sales Reps, small enough that the user running the report will not go crazy before they finish selecting names. It isn't feasible when the report needs to be run for over 1000 reps, which is what I am now being asked to accomplish.

Is there another way to prevent subreports in a group footer from running all of the values of a multivalued parameter each time the subreport appears?

More importantly, what about the case of <ALL> salesreps? (My solution with the formulas will not work in this case because the rep's names are never entered into the ?RepName array.)

Thank you in advance for your advice.

 

[synapsevampire]

Eliminate the SALESREP_NAME parameter in the subreport, use the Main report SALESREP_NAME (not the parameter) to link to the subreport SALESREP_NAME field.

Now you'll only get that SALESREP_NAME in the subreport, and the Main report will iterate through each SALESREP_NAME.

The parameter should only exist in the Main report.

As for choosing 'All', the same applies, except that in your record selection formula, place something like:

If {?RepName}[1] <> &quot;All&quot; then
Table1.SALESREP_NAME = ?RepName
else
true

Now if your user enters or selects All as the first choice, all rows will be returned to the report.

Fire anyone named &quot;All&quot; and your set.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Gnarfan]

Trust me to come up with a complicated workaround when it's actually very simple.

Thank you, K!