Summerized Fields input

[Crystalfire]

if Sum ({@new amount}, {@NEW TEST GROUP})=0 then 0
else Sum ({@Total }, {@NEW TEST GROUP})/ Sum ({@new amount}, {@NEW TEST GROUP})*100

This formula shows the % of gross profit I need to see on my report. Its work fine but I cannot insert it into a crosstab I need to create. It does not show up in the created formulas list for the summerized field section.

 

[rosemaryl]

Dear Crystalfire,

Anytime a formula doesn't appear, it meas that it is a WhilePrintingRecords formula.

Look at the help file for Multi-pass to get an understanding of what Crystal does when.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lbass]

What version of CR are you using? The solution is dependent on that. Also, you should always show the contents of nested formulas in your posts, as this can affect the solutions presented.

-LB

 

[Crystalfire]

8.5

 

[lbass]

And your nested formulas?

-LB

 

[lbass]

Assuming you have a row in the crosstab based on {@NewTestGroup}, try creating the following formula {@grossprofit} exactly like this:

if {@Total} > 0 then
{@Total} % {@NewAmount} else 0

Insert {@grossprofit} as a summary field in your crosstab and choose "weighted average" as the summary, and choose {@NewAmount} as the field to weight by. While in preview mode, select the new summary and click on the % icon in the toolbar in order to add the percent sign.

Unless there are some surpises in your nested formulas, I think the above should work.

-LB

 

[Crystalfire]

Assuming you have a row in the crosstab based on {@NewTestGroup}, try creating the following formula {@grossprofit} exactly like this:
I do but this formula still dosen't show me the % I am looking for as my original formula shows.

 

[lbass]

Please supply the contents of all three nested formulas. Also please show a sample of the results you got when you tried my suggestion.

-LB

 

[Crystalfire]

Using the formula you posted gives me a number such as 79.5. I need to see the % amount as stated in my formula below.


if Sum ({@new amount}, {@NEW TEST GROUP})=0 then 0
else Sum ({@Total }, {@NEW TEST GROUP})/ Sum ({@new amount}, {@NEW TEST GROUP})*100


@new Amount =
If {RECEIVABLE_DIST.AMOUNT_TYPE} like "CR" and {RECEIVABLE.TYPE}="M"then -{RECEIVABLE_LINE.AMOUNT} else
If {RECEIVABLE_DIST.AMOUNT_TYPE} like "CR"then {RECEIVABLE_LINE.AMOUNT} else
If {RECEIVABLE_DIST.AMOUNT_TYPE} like "DR"and {RECEIVABLE.TYPE}<>"M" then {@Rec Amount Negate}
else -{@Rec Amount Negate}

@ new Test Group =
if {PART.PRODUCT_CODE}="SF" or {PART.PRODUCT_CODE}= "PT SF" then "SF" else
if {PART.PRODUCT_CODE}="QF" or {PART.PRODUCT_CODE}= "PT QF" then "QF" else
if {PART.PRODUCT_CODE}="DDF" or {PART.PRODUCT_CODE}= "PT DDF" then "DDF " else
if {PART.PRODUCT_CODE}="DDF PERI" or {PART.PRODUCT_CODE}= "PT DDF PERI" then "DDF PERI" else
if {PART.PRODUCT_CODE}="LFS" or {PART.PRODUCT_CODE}= "PT LFS" then "LFS " else
if {PART.PRODUCT_CODE}="LP" or {PART.PRODUCT_CODE}= "PT LP" then "LP " else
if {PART.PRODUCT_CODE}="POD" or {PART.PRODUCT_CODE}= "PT POD" then "POD " else
if {PART.PRODUCT_CODE}="SP07" or {PART.PRODUCT_CODE}= "PT SP07" then "SP07 " else
if {PART.PRODUCT_CODE}="MPP" or {PART.PRODUCT_CODE}= "PT MPP" then "MPP " else
if {PART.PRODUCT_CODE}="UTFS" or {PART.PRODUCT_CODE}= "PT UTFS" then "UTFS " else
if {PART.PRODUCT_CODE}="SST" or {PART.PRODUCT_CODE}= "PT SST" then "SST " else
if {PART.PRODUCT_CODE}="MPSA" or {PART.PRODUCT_CODE}= "PT MPSA" then "MPSA " else
if {PART.PRODUCT_CODE}="MPF" or {PART.PRODUCT_CODE}= "PT MPF" then "MPF " else
if {PART.PRODUCT_CODE}="MPDL" or {PART.PRODUCT_CODE}= "PT MPDL" then "MPDL " else
if {PART.PRODUCT_CODE}= "SP" or {PART.PRODUCT_CODE}= "PT SP" then "SP " else
if {PART.PRODUCT_CODE}="MPU" or {PART.PRODUCT_CODE}= "PT MPU" then "MPU " else
if {PART.PRODUCT_CODE}="ULCF" or {PART.PRODUCT_CODE}= "PT ULCF" then "ULCF " else
if {PART.PRODUCT_CODE}="LFF" or {PART.PRODUCT_CODE}= "PT LFF" then "LFF " else
if {PART.PRODUCT_CODE}="PT LABEL" or {PART.PRODUCT_CODE}= "PT PT LABEL" then "PT LABEL" else
if {PART.PRODUCT_CODE}="OTHER" or {PART.PRODUCT_CODE}= "PT OTHER" then "OTHER " else
if {PART.PRODUCT_CODE}="OTHER FEEDER" or {PART.PRODUCT_CODE}= "PT OTHER FEEDER" then "OTHER FEEDER " else
if {PART.PRODUCT_CODE}="USVIBRA" or {PART.PRODUCT_CODE}= "PT USVIBRA" then "USVIBRA " else
if {PART.PRODUCT_CODE}="TOOLINGASSET" or {PART.PRODUCT_CODE}= "PT TOOLINGASSET" then "TOOLINGASSET " else
if {PART.PRODUCT_CODE}="TOOLASSET" or {PART.PRODUCT_CODE}= "PT TOOLASSET" then "TOOLASSET " else
if {PART.PRODUCT_CODE}="SFC" or {PART.PRODUCT_CODE}= "PT SFC" then "SFC " else
if {PART.PRODUCT_CODE}="WIG" or {PART.PRODUCT_CODE}= "PT WIG" then "WIG " else
if {PART.PRODUCT_CODE}="SSF" or {PART.PRODUCT_CODE}= "PT SSF" then "SSF " else
if {PART.PRODUCT_CODE}="SPLICING" or {PART.PRODUCT_CODE}= "PT SPLICING" then "SPLICING " else
if {PART.PRODUCT_CODE}="M3P" or {PART.PRODUCT_CODE}= "PT M3P" then "M3P " else
if {PART.PRODUCT_CODE}="CONDUCT TRAY" or {PART.PRODUCT_CODE}= "PT CONDUCT TRAY" then "CONDUCT TRAY " else
if {PART.PRODUCT_CODE}="DDF2" or {PART.PRODUCT_CODE}= "PT DDF2" then "DDF2 " else
if {PART.PRODUCT_CODE}="DDF2 PERI" or {PART.PRODUCT_CODE}= "PT DDF2 PERI" then "DDF2 PERI " else
if {PART.PRODUCT_CODE}="LFU" or {PART.PRODUCT_CODE}= "PT LFU" then "LFU " else
if {PART.PRODUCT_CODE}="LFU OTHER" or {PART.PRODUCT_CODE}= "PT LFU OTHER" then "LFU OTHER " else
if {PART.PRODUCT_CODE}="M3P BLOCKS" or {PART.PRODUCT_CODE}= "PT M3P BLOCKS" then "M3P BLOCKS " else
if {PART.PRODUCT_CODE}="MFG MOSSMAN" or {PART.PRODUCT_CODE}= "PT MFG MOSSMAN" then "MFG MOSSMAN" else
if {PART.PRODUCT_CODE}="MOSSMAN" or {PART.PRODUCT_CODE}= "PT MOSSMAN" then "MOSSMAN " else
if {PART.PRODUCT_CODE}="MPP" or {PART.PRODUCT_CODE}= "PT MPP" then "MPP " else
if {PART.PRODUCT_CODE}="PROAUTO" or {PART.PRODUCT_CODE}= "PT PROAUTO" then "PROAUTO " else
if {PART.PRODUCT_CODE}="PRODINSTALL" or {PART.PRODUCT_CODE}= "PT PRODINSTALL" then "PRODINSTALL " else
if {PART.PRODUCT_CODE}="SSFTL" or {PART.PRODUCT_CODE}= "PT SSFTL" then "SSFTL "

@total =
If isnull({@Commission Amount})
and {@new amount}<0 then {@new amount}- {@Toal Unit Cost}
else if isnull ({PART.ID})then {@new amount}
else If isnull({@Commission Amount})
then {@new amount}-{@Toal Unit Cost}
else if isnull ({@Toal Unit Cost}) then {@new amount}-{@Commission Amount}
else ({@new amount}-{@Toal Unit Cost})-{@Commission Amount}