Reset formula fields for a sub-report

[ConfusedCrystalite]

Hello everyone,

I've not used Crystal Reports before but have been asked by a client of ours to modify a report that's called by their line of business software. Through reading the in-line help, trial and error and reverse engineering I've got most of the way there but am stuck on one particular problem. The report in question contains a sub-report which calculates 90% of the data and that data is summarised on the main report. I've created a few simple formula fields on the sub-report which are shared with (and displayed on) the main report. This works well if the report is run for one client and one client only. The problem arises if, via the line of business software, you select multiple clients. In that situation my formula fields don't reset but carry forward.

This might be easier to explain by way of example.

Say I run a report for a client named Barry. Barry spent £1000 with us and breaking that cost down we find he spent £500 on apples and £500 on oranges. Because I've only selected Barry for consideration, all is well. Another customer, Steve, spends £2000. £1000 on apples and £1000 on oranges. If I run a report for both Barry and Steve on Barry's report the correct subtotals will appear (£500 worth of apples and £500 worth of oranges) but on Steve's it will appear as if he spent £1500 on apples and £1500 on oranges because Barry's subtotals carry forward.

If any of that makes sense (a stretch, I know) my question is: is there any way I can ensure the formula fields reset to zero before processing Steve's data?

Any and all advice very gratefully received.

=)

 

[lbass]

I can't tell whether you are actually summarizing the data on the main report or just displaying the shared variable. Also can't tell where the subreport is located. I'm going to assume it is executing once for each client. In that case, create a formula like this:

whileprintingrecords;
shared currencyvar apples := 0;
shared currencyvar oranges := 0;

Place this in a section above the one in which the subreport is located. If the sub is in GH_b, place the above formula in GH_a.

If this doesn't resolve the issue, explain in what report section the sub is located, show the content of the shared variable formula in the sub, and show the content of the formula you are using in the main report. Also identify how the sub is linked to the main report, if it is linked.

-LB

 

[scottostanek]

Another way to handle this that I use is to rig the display formula in the main report to reset the shared variable as it is displayed:

@D_apples
whileprintingrecords;
shared currencyvar apples;
local currencyvar disp_apples := apples;
apples := 0;
disp_apples

More complex at first glance but you don't have a reste formula floating around to be lost.

Rinse and repeat.

It means one less formuala for each to keep track of, which can be significant if you are passing over ten or more values from a sub out to the main.

Scotto the Unwise

 

[ConfusedCrystalite]

lbass and scottostanek, thank you both for your replies and sorry it's taken me quite so long to follow-up. I haven't heard nearly as much time to work on this as I'd like.

lbass - the sub-report is located in a group header and you assumed correctly, I'm displaying the shared variable and not summarising.

Regrettably I'm still having the same problem, perhaps it would be of use to quote my code?

Sub-report:
WhilePrintingRecords;
if {qryBilling280.Service} = "Some Value" then
shared numbervar ThisTotal:= ThisTotal + (tonumber({qryBilling280.SomeNumber}) * tonumber({qryBilling280.SomeOtherNumber}))

Primary report:
WhilePrintingRecords;
shared numbervar ThisTotal;
local numbervar DisplayThisTotal := ThisTotal;
shared numbervar ThisTotal := 0;
DisplayThisTotal

If you're both shaking your heads in disbelief please don't hold back, I'm ready and willing to be enlightened. =)

Cheers!

 

[lbass]

The sub should be located in a group header based on the name field and the sub should also be linked to the main report on the name field.

-LB

 

[ConfusedCrystalite]

Hi lbass,

The sub-report is in a group header based on the ClientID field, the one and only field which is also linked to the sub-report (Edit | Subreport Links).

That's as it should be, right?

 

[lbass]

In what main report section are you placing the display formula? It should be in the group footer.

Ordinarily, I would share a summary from the subreport, not an accumulating value. Why not do a formula like:

if {qryBilling280.Service} = "Some Value" then tonumber({qryBilling280.SomeNumber}) * tonumber({qryBilling280.SomeOtherNumber})

Then set the shared variable to the sum of this:

whileprintingrecords;
shared numbervar ThisTotal := sum({@yourformula});

-LB

 

[lbass]

In what main report section are you placing the display formula? It should be in the group footer.

Ordinarily, I would share a summary from the subreport, not an accumulating value. Why not do a formula like:

if {qryBilling280.Service} = "Some Value" then
tonumber({qryBilling280.SomeNumber}) * tonumber({qryBilling280.SomeOtherNumber})

Then set the shared variable to the sum of this:

whileprintingrecords;
shared numbervar ThisTotal := sum({@yourformula});

-LB

 

[ConfusedCrystalite]

lbass,

Many thanks for your patience and continued help; I don't doubt this is something you could have put together in a heartbeat. I'm pleased to report that, after following your advice, the figures are now displayed as they should be. Not only that, I've learnt a number of new and interesting to work in Crystal that I wasn't previously aware of.

Invaluable!

Cheers!