Sum nominated components of group to create total

[IceRuby]

Using Crystal V11, ODBC connection to SQL database
Am using a Grouping formula to create the following headings.

SALARIES & WAGES 10,000
BONUS & ALLOWANCES 8,000
EMPLOYER SGC -5,000
SUPER SAL SAC -4,000
EXEMPT TRANS 3,000

I am using a formula (see formula below) to transpose values from positive to negative however am stuck in that the total I am trying to create should sum only SALARIES & WAGES, EMPLOYER SGC & SUPER SAL SAC. I have tried removing the BONUS & ALLOWANCES from formula but then Crystal removes the Group altogether. I need all groups & values to display only the total should not include all grouping values. E.g. Total would be 10,000 – 5,000 - 4,000 = 1,000

GROUPING FORMULA
if {transaction_type} = "WORK" and not({time_code} in ["WC", "WC85", "MIL", "ABS", "ANNACC", "ANNCRA", "ANNDRA", "LCTACC", "LCTACD", "LCTCRA", "LCTDRA", "LSLACC", "LSLCRA", "LSLDRA", "LWOP", "MILU", "PLU", "WC80"]) then "SALARIES & WAGES" else

if {transaction_type} = "WORK" and {time_code} in ["WC", "WC85", "MIL", "ABS", "LWOP", "MILU", "PLU", "WC80"] or {allow_dedn_code} = "OSTAXP"
then "EXEMPT TRAN" else

if {transaction_type} = "ALLOWANCE" and not({allow_dedn_code} in ["EGPLSD", "MILADJ", "PILOND", "VRLSD", "WCADJ", "ROLOVR"])
then "BONUSES & ALLOWANCES" else

if {transaction_type} = "DEDUCTION" and {deduction_category} = "NON EMPLOYEE" and {paye_tax_category} <> "GROSS" and not({allow_dedn_code} in ["RBFER2", "RBFER3"]) or {allow_dedn_code} like "*ER4"
then "EMPLOYER SGC" else

if {transaction_type} = "DEDUCTION" and {paye_tax_category} = "GROSS" and not({allow_dedn_code} in ["RBFER2", "RBFER3", "EPAC2", "REM2", "PUCHLV"]) and not({allow_dedn_code} like "*EE1")
then "SALARY SACRIFICE" else

if {allow_dedn_code} like "*EE1" or ({allow_dedn_code} in ["EPAC2", "REM2"]) then "SUPER SAL SAC" else

if {deduction_category} = "DISBURSEMENT" and not({allow_dedn_code} in ["RBFER2", "RBFER3"]) or {deduction_category} = "NET PAY" or {deduction_category} = "TAX" or {allow_dedn_code} = "PUCHLV"
then "OTHER"

TOTAL FORMULA
if (
{transaction_type} = "WORK" and not({time_code} in ["WC", "WC85", "MIL", "ABS", "ANNACC", "ANNCRA", "ANNDRA", "LCTACC", "LCTACD", "LCTCRA", "LCTDRA", "LSLACC", "LSLCRA", "LSLDRA", "LWOP", "MILU", "PLU", "WC80"]))
or {transaction_type} = "ALLOWANCE" and not(allow_dedn_code} in ["EGPLSD", "MILADJ", "PILOND", "VRLSD", "WCADJ", "ROLOVR"])
then {amount} else

//sgc
if {transaction_type} = "DEDUCTION" and {deduction_category} = "NON EMPLOYEE" and {paye_tax_category} <> "GROSS" and not({allow_dedn_code} in ["RBFER2", "RBFER3"]) or {allow_dedn_code} like "*ER4" or

//super sal sac
{allow_dedn_code} like "*EE1" or ({allow_dedn_code} in ["EPAC2", "REM2"]) or

//sal sac
{transaction_type} = "DEDUCTION" and {paye_tax_category} = "GROSS" and not({allow_dedn_code} in ["RBFER2", "RBFER3", "EPAC2", "REM2", "PUCHLV", "OSTAXP"]) and not({allow_dedn_code} like "*EE1") and not({allow_dedn_code} like "*ER4")
then - {amount} else 0

 

[lbass]

Just use:

if {@groupingformula} in ["SALARIES & WAGES","EMPLOYER SGC","SUPER SAL SAC"] then {table.amt}

Place this in the detail section and then insert a summary on it.

-LB

 

[synapsevampire]

You might be over complicating this.

If the grouping is correct, then wouldn't a running total of:

if {@MyGrouping} in ["EMPLOYER SGC","SUPER SAL SAC'] then
-{amount}
else
if {@MyGrouping} = ["SALARIES & WAGES" then
{amount}
else
0

Not sure what "however am stuck in that the total I am trying to create" means, do you mean a grand total, or???

Anyway, when working with lengthy formulas, consider using additional parentheticals to clarify:

if (
{transaction_type} = "WORK" and not({time_code} in ["WC", "WC85", "MIL", "ABS", "ANNACC", "ANNCRA", "ANNDRA", "LCTACC", "LCTACD", "LCTCRA", "LCTDRA", "LSLACC", "LSLCRA", "LSLDRA", "LWOP", "MILU", "PLU", "WC80"]))
or {transaction_type} = "ALLOWANCE" and not(allow_dedn_code} in ["EGPLSD", "MILADJ", "PILOND", "VRLSD", "WCADJ", "ROLOVR"])
then {amount} else
//sgc
if
{transaction_type} = "DEDUCTION"
and
{deduction_category} = "NON EMPLOYEE"
and
{paye_tax_category} <> "GROSS"
and
not({allow_dedn_code} in ["RBFER2", "RBFER3"])
or
{allow_dedn_code} like "*ER4"
or
{allow_dedn_code} like "*EE1"
or
({allow_dedn_code} in ["EPAC2", "REM2"])
or
{transaction_type} = "DEDUCTION"
and
{paye_tax_category} = "GROSS"
and
not({allow_dedn_code} in ["RBFER2", "RBFER3", "EPAC2", "REM2", "PUCHLV", "OSTAXP"]) and
not({allow_dedn_code} like "*EE1")
and not({allow_dedn_code} like "*ER4") then
-{amount} else 0

Note that the ORs might need parentheticals around somegroup of them, as it stands, if any of them existr, then they satisfy the criteria.

-k

 

[MrBillSC]

It's not clear to me if your group formula above is in the "group expert/options/Use a formula as group name" tab or is just a formula field. I would recommend it being a formula field (@GroupFormula) that you use to group on. Then you total formula could be something like:

// formula: @AmtTotal - place in detail section
If {@GroupFormula} in ["SALARIES & WAGES ", "EMPLOYER SGC", "SUPER SAL SAC"] then ({table.amount} * -1) else 0

Then sum on the above formula.

MrBill

 

[lbass]

Oops, I missed the minus sign issue. SV nailed it with his first formula.

-LB