Conditions In BEX Query Designer

[former_member206475]

Hi Users,

I have a scenario to be implemented in my queries.

Scenario 1 -

I have a Characteristic field F1.

I have to create a coloum C1 in report display result which populates the below condition:

If F1 = '12345' then C1 = YES

ELSE it should be blank.

Scenario 2:

I have two date fields D1 and D2 with their corresponding KPI fields DV1 and DV2.

DV1 is for D1 and DV2 is for D2.

First D1 is compared with D2 and whichever is latest date then its corresponding field should be populated and other should not.

Example if D1 is 01.04.2014 and D2 is 22.04.2014 and If DV1 is 100 and DV2 is 200

Then only DV2 should be populated and DV1 should be empty.

Can anyone please suggest?

Regards

Syed

[anshu_lilhori · Accepted solution]

Two things to note before we move to solutions.

1. You need to convert all the dates(D1 and D2)and characteristic (F1) fields into Keyfigure with the help of formula variable with replacement path which are to be used in if else condition for comparison.
2. You cannot populate string (YES) at Bex lsevel so instead of that you may use number if that suits your requirement.

Now once you have converted the same then write the condition as below:

Scenario 1 -
I have a Characteristic field F1.
I have to create a coloum C1 in report display result which populates the below condition:
If F1 = '12345' then C1 = YES
ELSE it should be blank.

(FV1==12345)*1+(FV1<>12345)*0

For Scenario 2:

(D1>D2)*DV1+(D1<D2)*DV2

For converting the char into kfs refer the below document:

Hope this helps.

Regards,

AL

[former_member206475 · Accepted solution]

Hi Anshu,

For scenario 1 can i have a blank instead of a zero?

if user wants a Yes then can this be achievable via exit?

[former_member183777 · Accepted solution]

For Scenario1, you can create a selection called "Yes" and defined as F1 = '12345'.

[anshu_lilhori · Accepted solution]

Yes in query property settings you have option of displaying zero as space.But this will show all zeros as space in the query output.

It cannot be populated through exit..You can create a work based on this query and then you may populate the value as desired.

Regards,

AL

[former_member206475 · Accepted solution]

How can i get a Yes to be populated by selection? I dont think so.

[anshu_lilhori · Accepted solution]

Yes even i think the same it will just display in heading but not in the cell as desired.

Regards,

AL

[former_member183777 · Accepted solution]

Sample Output

Query where Yes is defined as Some char = some number and the other blank selection is defined as Same char <> same number as in Yes

[former_member206475 · Accepted solution]

Hi Anshu,

I could create D1 and D2 as key figures using formula variable.

But the DV1 and DV2 which are the values for D1 and D2 I am not able to find the DV1 and DV2 in replacement path object list while creating formula variable. Not sure why. I checked but its not there.

Not sure what to do. DV1 and DV2 are attributes of a master data object.

[former_member206475 · Accepted solution]

Hi Anshu,

i have created two formulas to convert the characteristics to key figures using formula variable.

It works but i have one problem.

The characteristics DV1 and DV2 show values like this:

DV1 = 100USD

DV2 = 200USD

The two formulas i created show the above values as:

F1 = 100USD

F2 = 100 and USD is shown in top of the coloum in report display. I want the currency in every cell like for F1 but F2 shows on top of coloum just one time.

I checked properties and both have same.

[former_member206475]

Hi,

Have resolved the case..

thank you for all your help..

assigning points..

[former_member182470]

Hi Syed,

Scenario 1:--

My take on this requirement would be "Virtual Char" concept for your Scenario 1. As you just want to populate text of YES in the report directly by based on a condition, you can write a small BADI code to populate Yes.

Step by step is below:--

Scenario 2:-- Convert both dates into KFs.

DV1 KF definition(New Formula) : ( D1 KF > D2 KF ) * DV1 + 0

DV2 KF Definition(New Formula) : ( D2 KF > D1 KF ) * DV2 + 0

So that dynamically your DV1 and DV2 will get populated.

Regards,

Suman

[former_member206475]

Hi Suman,

I could create D1 and D2 as key figures using formula variable.

But the DV1 and DV2 which are the values for D1 and D2 I am not able to find the DV1 and DV2 in replacement path object list while creating formula variable. Not sure why. I checked but its not there.

Not sure what to do. DV1 and DV2 are attributes of a master data object.

Re: Conditions In BEX Query Designer

[former_member182470]

Have you followed this doc?

Convert a Characteristic into a Key Figure (BEx)

You can select replace with Attribute in details tab of the Formula variable-->Replacement path-->Reference char as main Master data Infoobject.

[former_member206475]

Hi Suman,

Got it..

i have created two formulas to convert the characteristics to key figures using formula variable.

It works but i have one problem.

The characteristics DV1 and DV2 show values like this:

DV1 = 100USD

DV2 = 200USD

The two formulas i created show the above values as:

F1 = 100USD

F2 = 100 and USD is shown in top of the coloum in report display. I want the currency in every cell like for F1 but F2 shows on top of coloum just one time.

I checked properties and both have same.