Total Formula excluding duplicates. 1

[Stella1209]

I coditionally suppress a duplicate records in the report. Do u have any ideas how can I create a total Formula that will exclude duplicate values.
Thanks in advance,
Stella

 

[CrystalVisualBOracle]

Doesn't it do it automatically when you create a total of the report field and insert it into the report by right clicking on the field you wish to sum and click on the appropriate total you want ie/ grand total, sub total, etc.?

CrystalVisualBOracle

 

[kenhamady]

Totals are calculated based on the result set of the report, not what is visible. Supressing an object or a section does not affect totals. You need conditional totals, which are described in the FAQ on common formulas. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Stella1209]

Ken,thanks for your advise.
I created 3 conditional formulas for totals that should
exclude duplicates, and I got right calculation.But,now I am getting duplicates by groups.( I have 5 groups in my report). Do u have any idea how to fix it?
3 formulas that I am using:

@Display
WhilePrintingRecords;
CurrencyVar RunningTotal

I Put this formula in to group total



@Initialize
WhilePrintingRecords;
CurrencyVar RunningTotal := 0

This one in to page header


@Evaluate
WhilePrintingRecords;
CurrencyVar RunningTotal;
RunningTotal := RunningTotal + {Test.Field name}

this one in to details section

Thanks,
Stella

 

[kenhamady]

The formulas that you showed are not conditional totals, they are running totals using variables. For conditional totals use FAQ 1B, #10.

I don't know what you mean by "duplicates by group". Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Stella1209]

Thanks Ken,

I got my totals, but I have 5 groups in a report. Do I need to have separate formulas for each group to get conditional totals?

Thanks,
Stella

 

[kenhamady]

I forget, what version of CR do you have?
Can you show me your current formulas? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Stella1209]

Ken,
I am using CR V5.
I have 3 running totals formulas using variables listed above. Those formulas working right only for a 1 group in a report. I have 5 groups. What should I do to make them work for other 4 groups?
thanks,
Stella

 

[kenhamady]

OK, I reread this thread from the beginning. You don't need conditional totals as I earlier said. You are using variable running totals which is correct. I will stop calling them conditional totals.

If I understand you correctly, you want to eliminate duplicate values from your totals. You can already suppress the duplicates using the format options, but they still get into the totals. This means that all of the duplicates are consecutive, becuase "suppress if duplicated" only works when they are consecutive details. It doesn't work if you have a value in group 2 that is a duplicate of a value in group 1. So my first question is, what do you mean by "duplicate by groups"

You say that the formulas are working, but I don't see how. If you put the evaluate formula on the details section it will still include every record. This doesn't eliminate the duplicates. You also say that the reset is in the page header. This will cause a problem is the group is more than one page. It should be in a group header, one that doesn't repeat on each page.

If you have to do the same thing for multiple groups, you will probably need a separate set of 3 formulas for each group, each using a different variable name. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Stella1209]

Ken,
Thanks for your responce!

Ok, it's a little confusing because I didn't write all details. I am going to exlain everything from the beginning.
I suppressed duplicates by writing a formula in details section under suppress:
WhilePrintingRecords;
If {C_ILSOBG.SO} = Previous ({C_ILSOBG.SO}) and {C_ILSOBG.ITEM} = Previous ({C_ILSOBG.ITEM}) and {C_ILSOBG.SO_LINE}=Previous ({C_ILSOBG.SO_LINE})Then true

After that I have to get totals for every group. I created 3 formulas with variable to do that:
****************************
Reset formula, in a group header(as you said):

WhilePrintingRecords;
if OnFirstRecord = false then
CurrencyVar RunningTotal2:=0
else
RunningTotal2:={tablename.field name}
******************************
Evaluate formula, in details:

WhilePrintingRecords;
CurrencyVar RunningTotal;
//check if duplicate
If Previous ({tablename.field name }) = { tablename.field name } Then
RunningTotal :=RunningTotal
else
RunningTotal:=RunningTotal + { tablename.field name }

****************************
Display formula, in a group footer:
WhilePrintingRecords;
CurrencyVar RunningTotal
************************
Forget about "Duplicates by group" I fix it.

Thanks for telling me to create a separate set of 3 formulas for each group, it's working now.

I also need to have a Grand total, should I create the smae set of 3 formulas for that?


Thanks a lot,
Stella

 

[kenhamady]

Yes, I am afraid so.

Since it is working I wouldn't change it, but the "Previous()" technique for eliminating the duplicates isn't the best. I would have created a field that was:

SO+Item+line

and added 6th group on that field. You could then put all of your printing detail fields on this GF and suppress the details. You could also put your evaluate formulas on here, and it would only accumulate once per Group6, eliminating the duplicates.

Or if you still need to see the details you could suppress GH6 and GF6 and still use them to evaluate only once per group.

Good Luck. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)