SAP BI Query with specifiy logic !

[pavneet_rana]

Hi to all,

In need to develop new SAP BI query with below logic.

I have 3 field in query Equipment, Contract, contract start date and end date.

-> Now or each equipment search for a contract with start date in the past and end date in the future. If non exists show
'None', blank or 'N/A' in query.

-> If there is more than one active contract show the highest
e.g. P1-5>P2-4>P3-3

P1-5 in query.

Please can any one provide some light, how to do same in SAP BI Query

[john_hawk]

Hi Pavneet,

What key figure are you using?

All queries should contain a key figure.

What kind of InfoProvider are you using for your query?

My approach would be a MultiProvider built on InfoObject Equipment and the InfoCube or DSO where Contracts are stored.

This will allow you to get all Equipment records, whether or not a contract exists.

In the query, be sure to include "No. of records" key figure, hiding it if desired. This will insure all Equipment values are selected.

You will need to a a key figure from the Contract DSO or InfoCube also, hiding it if desired.

Create a calculated key figure labled 'Status' with Boolean logic

(key figure from the Contract DSO < 0 ) * 1 ---- Will return 0 when there is no contract.

---- will return 1 when a contact exists.

Use this Status it identify equipment without contracts.

Use Exception aggregation on the key figure from the Contract DSO or InfoCube, using Contract and use Aggregation type LAST to get the latest Contract.

Let me know if this helps, and let us know your final solution.

John Hawk