Summary Values using Condition

[karnd]

Hi,

I have the field Amount that includes both Parent and Child records but when i sum them up it should not sum up the child records data.

Dept Name Amount
======== ========
Dept1 1000.00
Dept1-ABC 200.00
Dept1-XYZ 500.00
Dept1-MNO 300.00

Dept2 2000.00
Dept1-ABC 1200.00
Dept1-XYZ 400.00
Dept1-MNO 400.00

Sum ===========
3000.00
==========

Sum should be 3000.00 but not 6000.00


Note: Dept1 Amount is summation of Child values but since the report requirement shows both Parent and child records need to display so i have no idea how to exclude them

Thanks for your help!!
Regards.

 

[synapsevampire]

You might show real data rather than a reports output, the output doesn't explain it as well.

Also include basics such as your software version so as nto to waste peoples time by coming up with a solution your version doesn't support.

I'llk have to guess here since you didn't share how the data is stored...:

Group by the parent field

In the group header use a formula such as:

whileprintingrecords;
numbervar MySum:=MySum+{table.field}

Then in the report footer display using:

whileprintingrecords;
numbervar MySum

-k

 

[karnd]

Hi,

Sorry for delay.
I am currenttly using of CRXI.

The report looks like this:
GR1
GR2
Details a
Details b
GF1
GF2

Just for display purpose on the report i have reflected Details b same as Details a.
Details a & b sections has DEPTNAME field with Amount.Its not grouped by.

The DEPTNAME & Amount fields on the Database got populated same as shown on the report.
But the report displayed for the child records is in such a way that when DEPTTYPENAME is NULL then SUPPRESS "Details a" section.
It works fine upto display as per the report requirement but only concerns is SUM(Amount) should not consider child records data i mean it has to exclude childs' Amount going through.

I hope its clear now.
But the above solution you provided does not work for this.
Please let me know if it is not clear.

Thanks

 

[karnd]

Hi,

Could you please throw some ideas ...

Thanks

 

[lbass]

Synapsevampire's solution should work, so if it doesn't it is because your report structure is not clear. Please use "GH" for group header, not GR, and identify what each group is. Note also that groups are ordered like this:

GH#1 Department
GH#2 Class A
details_a Student
details_b Student Address
GF#2 Count of Students in Class A
GH#2 Class B
details_a Student
details_b Student Address
GF#2 Count of Students in Class B
GF#1 Count of Students in Department

From your example, it looks like the "Dept1" is a group field. If it is not, you need to clarify. You also should clarify whether the amount you are showing for the department group is a summary or whether it is a database field (that was built to contain a summary of the child records).

-LB

 

[karnd]

Hi,

1)My bad , its GH1,GH2 i have used in the report but i put wrongly in the threads.

2)I agree with your way of report structure but earlier i tried as per you said and i have missed some DEPTNAME records(which report wanted them also) that is i came up like that
3)For your sake, DEPTNAME is displaying under Details_a section and also AMOUNT Field besides DEPTNAME field.
4)I have created Detail_b section just exact as Details_a but the purpose is displaying of child records.
So displaying of child records is shown on the report in such a way that they need to be indented further away (tab space) from the parent records when satisfies ISNULL(DEPTTYPENAME). so , parents records are left aligned in Detail_a section and child records in Details_b section are tabbed.
5)Finally my concerns is, the amount records in Details_a and Details_b should not be double counted just because of the child records. so what ever parents has it has to take that amount record when i SUM up.

please let me know if it is not clear.
thanks a lot for inputting.

Regards,

 

[lbass]

You did not really respond to my request for clarification.

1-What ARE your group fields?

2-Clarify whether the amount you are showing for the department group is a summary or whether it is a database field (that was built to contain a summary of the child records).

-LB

 

[karnd]

Hi,

Thanks for your response.

1)The GH1 and GH2 someother fields(but not DEPTNAME) grouped by.
GH1 is RegionTypeName
GH2 is PlaceTypeName
and Details_a is DeptName(which is not grouped by again please!!!)
Basically it has like that the report requirement is.

2)Amount is a Database field sitting besides DeptName field but not grouped by on DeptName field and which also contains child records(which is like that in Database).
only difference between Details_a and Details_b is display of Parent and child records on the report by Suppressing Details_a when ISNULL(DEPTTYPENAME)

Eg:
GH1-NorthUSA
GH2-Inflation
Details_a - Dept1 1000.00
Details_b Dept1-ABC 200.00
Dept1-XYZ 500.00
Dept1-MNO 300.00

Dept2 2000.00
Details_b Dept1-ABC 1200.00
Dept1-XYZ 400.00
Dept1-MNO 400.00

Sum ===========
3000.00
==========

I hope its clear now otherwise please let me know.

Thanks a lot for your cooperation.
Regards,

 

[lbass]

Okay, that's much clearer. Please try this. Create a formula:

if not isnull({DEPTTYPENAME}) then {table.amt}

Place this in detail_a and right click on it and insert a summary (sum) at the Group 2 and again at the Group 1 level, if you want summaries at both.

-LB

 

[karnd]

Hi,

Wah!!!that's really great.Its working fine now.

Thanks a lot for all your input.

Regards,