Deriving text from a key figure comparision

[Former Member]

Hi SDN Gurus,

I have a situation where I have to compare two key figures and depending upon the results, show results in a column as text, such as: 'Below Minimum', 'Within Range' and 'Above Maximum'.

Any suggestion how to handle this?

Thanks.

SM

[ravikanth_indurthi · Accepted solution]

Hi Shahid,

One way of achieving this is, include a new infoobject in the cube and write a routine in update rules / Transformations such that it fills the required Texts depending on the Key figures comparisons/ calculations.

Hope this helps............

Rgs,

Ravikanth.

[Former Member · Accepted solution]

True, but that is what I am trying to avoid. This would mean that I do all the simple calculations in the start routine instead of the query.

I am looking for a Query Designer solution if possible.

Thanks for the suggestion though.

SM

[Former Member]

Can you use an Exception? It's color based, so you can change the colors depending on the comparison value.

Alternatively, you could use the boolean logic to set a value of -1 for below, 0 for same and +1 for above -- like this:

((kyf1 - kyf2 ) < 0 * -1) + ((kyf1-Kyf2)>0 * 1)

If kyf1 > kyf2 , then you get 1 (second half of equation). If kyf1<kyf2, you get -1 (first half). If they are equal, you get 0.

It's not textual, but it works. When coupled with an exception you can visually show green, red or yellow for the values.

Brian

[Former Member]

Thanks.

I thought about it but the customer is insisting on seeing text.

SM

[Former Member]

Good Luck then, because I know of no way to put text into a key figure.

Brian

[Former Member]

Brian,

Do you think text variables would be of some help to convert -1, 0 and +1 to text?

Thanks.

SM

[Former Member]

I personally, have never used a formula variable to get text inserted into a key figure.

Thinking about it a little more ... I have a solution ... it's not pretty, but it will work. You can write a bit of VB code to go through the key figure column and swap out the numbers for texts.

It's been a few years (back in 2002) since I wrote VB code in Excel for BEx, and I can't seem to remember how to get at the sub-routines that allow you to call your own routines after a refresh. I think SAP has locked this feature down. You can always add a button that will do the exchange. I know it's not elegant, but it's a step closer .... I used to add a button to refresh the query and then run my sub-routine, but I can't seem to find those routines anymore. I think bex refresh routine was sapbexqueryrefresh or something like that.

Brian

[Former Member]

Hi Shahid,

Like Brian suggested, here if you are doing this in BEX Excel then upon Query Refresh you can execute a VBA Macro to get your Text. Will be Hard to do it on WEB Unless you are on 3.5 then you can try to use the Table Interface, and change the HTML before its Rendered.

I will have to find a Table Interface Example.

But in Excel, for 3.5 you can insert VBA code in SAPBEX Module ( this is inserted automatically )

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)
     'Write Code here. 
End Sub

Hope this helps.

Datta.