create new object (measure) with IIF function on BW Olap universe:mdx error

[Former Member]

hello

i have a universe based on a BEX query

i want to create a new object (a measure) with the IIF(,,) function

i created the following select:

<EXPRESSION>IIF(@Select(Currency Type\CurrencyTypeCode)="10",@Select(Key Figures\RevenueGlobalCurrency),@Select(Key Figures\RevenueLocalCurrency))</EXPRESSION>

defined as number

where

CurrencyTypeCode is a dimension (character format)

RevenueGlobalCurrency and RevenueLocalCurrency are measure (number format)

i always have a parse which is OK but

when i want to use the object on a webi report i always have an MDX error : ...invalid MDX with .... (WIS 10901)

and advise ?

Thanks

[Former Member · Accepted solution]

Hii,

The Universe Designer parses everything except MDX statement enclosed in <EXPRESSION> tags.

By the way, the error is normal the correct syntax for your expression is IIF and not IF.

We have delivered with BOE XI 3.1 SP2 and MDX dictionary that fives you the MDX statements you can use in the object editor.

See SAP BW MDX functions document in attachment.

Regards,

Didier

[Former Member · Accepted solution]

Hi,

thanks for your reply.

sorry but it seems i wrote iif ....not if ...please advise.

And about pdf file, i can't open it . It seems it is corrupt.

i've sent you an email about that.

Thanks in advance,

Rgds

[Former Member · Accepted solution]

Hi,

I successfully opened the PDF using the hyperilink.

In your MDX you must replace @Select(Currency Type\CurrencyTypeCode) by one out of the 2 proposals :

[Characteristic definition].currentmember

[currency definition].currentmember.properties("[currency key]")

For instance, if I use "Statistics currency" characteristic then the expression will be:

	IIF ([0STAT_CURR].currentmember = "10", @Select(Key Figures\RevenueGlobalCurrency), @Select(Key

Figures\RevenueLocalCurrency))

	IIF ([0STAT_CURR].currentmember.properties("[20STAT_CURR]") = "10", @Select(Key Figures\RevenueGlobalCurrency), @Select(Key Figures\RevenueLocalCurrency))

The current member return the caption whereas the key property returns the key value.

Regards,

Didier

[Former Member · Accepted solution]

Thanks,

it works fine now with both proposals

And i have another question quite similar

i would like to create a new measure which is filtered on a value of a dimension

for example

i have the following numeric standard measure "REVENUE" whose select is [Measures].[4FVLHFZZRL0YFNCVUCUU9JQ9Y]

i would like to create another dedicated measure on the universe wich is the "REVENUE" above , filtered on a value of the character dimension "L01 Currency Type" whose select is [0CURTYPE].[LEVEL01]

i thought i could create a new measure "REVENUE GLOBAL CURRENCY" whose select is <EXPRESSION>@Select(Key Figures\Revenue)</EXPRESSION> but filtered on the value "10" of the <EXPRESSION>@Select(Currency Type\L01 Currency Type)</EXPRESSION>

i have seen on your OLAP universes best practice V1.5 on page 4 that there seems to be something simalar

Filtered measure / Restricted key figure: Single member : (([Measures].[0D_INV_QTY],[0CALQUARTER].[19981],[0D_DIV].[7])

but i do not know really how to use it

Thanks for your help

rgds,

[Former Member · Accepted solution]

Hi,

In fact this type of calculated measure is quite easy to write in MDX.

The syntax is:

([Measures].[Measure definition], [Dimension].[Member definition])

So to solve your expression, the correct syntax is:

([Measures].[4FVLHFZZRL0YFNCVUCUU9JQ9Y], [Currency Type].[10])

Regards,

Didier

[Former Member · Accepted solution]

Hi Didier,

i was very happy seing your answer

so i tried but it seems it does not work...

i tried to create new measures in the BW based olap universe.

I tried the 3 following syntaxes:

<EXPRESSION>[Measures].[4FVLHFZZRL0YFNCVUCUU9JQ9Y], [L01 Currency Type].[10]</EXPRESSION>

<EXPRESSION>[Measures].[4FVLHFZZRL0YFNCVUCUU9JQ9Y], [0CURTYPE].[10]</EXPRESSION>

<EXPRESSION>[Measures].[4FVLHFZZRL0YFNCVUCUU9JQ9Y],[0CURTYPE].[LEVEL01].[[20CURTYPE]].[10]</EXPRESSION>

<EXPRESSION>[Measures].[4FVLHFZZRL0YFNCVUCUU9JQ9Y],[0CURTYPE].[20CURTYPE].[10]</EXPRESSION>

and the one you sent :<EXPRESSION>Measures.4FVLHFZZRL0YFNCVUCUU9JQ9Y, Currency Type.10</EXPRESSION>

but each time i try tu use the object on the webi (rich client) report

i have twice the same error message

query1 - <universe name>

some objects are no longer available in the universe. See Your BusinessObjects Administrator (who i am) (Error WIS 00001)

if i use other measure ( for example the [Measures].[4FVLHFZZRL0YFNCVUCUU9JQ9Y] one) i have no error message.

if you can help

Thanks in advance,

Rgds

[Former Member · Accepted solution]

Hi,

The solution is whar I wrote in my previous answer:

([Measures].[4FVLHFZZRL0YFNCVUCUU9JQ9Y], [Currency Type].[10])

In your test you forgot the right an left parenthseis in the expression!

Didier

[Former Member · Accepted solution]

Hi Didier,

Thanks a lot for your help.

Now it works : i do not understand why

i explain:

the correct syntaxe of my new measure is :

<EXPRESSION>([Measures].[4FVLHG7OAJMNY9WC06X6JLOZQ], [0CURTYPE].[10])</EXPRESSION>

it does not works with my "L01 currency type" BO dimension

([0CURTYPE].[LEVEL01])

when i had :

-my universe is based on a BW BEX query

-my universe has been automaticaly generated with the assistant

-the Currency Type Class was define as below:

- 1 BO dimension object : L01 Currency Type =>

[0CURTYPE].[LEVEL01]

- 3 BO detail objects : L01 Currency Type techName =>

[0CURTYPE].[LEVEL01].[TECH_NAME]

L01 Currency Type Key =>

[0CURTYPE].[LEVEL01].[[20CURTYPE]].[Value]

L01 Currency Type Long Name =>

[0CURTYPE].[LEVEL01].[[40CURTYPE]].[Value]

i always had a wrong mdx syntax error on the webi report

i updated the class with the follow

- 1 BO dimension object : L01 Currency Type =>

[0CURTYPE].[LEVEL01] (idem)

- 3 BO detail objects : L01 Currency Type techName =>

[0CURTYPE].[LEVEL01].[TECH_NAME]  (idem)

L01 Currency Type Key =>

[0CURTYPE].[LEVEL01].[NAME] (updated)

L01 Currency Type Long Name =>

[0CURTYPE].[LEVEL01].[DESCRIPTION] (updated)

then it was working

Then i tried to reproduce from scratch and now it also works with the previous version of the class.

I do not understand anything

Is there something like a cache file of the MDX query (in RSRT, cache mode is set to inactive for the bex query) ?

ps: this is the same if a use webi under infoview (java) or webi rich client...

Edited by: Frederic Nez on Jan 19, 2010 6:35 PM

(edit : there is mistake in the post : objects are always between [] even if it is not dispay => change objects to code mode)

[Former Member · Accepted solution]

Hi Didier,

I created a dimension as

<EXPRESSION>IIF ([0MATERIAL__0MATL_GROUP].currentmember ="Consumer",1,0)</EXPRESSION>

I get the and error running WebI report

A database error occured. The database error text is: Error in MDDataSetBW.GetCellData. See RFC trace file or SAP system log for more details. (WIS 10901)

Nikhil

[Former Member · Accepted solution]

Hi,

The correct syntax must be like this:

<EXPRESSION> IIF ( [0D_MTLGROUP].currentmember.properties("[10D_MTLGROUP]") = "Consumer",1,0) </EXPRESSION>

In this sample I use Material_Group characteristic. You need to take the display attribute definition that matches the caption you want to compare.

Regards

Didier

[Former Member · Accepted solution]

Hi Didier,

Can I do a grouping as well?

Eg I want item # 1,2,3 to be grouped as "Group1", 4,5 as "Group 2" and 6.7.8.9 as "Group 3".

In SQL the universe objects definition would be similar to

IF item_num in (1,2,3) then "Group1" Else

IF item_num in (4,5) then "Group2" Else

IF item_num in (6,7,8,9) then "Group3"

Thanks,

Nikhil

[Former Member · Accepted solution]

Hi Didier,

As I mentioned in my previous post I am trying to do a grouping in the universe

I have movement type codes that I have to group.

In the "select" of the Movement Type Key (detail object) I have

[0MOVE_TYPE].[LEVEL01].[[20MOVE_TYPE]].[Value]

I created an dimension objects named "Movement Type Group" and in the "select" I have

<EXPRESSION>

IIF([0MOVE_TYPE].currentmember.properties("[20MOVE_TYPE]")="101","1","2")

</EXPRESSION>

If I include the above dimension in a report, the report runs without error, but the Movement Type Group column is empty

L01 Movement Type L01 Movement Type Key L01 Movement Type Name Movement Type Group

101 101 101

102 102 102

161 161 161

162 162 162

301 301 301

303 303 303

304 304 304

305 305 305

306 306 306

343 343 343

I changed it to

<EXPRESSION>

IIF([0MOVE_TYPE].currentmember.properties("[20MOVE_TYPE]")="101",1,2)

</EXPRESSION>

And it works. Does that mean that we canot used characters in the True & False expression.

Also, can I do

<EXPRESSION>

IIF([0MOVE_TYPE].currentmember.properties("[20MOVE_TYPE]") InList("101","102","161"),1,IIF([0MOVE_TYPE].currentmember.properties("[20MOVE_TYPE]") InList("162","301","303"),2,3)

)

</EXPRESSION>

Edited by: Nikhil Khasnis on Feb 10, 2010 8:57 PM

I changed it to

<EXPRESSION>

IIF([0MOVE_TYPE].currentmember.properties("[20MOVE_TYPE]")="101",1,2)

</EXPRESSION>

And it works. Does that mean that we canot used characters in the True & False expression.

[Former Member · Accepted solution]

Hi,

SAP BW only support numeric values to be returned in a calculated expression: this is a limitation of SAP BW engine.

So, I confirm that you cannot return string in a IIF expression.

You wrote:

Also, can I do 

<EXPRESSION>
IIF(0MOVE_TYPE.currentmember.properties("20MOVE_TYPE") InList("101","102","161"),1,IIF(0MOVE_TYPE.currentmember.properties("20MOVE_TYPE") InList("162","301","303"),2,3)
)
</EXPRESSION>

No you can't, here is the correct syntax:

<EXPRESSION>
IIF([0MOVE_TYPE].currentmember.properties("[20MOVE_TYPE]")= "101",1,
IIF([0MOVE_TYPE].currentmember.properties("[20MOVE_TYPE]")= "102",1,
IIF([0MOVE_TYPE].currentmember.properties("[20MOVE_TYPE]")= "161",1,
IIF([0MOVE_TYPE].currentmember.properties("[20MOVE_TYPE]")= "162",2,
IIF([0MOVE_TYPE].currentmember.properties("[20MOVE_TYPE]")= "301",2,
IIF([0MOVE_TYPE].currentmember.properties("[20MOVE_TYPE]")= "303",2,3))))))
</EXPRESSION>

Regards

Didier

[Former Member · Accepted solution]

Hi Didier,

This is very helpful, Thanks.

Nikhil

[Former Member · Accepted solution]

Hi Didier,

Can I get this document? I am not able to open this document you provided as attachment.

Thanks & Regards,

Peter

[Former Member · Accepted solution]

I can't access this document...can you provide an updated link?

[Former Member · Accepted solution]

I'm trying to write an if/then/else statement similar to the one below for BW OLAP Universe....Please help with the syntax....

<EXPRESSION>

IIF([/CPMB/JKDCC2E].[LEVEL01] IN ("D01","D02","D03") AND [/CPMB/JKD4N0F].[LEVEL01] = "USD",1,

IIF([/CPMB/JKDCC2E].[LEVEL01] IN ("D04","D05","D06") AND [/CPMB/JKD4N0F].[LEVEL01] = "Local Currency",2,

IIF([/CPMB/JKDCC2E].[LEVEL01] IN ("D07","D08","D09") AND [/CPMB/JKD4N0F].[LEVEL01] = "Local Currency",3,4)))

</EXPRESSION>

[Former Member]

thanks again Didier for clarifying. Looks very good.

For it to work in Xcelsius I had to use the IIF syntax and that works too, so thank.

[Former Member]

I am trying to create 2 Measures.

1. Sales

<EXPRESSION>

@Prompt('Enter Metric','N:A',{'1':'[Measures].[D78FBYNBGG6TEDTJW33N7WSBE]','2':'[Measures].[D7884TXUOXY0NSQJI01MOTQAI]','3':'[Measures].[D7884TXUOXWHAK30UX6EJYXQY]','4':'[Measures].[D7884TXUOXZK11E252WUTOIU2]','5':'[Measures].[D7884TXUOY13EA1KS5S2YJBDM]','6':'[Measures].[D78YSRTZS9JELSPBZNAU8DR2Y]','7':'[Measures].[D78YSRTZS9KXZ1CUMQ62D8JMI]'},Mono,Primary_Key,Not_Persistent)

</EXPRESSION>

2. Units

<EXPRESSION>

@Prompt('Enter Metric','N:A',{'1':'[Measures].[D78FBYNBGG7L305B7MJ9AC6L6]','2':'[Measures].[D7884TXUOXUXXBFI7UB6F457E]','3':'[Measures].[D78P4ECGEP1S75DZ4JFB0J7TM]','4':'','5':'','6':'[Measures].[D78YSRTZS9K6AF13B6QGAT5CQ]','7':'[Measures].[D78YSRTZS9MHCA0D9T1AI3C62]'},Mono,Primary_Key,Not_Persistent)

</EXPRESSION>

3. SalesA (alternative)

<EXPRESSION>

@Prompt('Enter Metric','N:A',{'1':'@Select(Key Figures\Booked_Amt)','2':'@Select(Key Figures\Gross_Sales_Amt)','3':'@Select(Key Figures\Net_Sales_Amt)','4':'@Select(Key Figures\Std_Margin_Amt)','5':'@Select(Key Figures\ASP_Amt)','6':'@Select(Key Figures\Daily_Booked_Amt)','7':'@Select(Key Figures\Daily_Gross_Sales_Amt)'},Mono,Primary_Key,Not_Persistent)

</EXPRESSION>

Query results are

time, Sales --> success

time, Units --> success

time, Sales, Units --> success (BUT both objects (Sales and Units) return results for Sales

time SalesA --> Fail

time Sales, SalesA --> success (BUT same results as query for "time, Sales, Units")

The above results are in Infoview

When generated as QAAWS and imported into xcelsius, the results are

time, Sales --> success (BUT results are user response, ie 1 or 2 or 3... 7 and not the results)

the alternative option is to create a webservice for each key figure, ie 7 x 2 (units and sales) = 14, rather than 2.

[Former Member]

Hi,

As I mentioned, the case 3 cannot work because you are using @Select in static prompts. Then @Select is not replaced by the object definition but used as a string.

Concerning your 2 queries, because your prompt question is the same for both queries they will be merged and the same answer will be sent to both queries.

In case of prompt merge only one List of Values is used. So I recommend to use different questions for the prompts

<EXPRESSION>
@Prompt('Enter Metric for Sales','N:A',{'1':'Measures.D78FBYNBGG6TEDTJW33N7WSBE','2':'Measures.D7884TXUOXY0NSQJI01MOTQAI','3':'Measures.D7884TXUOXWHAK30UX6EJYXQY','4':'Measures.D7884TXUOXZK11E252WUTOIU2','5':'Measures.D7884TXUOY13EA1KS5S2YJBDM','6':'Measures.D78YSRTZS9JELSPBZNAU8DR2Y','7':'Measures.D78YSRTZS9KXZ1CUMQ62D8JMI'},Mono,Primary_Key,Not_Persistent)
</EXPRESSION>
<EXPRESSION>
@Prompt('Enter Metric for Units','N:A',{'1':'Measures.D78FBYNBGG7L305B7MJ9AC6L6','2':'Measures.D7884TXUOXUXXBFI7UB6F457E','3':'Measures.D78P4ECGEP1S75DZ4JFB0J7TM','4':'','5':'','6':'Measures.D78YSRTZS9K6AF13B6QGAT5CQ','7':'Measures.D78YSRTZS9MHCA0D9T1AI3C62'},Mono,Primary_Key,Not_Persistent)
</EXPRESSION>

Didier

[Former Member]

Thanks Didier,

it's worked perfectly,

however, when I use the @Select syntax, it doesn't. The results are very odd. it only works if I use the object that has the same definition without the @Select syntax in the query as well. very odd.

Also odd is if I use multiple objects defined with @Prompt('Select Metric') in the same query, they all return the same value; ie the results of the frist object. But I get the expected values for each if I run them independently.

The bad part is that in the end I can't benefit from the "dynamic measure" because, although the preview in "Query as a web service" displays the correct data... when it's imported into Xcelsius, it returns the user response and not the data.

Are these findings true, common or am I missing steps?

[Former Member]

Hi,

In a prompt definition with static values like in may sample you can't use @Select expression.

Again in the sample I provided, the prompt can be interpreted as a case when else, so you can't select multiple values.

So maybe I didn't understand what you wan to achieve.

So if you can send concrete samples with screenshot, I can help to solve your problem.

Regards,

Didier

[Former Member]

is it possible to write nested IIF expressions? All or does it only evaluate to 1 or 0?

eg

<EXPRESSION>

IIF(@Prompt('Enter Metric','N',,mono,free) = 1,

@Select(Booked_Amt\Booked_Amt),

IIF(@Prompt('Enter Metric','N',,mono,free) = 2,

@Select(Actual_Sales_Amt\Gross_Sales_Amt),

@Select(Key Figures\ASP_Amt)

))

</EXPRESSION>

[Former Member]

Hi,

Of course you can wirte such expression.

By the way you can alo write a similar expression using a single prompt:

<EXPRESSION>@Prompt('Enter Metric','N:A',{'1':'[Measures].[Measure 1]','2':'[Measures].[Measure 2]','3':'[Measures].[Measure 3]'},Mono,Primary_Key,Not_Persistent)</EXPRESSION>

Regards,

Didier