Conditionally suppress records based on calculated member - Crosstab

[Former Member]

I am building a fairly simple exception report which should show any sales orders/items where the net price on a sales order item does not match the customer's expected price.  I have a calculated member field which displays the difference in the prices.   I am struggling to suppress any records where this value = 0.

Report details:   The tables used are VBAK (sales order header) and KONV (sales document pricing condition data).  My crosstab has two rows VBAK.VBELN (Sales Document Number) and KONV.KPOSN (Item Number).   It has a single column KONV.KSCHL (Pricing Condition Type).   The summarized field is Sum of KONV.KWERT (Pricing Condition Value).   The calculated member of this column is the difference between SAP net price (condition type PNTP) and the customer expected price (condition type JED1).

Example output:

Sales Order     Item          Expected Price          Net Price          Difference

2842207              10         3,572.59                    3,572.59            0.00

2842207              20         4,127.32                    5,128.71            -1,001.39

In the example above, I would NOT want the first record to show, because the value of "Difference" is 0.00 (the Difference field is a calculated member).  I am relatively new to Crystal Reports, and would appreciate your ideas and detailed steps on how to accomplish this task.

Thanks in advance.

-Dan

[abhilash_kumar]

Hi Daniel,

You cannot conditionally suppress rows/columns off of a Calculated Member - I wish this feature existed however, it doesn't yet.

Your workaround is to not use a Crosstab but to use groups and summaries. Here's what you need to do:

1) Insert a Group on the VBAK.VBELN field

2) Insert a Group on the KONV.KPOSN field

3) Suppress Group Header #1 and Group Footer #1

4) Move the Sales Order and Item Number fields to Group Header #2

5) Create a formula (@Exp_Price) with this code:

If {KONV.KSCHL} = 'Expected Price' then

     KONV.KWERT

6) Create a formula (@Net_Price) with this code:

If {KONV.KSCHL} = 'Net Price' then

     KONV.KWERT

7) Go to the 'Insert Summary' option on the toolbar > Choose @Exp_price as the 'Field to Summarize' > Choose 'Sum' as the summary function > Under 'Summary Location' choose 'Group #2'.

Move the summary from Group Footer #2 to Group Header #2

😎 Repeat step 7 for @Net_Price formula

9) Create another formula (@Difference) with this code:

Sum({@Exp_Price}, {KONV.KPOSN}) - Sum({@Net_Price}, {KONV.KPOSN})

Place this formula field beside Net Price summary on Group Header #2

10) And lastly, go the Report Menu on top > Selection formulas > Group and add this code:

Sum({@Exp_Price}, {KONV.KPOSN}) - Sum({@Net_Price}, {KONV.KPOSN}) <> 0

11) Suppress the Details Section

I hope this helps.

-Abhilash

[Former Member]

Hi Daniel, the following steps might help in solving your problem:

1. Create a new VBELN-Formula or KONV-Formula formula for field that is used in cross tab rows and you want it to suppress like

VBELN-Formula:

  if({@Difference} > 0) then (

      {VBAK.VBELN}

  )

KONV-Formula:

  if({@Difference} > 0) then (

      {KONV.KPOSN}

  )

2. Remove the {VBAK.VBELN} or {KONV.KPOSN} field from the cross tab.

3. Now instead of using direct field {VBAK.VBELN} or {KONV.KPOSN} you should go with @VBELN-Formula or @KONV-Formula.

4. On preview it will remove all rows with {@Difference} less than and equal to 0.