Conditional Sum in Report

Hello everyone, hope you can help me out. I need to do the following in a Oneworld report.

Sum "CostAmount" where "CostType" = Type1 and Type6
and Sum "CostAmount" where "CostType" = Type2 and Type5
etc...
and format it kinda like a crosstab query in MS Access

I currently have a report that looks like this;

PartNo----|Cost Amount--|CostType---|
12345-----|50.00-----------|Type1------|
12345-----|75.00-----------|Type2------|
12345-----|45.00-----------|Type3------|
12345-----|90.00-----------|Type4------|
12345-----|100.00---------|Type5------|
12345-----|25.00-----------|Type6------|

I need it to look like this;

Part------|CostAmountTotal-1and6---|CostAmountTotal-2and5--|
12345---|75.00--------------------------|175.00-----------------|

So based on the Part Number I need the report to look at the costtype and then sum the costamount accordingly.

hope you understand my gibberish and hope you can help

Thanks in Advance,
Jovix

Jovix,

Sort your main section by Part#. Add a level break footer for Part#. In the Do Section of the main section keep a manual total for the two sums:
If CostType = 1 or 6
CostType1And6 = CostType1And6 + BC Cost

If CostType = 2 or 5
CostType2And5 = CostType2And5 + BC Cost

SuppressSectionWrite

Then in the Do Section of the level break footer, output the PC Part# and the calculated costs. Then reset your cost accumulators to zero.

Hope I understood your requirement. Good Luck

Create RVs that accumulate the amounts based on the costtype, level break on
part number (totaling the RVs) and hide the accumulating section.

------------------------------------------
Daniel Bohner
ExistingLight, LLC
http://www.existinglight.net
[email protected]
mobile: 208.250.1917
------------------------------------------



Member - iConsortium
Daniel Bohner | JDE Developer
Independant-JDE/Web/Photo
www.ExistingLight.Net | [email protected]

JDE XE | SP20 | AS/400 | V5R1

JMR,

Thanks for your help, I really appreciate it. I have the bulk of the report. but I'm not sure what you mean when you say "reset the cost accumulators to zero". Can you please clarify this.

Thanks,
Gio

Gio,

No problem...by 'reset the accumulators to zero' I mean to just do a variable assignment and set them to zero so the accumulation starts over with the next part#. The accumulators being the report variables you use to total the costs.

Hope that clears it up for you.

Thank you sooo much JMR

it works perfectly. I really appreciate it.

Gio