CODEWITHSUNDEEP
business-objects
radi8
radi8

Reputation: 526

SAP BO - Compare values between rows and conditionally display

I have a request where I need to create an integrity report where I am looking at sales data and verifying that the payment terms are consistent on all items (rows).

Sample data:

Sales # | Line # | Terms Code 
100     | 1.0    |  N90
100     | 2.0    |  N90
101     | 1.0    |  N60
101     | 2.0    |  P45
101     | 3.0    |  N60

Notice that on Sales Order 101, line 2.0 has a different terms code. I need to detect that and only display order 101 on the report.

I was looking at using the PREVIOUS operator in a variable like this:

=If(Previous([Payment Terms Code])=[Payment Terms Code]) Then 0 Else 1

but that looks at the Order 100 line 2.0 and would flag it incorrectly.

I am not sure how I can do this, but maybe using the IN operator some how. Suggestions?

Upvotes: 1

Views: 6435

Answers (2)

DocZerø
DocZerø

Reputation: 8557

The syntax for the Previous() function is:

Previous(dimension|measure|Self [;Row|col][;(reset_dims)][;offset][;NoNull])

The third parameter, reset_dims allows you to specify when the list of dimensions used to reset the calculation.

Thus, your formula would then be:

Previous([Payment Terms Code]; ([Sales #];[Line #]))

Notes:

  • You must always place dimensions in parentheses even if there is only one dimension in the list of reset dimensions.
  • When you specify a set of reset dimensions you must separate them with semi-colon

Upvotes: 1

Joe
Joe

Reputation: 6827

So is appropriate to describe the requirement as you need to see any Sales Orders that have more than one distinct terms code? If so, you can accomplish it like this:

Create a new variable:
Terms Count =Count([Terms Code]) in ([Sales #])

Add this object to the report. You should see "1" for all of the 100 records, and "2" for the 101 records.

Finally, add a filter to the report on Trans Count > 1.

Upvotes: 1

Related Questions