How to create 2 RKFs in Universe

[Former Member]

Hello,

I am trying to develope 2 restricted key figures in Universe. I follwed the white paper given by Ingo.

I better explain with example;

1. Created a 'Measure1' and wrote MDX in properties with a prompt on 'Doc Type'.

And when I run the Webi, the prompt pops up and I choose a doc. type 'S1'.

Beautiful the webi report works

2. Created a second 'Measure2' and wrote MDX in properties with a prompt on 'Doc Type'

An when I run the webi, both prompts pop up i.e., 1)Prompt 1 for Doc Type in which I choose 'S1' and 2)Prompt 2 for Doc Type in which I now choose 'S2'.

Now, when I run Webi, the report doesn't show up any results

I think this is happening because it's writing an AND condition for doc. types S1 and S2. Obviously it returns no records.

I want the query to apply S1 and S2 only on each key figure(measure) not at the query level. This way I can show S1 related key figure values in one column, and S2 related key figure data in another column.

Help Please

[Former Member · Accepted solution]

Hi,

Can you provide the MDX expressions you wrote?

This would be useful to help you

Didier

[Former Member · Accepted solution]

Hi,

You can try this expression, it works (see screenshot):

<EXPRESSION>IIF ([0D_CO_CODE].currentmember.uniquename = "@Prompt('Company code','A','Company code\L01 Company code',Mono,Primary_Key,Persistent)",  
(@Select(Open order net value in statistic currency (SAP DEMO)\Open order net value in statistic currency (SAP DEMO)) , @Prompt('Company code','A','Company code\L01 Company code',Mono,Primary_Key,Persistent)), 
null)</EXPRESSION>

Regards

Didier

[IngoH]

Hi,

not sure which whitepaper you are referring to.....

You did enter these two prompts into the actual measure objects ? so you have measure 1 restricted to filter 1 and measure 2 restricted to filter 2 - correct ?

why not setting these items up in the BW query ?

Ingo

[Former Member]

Hello Ingo/Didier,

Thanks a lot replying.

We used 'OLAP Universes best practices' by Didier MAZUOE dated Dec 2009 Version 1.5

We referenced 'Mandatory filter with prompt' section with the syntax

<FILTER

KEY="[0D_DIV].[LEVEL01]"><CONDITI

ON

OPERATORCONDITION="Equal"><CONSTA

NT CAPTION="@Prompt('L01

Division','A','Division\L01

Division',mono,constrained)"/></C

ONDITION></FILTER>

Basically we replaced 0D_DIV with our characterstic name and gave appropriate prompt names. Also we tried different variations of the above like 1) 'primary key' instead of 'constrained' 2)Adding <OPERATOR VALUE="OR"> infront of <FILTER statement above etc.,

Yes Ingo. We did enter above prompts onto actual measure objects in Universe. So measure 1 is restricted to filter 1 and measure 2 is restricted to filter 2. As I said when we have only 1, the webi returns data, but when we have both of them and execute webi, then it doesnt return data. Again, the filter is on the same BW characterstic. My guess is that it is writign AND condition on filter and applying at query level.

Now, yes we can do it at BEx query level, but from BOBJ best practices point of view, our BI governance team is trying to eliminate the possibility of too many universes(to avoid 1 to 1 Bex/Universe).

[Former Member]

Hi,

The way you tried to write a restricted key figure is not the right way to do it.

In fcat you have defined a filter that applies to the whole query that means that all key figures will be impacted by this filter.

Moreover if you create a second filter with a different value, it will depends of the operator, but for sure it won't return the expected result.

You need to restrict the characteristic value for each calculated key figure: you don't need to create a filter.

So you need to create a calculated measure for each restricted key figure you want to use.

Here is the right way to do it (this is a sample):

<EXPRESSION>(@Select(Open order net value\Open order net value) , @Prompt('Company code','A','Company code\L01 Company code',Mono,Primary_Key,Persistent))</EXPRESSION>

Replace @Select to the key figure you want to use and the prompt to the characteristic you also want to use, do no forget the primary_key parameter.

Regards

Didier

[Former Member]

Didier, Is it possible to make the RKF display value only when the characteristic on which the restriction is applied is displayed if not null. For example: if we apply

a restriction on country= US to sales and then a user runs a report having Country and Sales, the Sales value for all the rows display the value for US. Is it possible to display 0 or NULL

for other countries and display just the value in the US row? Thanks.