Totals of if statement formulas 1

[GaryFrost]

My report consists of the following. I am using 3 tables : Schools, Pupils and Parents
A School has many pupils an a pupil has 0,1or 2 parents

The report looks as follows

Group Header 1 Schools
Group Header 2 Pupil
Details Pupil details and Parent details
@IsFather
@IsMother
Group Footer 2
SUM(@IsFather)
SUM(@IsMother)
@IsFather&Mother
Group Footer 1


@IsFather displays a 1 if Parent is father else 0
@IsMother displays a 1 if Parent is mother else 0
@IsFather&Mother displays a 1 is SUM(@IsFather) = 1 and SUM(@IsMother) = 1 else 0

The way the report works at the moment is that the details are repeated for the number of parents the pupil has – this is fine as I only want a total of pupils with a mother or a father or both for each school (I will suppress Details section)

When I try to insert a Grand Total on Group Footer 1 the @IsFather&Mother field is not displayed. If I manually try to create the SUM(@IsFather&Mother, GroupName (School)) then I get the error message “The Summary/running total field could not be created”

Which ever way round I format the report it seems I cannot create a summay/total for a formula with an if statement.

Hope that makes sense. Any pointers?

 

[bdreed35]

It isn't working because you can't create a Summary field off of another summary field.

Try changing your @IsFather&Mother formula to:

if {@IsFather} = 1 and {@IsMother} = 1 then
    1
else
    0

then place it in your detail section. Now you can create a summary off it for any of your groups or overall total.

~Brian

 

[hilfy]

Try changing @IsFather&Mother to this:

If {@IsFather} = 1 and {@IsMother} = 1 then 1 else 0

You should then be able to use it in a sum like you're using the other two.

-D

 

[Ngolem]

First of all, I must congratulate you on the layout of your problem...at a glance we can see where you are having difficulty

**************
Group Header 1 Schools
Group Header 2 Pupil
Details Pupil details and Parent details
@IsFather
@IsMother
Group Footer 2
SUM(@IsFather)
SUM(@IsMother)
@IsFather&Mother
Group Footer 1

@IsFather displays a 1 if Parent is father else 0
@IsMother displays a 1 if Parent is mother else 0
@IsFather&Mother displays a 1 is SUM(@IsFather) = 1 and SUM(@IsMother) = 1 else 0

****************************
this problem is easily handled with a variation of the 3 formula sum method

In the Group 1 header place the following formula

//@Initialize (Suppressed)

WhilePrintingRecords;
numberVar TotalFatherOnly := 0;
numberVar TotalMotherOnly := 0;
numberVar TotalBothParents := 0;

now in the Group 2 header place the formula

//@ResetPupilParents (Suppressed)

WhilePrintingRecords;
NumberVar father := 0;
NumberVar mother := 0;

Now in the Detail section put the following formula

//@DetermineParents (Suppressed)

WhilePrintingRecords;
NumberVar father ;
NumberVar mother ;

if {Table.father} = "father" then
father := 1
else if {Table.father} = "mother" then
mother := 1;

In the Group2 pupil footer place the formula

//@totalprofile
WhilePrintingRecords;
NumberVar father ;
NumberVar mother ;
numberVar TotalFatherOnly ;
numberVar TotalMotherOnly ;
numberVar TotalBothParents ;

if father = 1 and mother = 0 then
TotalFatherOnly := TotalFatherOnly + 1
else if father = 0 and mother = 1 then
TotalMotherOnly := TotalMotherOnly + 1
else if father = 1 and mother = 1 then
TotalBothParents := TotalBothParents + 1;

Note: you can see in the above formulas that another option could be created....ie. the student has no father or mother
That is he is an orphan being looked after by a gaurdian...if you need this case then both father and mother would have a zero value and it could be titled "other" . This can easily be adapted in the formulas above

In your group 1 footer you can have the formula

//@display
WhilePrintingRecords;
numberVar TotalFatherOnly ;
numberVar TotalMotherOnly ;
numberVar TotalBothParents ;

"Breakdown of Pupils Parentage:" + chr(13)+ chr(10)+
" Mother only - " + totext(TotalMotherOnly,0)+ chr(13)+ chr(10)+
" Father only - " + totext(TotalFatherOnly,0)+ chr(13)+ chr(10)+
" Both Parents - " + totext(TotalBothParents,0);

That should solve your problem

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[GaryFrost]

Many thanks Jim thats solved it!

I've got to cope with orphans, step parents, foster parents etc I just wanted to keep it simple to understand the theory but now you solved that I should be able to apply it to all possible permutations.

Many thanks again

Gary Frost