Running Totals with Shared Variables

[harrietohs]

Last week, you solved the problem for me when I had to subtract the total count of two groupnames from another groupname, by using the set of formulas at the end of this note.

Is it possible to use something similar for a sum of $ amount field group total on the same report?

What I want to achieve is to deduct the value of those records with status of Held or Terminated, from Status of Active sum.

Here's the set of formulas:



To decrease Active Count by Held and Terminated:

@inittotal
shared numbervar total:= 0;
-Place in Report Header

@accumtotal
shared numbervar total;
if GroupName ({PAW_CC_AMMEND.Gift Status}) = "Active" then
total := total + Count ({PAW_CC_AMMEND.Constituent ID}, {PAW_CC_AMMEND.Gift Status})
else
total := total - Count ({PAW_CC_AMMEND.Constituent ID}, {PAW_CC_AMMEND.Gift Status})

Note: replace groupname and field names as required.
-Place in Group Footer 1


@showtotal
shared numbervar total;
total
-Place in Report Footer


Thanks,

Harriet Farmer
Ottawa Humane Society

 

[Madawc]

Have you tried using Crystal's own Summary Totals and Running Totals? A lot easier and quicker than variables, for most purposes.

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.


Madawc Williams (East Anglia, UK)

 

[harrietohs]

I didn't think I could use running totals because I am trying to subtract the positive totals for 2 groups from the positive total of another group: (i.e. Sum of Active amounts pledged, minus the Sum of Held and Terminated amounts pledged.)


Harriet

 

[GJParker]

It would help if we new what your data looked like and the layout of your report.



Gary Parker
MIS Data Analyst
Manchester, England

 

[harrietohs]

Layout of report:
Grouped by: Gift_Status (Active, Terminated, Held)

Name Mthly_Amount
ACTIVE

Doe, John $50
Smith, Joe $10

$60

TERMINATED
Presley, Elvis $5
Monroe, Marilyn $2

$7

HELD

Blow, Joe $30
Peters, Pete $100

$130

The goal would be to subtract the totals of TERMINATED ($7) and HELD ($130), from the total for ACTIVE ($60), with the correct grand total being a minus $77. This indicates to us the change in total amount pledged.

Harriet

 

[lbass]

Are any of these numbers coming from subreports? I only ask because your first post references shared variables, which are only necessary for subreports. If not, you should be able to create three running totals using the expert. {#Active} would be based on selecting the person's name or a person ID, distinctcount, evaluate based on a formula:

{table.giftstatus} = "Active"

Reset never. For the other two running totals ({#terminated} and {#held}, just replace the status in the evaluation formula. Then create a formula for the report footer:


{#Active}-{#terminated}-{#held}

-LB

 

[harrietohs]

No subreports. The piece I forgot to show was the result of the shared numbervar in a formula, that calculates the total count in the same way I want to calculate the total $amount.

To get the running count, I used the following 3 formulas:
@inittotal
shared numbervar total:= 0;

@accumtotal
shared numbervar total;
if GroupName ({Gf.Gf_Gift_status}) = "Active" then
total := total + Count ({GfCnBio.GfCnBio_ID}, {Gf.Gf_Gift_status})

@showtotal
shared numbervar total;
total
else
total := total - Count ({GfCnBio.GfCnBio_ID}, {Gf.Gf_Gift_status})

When I tried to do the same thing for a sum amount, I couldn't get it to work.

 

[lbass]

You don't need to use variables. Please try my suggestion.

-LB

 

[harrietohs]

LB ...sorry I haven't gotten back to thank you till now, but I was away. Thank you for introducing me to "running totals". None of the training I have taken covered this wonderful function. I followed your examples and it gave me exactly what I wanted.

Thank you so much.

Harriet