Suppress on Condition

[YANKRAY]

Using CR 10.0, Oracle database.

I am pulling details based on an action code of "S".
There is a transaction code that creates an "R" action code, but is needed to remove its associated "S" transaction.

This is the information in the table:

Item PO action tier price Transaction
123 1234 S 100 5.00 MRLE
123 1234 S 101 10.00 MRLE
123 1234 S 102 10.00 MRLE
123 1234 S 103 10.00 MRLE
123 1234 R 100 5.00 AQAP
123 1234 S 100 10.00 AQAP

I pull only the S action items.
All transaction are valid.
The Tier 100 AQAP transaction are to correct the Tier 100 MRLR transaction.

I am trying to negate the Tier 100 MRLE/AQAP transaction where the price is the same.

I want my result to look like:

Item PO action tier price Transaction
123 1234 S 101 10.00 MRLE
123 1234 S 102 10.00 MRLE
123 1234 S 103 10.00 MRLE
123 1234 S 100 10.00 AQAP

Thanks,
Ray

 

[synapsevampire]

Group by the PO, then the Item, then by TIER, then sort by the Transaction (this assumes that you can allow this ordering).

In the suppress formula of the details section try:

previous({table.item}+{table.po}+{table.tier}) = {table.item}+{table.po}+{table.tier}
and
(
previous({table.transaction}) = "AQAP"
and
{table.transaction} = "MRLE"
)
and
previous({table.price}) = {table.price}

This should get you very close.

-k

 

[YANKRAY]

I am receiving the error:

"a string is required here" at the first {table.tier}

previous({table.item}+{table.po}+{table.tier}) = {table.item}+{table.po}+{table.tier}
and
(
previous({table.transaction}) = "AQAP"
and
{table.transaction} = "MRLE"
)
and
previous({table.price}) = {table.price}

 

[synapsevampire]

That means that one (or more) of the fields I've concatenated are of a diffferent type, the cheat is to use a &.

Try:

previous({table.item}&{table.po}&{table.tier}) = {table.item}&{table.po}&{table.tier}
and
(
previous({table.transaction}) = "AQAP"
and
{table.transaction} = "MRLE"
)
and
previous({table.price}) = {table.price}

-k

 

[YANKRAY]

Now I get the message:

"a field is require here"
with the first previous group highlighted.

previous({WIP_MATL_BOOKING.COMPONENT_NO}&{WIP_MATL_BOOKING.LOT_NUMBER}&{WIP_MATL_BOOKING.COST_TIER})
={WIP_MATL_BOOKING.COMPONENT_NO}&{WIP_MATL_BOOKING.LOT_NUMBER}&{WIP_MATL_BOOKING.COST_TIER}
and(previous({WIP_MATL_BOOKING.SOURCE})="AQAP" AND
{WIP_MATL_BOOKING.SOURCE} = "MRLE") AND
PREVIOUS({WIP_MATL_BOOKING.COST_EA}) = {WIP_MATL_BOOKING.COST_EA}

 

[YANKRAY]

I was able to suppress the records with this:

previous({WIP_MATL_BOOKING.SOURCE}) = "AQAP"

Only problem is the suppressed records amounts are still in my totals.

Is there a way to Select records based on this condition?

The grouping you gave me works perfectly, but I need records that are suppressed to be ignored in the totals.

 

[Naith]

Use your condition to populate a counting/summing variable.

e.g.
whileprintingrecords;
if
previous({table.item}&{table.po}&{table.tier}) = {table.item}&{table.po}&{table.tier}
and
(
previous({table.transaction}) = "AQAP"
and
{table.transaction} = "MRLE"
)
and
previous({table.price}) = {table.price}
then numbervar counter := counter + 1;

Reset the variable in the group header if necessary.

Naith