Question Re: create new object (measure) with IIF function on BW Olap universe:mdx error in Technology Q&A

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

Former Member — Thu, 14 Jan 2010 10:01:27 GMT

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

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

Former Member — Thu, 14 Jan 2010 14:52:05 GMT

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

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

Former Member — Thu, 14 Jan 2010 19:18:35 GMT

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

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

Former Member — Fri, 15 Jan 2010 09:42:12 GMT

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

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

Former Member — Mon, 18 Jan 2010 18:11:56 GMT

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,

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

Former Member — Tue, 19 Jan 2010 07:10:15 GMT

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

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

Former Member — Tue, 19 Jan 2010 11:54:18 GMT

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

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

Former Member — Tue, 19 Jan 2010 14:16:05 GMT

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

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

Former Member — Tue, 19 Jan 2010 17:32:30 GMT

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)

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

Former Member — Thu, 21 Jan 2010 20:35:26 GMT

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

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>

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

Former Member — Fri, 22 Jan 2010 16:07:14 GMT

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

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

Former Member — Tue, 26 Jan 2010 22:49:15 GMT

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?

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

Former Member — Wed, 27 Jan 2010 07:33:14 GMT

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

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

Former Member — Wed, 27 Jan 2010 14:22:50 GMT

I am trying to create 2 Measures.

1. Sales

@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

@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)

@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.

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

Former Member — Wed, 27 Jan 2010 14:58:53 GMT

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

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

Former Member — Wed, 27 Jan 2010 16:20:21 GMT

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.

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

Former Member — Thu, 04 Feb 2010 14:44:41 GMT

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

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

Former Member — Fri, 05 Feb 2010 09:35:38 GMT

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

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

Former Member — Wed, 10 Feb 2010 16:33:21 GMT

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

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

Former Member — Wed, 10 Feb 2010 19:30:44 GMT

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

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

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

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

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.