Sum Formula 1

[kuberacupidagra]

I have a formula called @CurrentBCWS and when I browse the data it gives two values: 3913 and 4338.

I create another formula called @CurrentBCWSsum = sum({@currentBCWS}) and the value is: 267,870.

What could be going wrong here.

Thanks for your help.

AA

 

[Madawc]

Are you modifying someone else's Crystal report?

Are there multiple records grouped for two values?

If that's it, you need a running total that evaluates once per group.

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). Using Windows XP & Crystal 10

 

[lbass]

Note that your formula will sum the values of this formula for the entire report. If you are trying to summarize at the group level, right click on the detail level formula and insert a summary at the group level. The equivalent formula (unnecessary to create in this instance) would be:

sum({@currentBCWS},{table.groupfield})

-LB

 

[kuberacupidagra]

Thanks both to Madawc and lbass. I have tried your methods and have got only a partial answer.

I created a formula CurrentBCWSsum1 = sum({@currentBCWS}, Departments.Dept}) and the values for dept(662 and 667) are 177,863 and 90,006 which adds up to 267,870 which I got earlier. The values that I am looking for are 4338 and 3913 for dept(662 and 667) which is displayed by @CurrentBCWS.

The formula for @CurrentBCWS is:

Choose ({@ReportMonth},{HOURS1.Jan},{HOURS1.Feb},{HOURS1.Mar},
{HOURS1.Apr_},{HOURS1.May},{HOURS1.Jun},
{HOURS1.Jul},{HOURS1.Aug},{HOURS1.Sep},
{HOURS1.Oct},{HOURS1.Nov},{HOURS1.Dec} )

The group name is: Departments.Dept

Looking forward to your help.

Regards,

AA

 

[lbass]

Please describe your report structure (groups, etc.) and provide some sample data.

-LB

 

[kuberacupidagra]

Thanks lbass. Here is the report structure with some sample data:

There are four tables:

1) TTIST002530 with fields:
T_PDNO, T_CWOC, T_AHMC, T_CMDT

2) TTISFC010530 with fields:
T_PDNO, T_OPNO, T_ITEM, T_CWOC, T_OPST, T_LFDT, T_PRTM

3) Departments: T_CWOC, T_DSCA, CATEGORY, DEPT

4) Hours1: Desc, Type, DEPT, Jan,Feb to Dec

The first two tables are joined by T_PDNO and T_CWOC
The second and third tables are joined by T_CWOC
The third and fourth tables are joined by DEPT

The department has values of 663, 667, 661, 662 which correspond to values of 4273, 6922, 7952, 9036 for Jan and 4273, 6922, 7952, 9036 for Feb etc.

There is other data but I am giving the data relevent to this @CurrentBCWS and @CurrentBCWSsum problem.

Best Regards,

AA

 

[lbass]

Sorry, I can't follow this. What I meant by sample data was the layout with actual data as it relates to your formula. Please show your formula in the detail section and the summary value you expect to see in the group footer. Also show other relevant fields. You probably need to use running totals, because of the multiple tables, and what you would be looking for is the unique ID that corresponds to the formula value. Once you have determined how the value is repeating, you can set the running total to evaluate on change of that field. For example,

Dept A ID yourformula other field
123 2345 abc
123 2345 def
123 2345 ghi
456 1011 abc
456 1011 def
456 1011 ghi
group total 3356

In this example, you would set the running to total to sum your formula on change of {table.ID}, reset on change of grop (department), and then place the running total in the group footer.

-LB

 

[kuberacupidagra]

Thanks for your advice lbass.

Yes I did find duplicates when using the @CurrentBCWS formula.

Dept @CurrentBCWS
662 5351.84
662 5351.84
662 5351.84
.
.
.
Total 219425.44

Dept @CurrentBCWS
667 4827.78
667 4827.78
667 4827.78
.
.
Total 111038.94

I now have to select distinct record. I tried the formula
Sum of (Distinct count of @CurrentBCWS)
but it gives errors. Is there a function to add up just values for the formula @CurrentBCWS.

Thanks and Regards,

AA

 

[lbass]

Please clarify whether there is only one value per department. If there is only one value per department, you can right click on your formula and insert a maximum to get the group summary. Then insert a running total to get the grand total, where you would select {@currentBCWS}, sum, evaluate on change of group (department), reset never.

If there is more than one value per department, then you need to determine what field changes when the formula changes, and use that in a running total in the evaluation section (evaluate on change of field->that field), and reset on change of group for the group summary (reset never for the grand total).

-LB

 

[kuberacupidagra]

Hello lbass,

Thanks for your help which is very valuable. My group is Departments.Dept for whcih I have multiple values. I have two values for Dept(662 and 667). Each Dept(662 and 667) has several values for @CurrentBCWS which are same and repetitive of which I want only one each.

I did the following for Running Total,

1) Created a Running Total #RTotal0
2) Field to Summarize - @CurrentBCWS
Type of Summary - Sum
3) Evaluate
On change of field - Departments.Dept
4) Reset
On Change of Group - Group#1: Departments.Dept

Now in the Group Footer I have:

Dept: 662 @CurrentBCWS: 4338.14 (Instead of 177,863)
Dept: 667 @CurrnetBCWS: 3913.34 (Instead of 90,006)

The new values for Dept 4338.14 and 3913.34 are CORRECT as I am picking up just one value instead of total of all values. Do I have to create another formula to add up the two new values for #RTotal# for a Grand Total?

Thanks so much for your help.

AA

 

[lbass]

Yes, create another running total exactly like the other except that you will use reset: never, and place the running total in the report footer.

-LB

 

[kuberacupidagra]

Hello lbass,

Yes, it works now and I can progress with the rest of my report.

You are indeed a very kind and knowledgeable person.

Thanks and Regards,

AA

 

[kuberacupidagra]

Hello lbass,

I am not sure if I have to open another thread. Since this is related to the last query I am requesting it in the same thread.

I used the Insert-> Summary on @CurrentPRTM to create (Sum of @CurrentPRTM). I am now trying to find variance as follows:

@CurrentScheduleVariance = ({Sum of @CurrentPRTM} - {#RTotal1})

The formula is giving an error that Sum of @CurrentPRTM field is not recogonised. Yes, I don't have the field Sum of @CurrentPRTM but have @CurrentPRTM. How do I sum @CurrentPRTM to use in the formula.

Regards,

AA

 

[lbass]

You need to specify where you are trying to place this formula. I also do not know which running total your formula is using. It is best to name the running totals something that makes sense, e.g.,{#BCWS-grp}. If your formula is to be evaluated at the group level, then your formula should look like:

sum({@CurrentPRTM},{Departments.Dept})-{#yourrunningtotal1}

At the report footer level, it would look like:

sum({@CurrentPRTM})-{#yourrunningtotal2}

This assumes that you have determined that {@CurrentPRTM} is returning correct results.

-LB

 

[kuberacupidagra]

Hello lbass,

My report is nearly complete except for one field. I was using the following formula for CurrentCostVariance which was incorrect: {@CurrentPRTM} - {@CurrentAHMC}

I changed it to:
sum({@CurrentPRTM},{DEPARTMENTS.DEPT}) - sum({@CurrentAHMC}, {DEPARTMENTS.DEPT})

This formula compiles without errors but when running gives an error: A summary has been specified on a non-recurring field Details: @CurrentCostVariance.

I changed it again using the Running Total field, but got the same error.

({#RTotal4}) - sum({@CurrentAHMC}, {DEPARTMENTS.DEPT})

What could I be doing wrong?

Regards,

AA

 

[lbass]

Please share the contents of all nested formulas. Also please explain whether any of these fields can be null.

-LB

 

[kuberacupidagra]

Hello lbass,

I was trying to get the report definition file but it seems I have one installation file missing. Will try to get it later. Below is the list of formulas used for @CurrentCostVariance which may have problems.

@CurrentPRTM:
if ( ( {?ReportPeriodStart} <= {TTICST002530.T_CMDT} ) and
( {TTICST002530.T_CMDT} <= {?ReportPeriodEnd} ) ) then {TTISFC010530.T_PRTM}
else 0


@CurrentAHMC:
if ( ( {?ReportPeriodStart} <= {TTICST002530.T_CMDT} ) and
( {TTICST002530.T_CMDT} <= {?ReportPeriodEnd} ) ) then {TTICST002530.T_AHMC}
else 0


@CurrentCostVarianceTHIS GIVES THE ERROR)
sum({@CurrentPRTM},{DEPARTMENTS.DEPT}) - sum({@CurrentAHMC}, {DEPARTMENTS.DEPT})

There are two groups:
Group#1 Name: Departments.Dept
Group#2 Name: TTICST002530.T_CWOC

I don't have any fields that could be NULL.

Regards,

AA

 

[kuberacupidagra]

Hello lbass,

Here are some more formulae that I have used in the report.

1)@CurrentAHMC = if ( ( {?ReportPeriodStart} <= {TTICST002530.T_CMDT} ) and
( {TTICST002530.T_CMDT} <= {?ReportPeriodEnd} ) ) then {TTICST002530.T_AHMC}
else 0

2)@CurrentAHMCSum = sum ({@CurrentAHMC} )

3)@CurrentBCWS = Choose ({@ReportMonth},{HOURS1.Jan},{HOURS1.Feb},{HOURS1.Mar},
{HOURS1.Apr_},{HOURS1.May},{HOURS1.Jun},
{HOURS1.Jul},{HOURS1.Aug},{HOURS1.Sep},
{HOURS1.Oct},{HOURS1.Nov},{HOURS1.Dec} )

4)@CurrentPRTM = if ( ( {?ReportPeriodStart} <= {TTICST002530.T_CMDT} ) and
( {TTICST002530.T_CMDT} <= {?ReportPeriodEnd} ) ) then {TTISFC010530.T_PRTM}
else 0

5)@CurrentPRTMsum = sum( {@CurrentPRTM} )

6)@CurrentScheduleVariance = Sum ({@CurrentPRTM}, {DEPARTMENTS.DEPT}) - {#RTotal0}

7)@ReportMonth = month({?ReportPeriodEnd})

8)@ReportYear = year({?ReportPeriodEnd})

9)@ReportYearBegin = DateValue( {@ReportYear}, 1, 1 )

10) @CurrentCostVarianceTHIS FORMULA GIVES ERROR)
sum({@CurrentPRTM},{DEPARTMENTS.DEPT}) - sum({@CurrentAHMC}, {DEPARTMENTS.DEPT})

This formula compiles OK. While running gives an error: A summary has been specified on a non-recurring field Details: @CurrentCostVariance.

I have two parameter values(?ReportPeriodStart) and (?ReportPeriodEnd)

There are two Groups:

Group#1 Name: DEPARTMENTS.DEPT
Group#2 Name:TTICST002530.T_CWOC

Regards,

AA

 

[lbass]

Sorry, but I can't see any problem. Try removing the offending formula to make sure that is the only one causing the problem--it might tell you something if then a second formula results in the same error.

-LB