cancel
Showing results for 
Search instead for 
Did you mean: 

BEx Fixed Unit of measure display in a Formula

ssurampally
Active Contributor
0 Kudos

Hi,

I have got a formula in BEx Query, it has an if else condition to display the value. the first part is when if condition is true,  display the value in a keyfigure else display fixed value 100%.

The displaying keyfigure is created in back end, it is defined with % as Fixed unit of measure. So, the first part of the condition is working fine, the else part I am unable to display the constant value as '100%',  I am wondering how can define fixed unit of measure in BEx formula for a constant value?

Do you know, how can I display it?

Thanks

Sreekanth

Accepted Solutions (1)

Accepted Solutions (1)

ssurampally
Active Contributor
0 Kudos

Hi, Actually, I am not expecting a constant value in that formula, as I said earlier, I have a formula defined with if else condition, the else part is to get 100 %. I can put the formula to display 100 but not 100%, I am wondering is there any I can show 100% in the report output.

Former Member
0 Kudos

Hi sreekanth,

can you post your bex formula ( if else) here? or may be an example?

I can put the formula to display 100 but not 100%, I am wondering is there any I can show 100% in the report output.


sorry, I didn't get you here, do you mean you are not getting value as 100% ?



If I understand your question correctly,


if <conition true>  result KF1 else <condition false> result should be 100% ?


create new formula  ex: KC1 (as mentioned in previous post ) for constant value , then your if else formula should be like below


<condition true> * KF1 + <condition false> * KC1


KC1 gives the constant value as 100%

Message was edited by: Jyothi velpula

ssurampally
Active Contributor
0 Kudos

ya, you understood correctly. I will create a new formula to populate 100% and hide it. Then use that formula in the else condition, I will let you know the result of it, Thanks.

ssurampally
Active Contributor
0 Kudos

also, there is another problem with this formula,

Interest rate,

  if (FV_Company Code = XXXX) then ZInterest

     else,

  100%.

  endif.

In this, FV_company code is a formula variable created on Company code char as replacement process type, if company code char is not in the drill down of the report, then the formula is not working, it is displaying as X.

Users do not want company code char in the default drill down, it has to be in free characteristcs section only,  What should I do to make the formula work, though the company code char is not in drill down.

Former Member
0 Kudos

Hi,

in that case, create a dummy key figure in target --> then create a selection /restricted key figure (ex: RK_compcode) with company code and that dummy key figure.

now use this newly created selection/restricted key figure in your formula (if RK_compcode = XXXX)

Psnote: this will still bring X values if the company code is in free char.

this formula works only when user drag and drop company code in rows.

ssurampally
Active Contributor
0 Kudos

Then it would not help anyway to me, I am looking for an option to use a formula with replacement patch variable on a Characteristic company code, but not having that char in the report drill down.  Let me know if that can be achieved by anyway.

also what you said to get 100% value in the formula would not work, I have created a dummy keyfigure with a formula - 0 / 100 , it gives me 100% for that column, but when I used in the main formula, it is still showing as 100. So there is no use of doing it.

Otherwise, I will convince the user to adjust the worksheet with this new column in spread sheet.

Loed
Active Contributor
0 Kudos

Hi,

Did you already get the 100% part? If not, you use this formula in your ELSE statement..

1 %A 1

It will give you the result of "100%"..

About your 2nd problem, is your company code a NUMBER or LETTERS?

If it's a number, we can use a replacement path to make it as number then we can do an IF statement..Else, you put your company code in the drill down but in display just choose HIDE..

Regards,

Loed

ssurampally
Active Contributor
0 Kudos

I am able to resolve the 2nd problem. I have kept the char in drill down but selected 'No display option'. It is working fine. Thank you.

The first issue,  getting 100%,  even with your formula also, it did not work.  I can display the keyfigure as 100% in a new column with the formula 1 %A 1. But when I used that keyfigure in another formula, it is still taking only value of 100, not the 100%.  Because % is a dimension of a keyfigure, I think I can only get it from the back end, it seems.

May be, I have to write a customer exit to read as Virtual keyfigure value for it.

ssurampally
Active Contributor
0 Kudos

Hi All,

any help on % dimension issue. With this reason, the formula is giving an error message that, it is not correctly defined. However the report output is fine.  if there is no solution to it, then I have got following 2 options,

1. Suppressing the error message, assuming that, the report would still give the correct values.

2. Creating virtual keyfigure in the cube with % as fixed dimension, populate that using Customer Exit in reporting.

Please share your ideas, if there is any other way to handle this situation. thank you in advance.

Sreekanth,

Loed
Active Contributor
0 Kudos

Hi,

If there is an error message then your formula is really incorrect..Suppressing the error message won't help..Please post the formula with error message..

Regards,

Loed

ssurampally
Active Contributor
0 Kudos

Ok Loed,  here is the details of the issue,

formula for net interest before,      if Net Interest actual = 0 then take Net interest Budget  else

                                                   if net Interest actual <> 0 then take Net interest actual

Net interest Actual and  Net Interest Budget are 2 standard keyfigures in the back end with unit of measure as %. so the values be 50% or 60% etc..

It was working fine before,  the problem started when I changed the formula definition of it,

now this formula is changed as,  if net Interest actual = 0,  and Net Interest Budget = 0 then take '100 '  else  if net interest actual <> 0 then net interest actual.

With this change, there is a chance that, Net Interest can get 100 as value which is a number without a dimension,  whatever the ways I tried to display the 100% in this formula it did not work.  I tried all the suggestions above in this thread, I am able to display 100% by creating a new column but when I used the new column in this formula, it is not taking % from there, because it is not a unit of measure dimension associated with it.

So that, the formula definition is giving warning message, it is not an error.

the error is ,  Element Net Interest is not correctly defined, please check the query definition,

the definition warning says, one of the if condition result is giving % value value and other one is giving just the number, which is 100.

Option I am going with is,  assuming that it is a warning message, suppressed it in RSRT,  the query is executing fine. also checked the values, they are good.

Please let me know, if that works fine... also thank you very much for your interest in assisting on this issue.

Thanks

Sreekanth

Loed
Active Contributor
0 Kudos

Hi,

You said that you were able to create 100% in new column, right? Let's call your new column as KF_NEW_COLUMN..

Did you already try this formula?

(NET_INT_ACTUAL = 0 AND NET_INT_BUDGET = 0) * KF_NEW_COLUMN + ((NET_INT_ACTUAL <> 0) * NET_INT_ACTUAL)

Regards,

Loed

ssurampally
Active Contributor
0 Kudos

Yes Loed, I have tried it,  new column can display the % associated with number, but if it is used in other formula it would result in 100, 

finally we convinced the user to display 100 for some records and some would display as 100% format, it is fine now.

Thanks you very much all the help you provided, highly appreciated your effort.

Answers (3)

Answers (3)

former_member183519
Contributor
0 Kudos

Hello Sreekanth,

pls share ur formula.

yes , in some cases else part will not work.So instead of else part u can make it to if condition in formula.

ex. suppose if your formula is like: PO = 100 then 1 else 0.

so instead of using PO ='100' * 1 + 0

u can use              (PO = '100') *1 + (PO <> '100') *0

check this workaround .

Regards,

Hitesh Gavande

Former Member
0 Kudos

Hi,

You can find a very useful doc (for the first way, BEx) following this link https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sapportals.km.docs/documents/a1-8-4/how to report data in alternate units of measure

Otherwise, if for you it's not necessary to have the indication of the UoM with your amount

On the other hand, you can use the update rules to convert your UoM when you load data in the infoprovider !

Former Member
0 Kudos

Hi Sreekanth,

you can create a new formula for constant value like below

- (0 % 100)

this will give you the constant value as 100% for each row. In the else place, use this column to populate.