Restricted measure in sap olap universe

[former_member206223]

Hi,

I am trying to create a restricted measure in sap olap universe with Orders key figure restricted on 0PLANT and 0MATERIAL objects.i want to restrict on list of 0PLANT (1000,1200,2000) and 0MATERIAL (DPC1009,DPC1010).

I used <EXPRESSION>@Select(Keyfigures\Orders)</EXPRESSION> in select clause,

and restriction on list of values in where clause as below.

<FILTER KEY="[0MATERIAL].[LEVEL01]">

<CONDITION OPERATORCONDITION="InList">

<CONSTANT CAPTION="DPC1009"/>

<CONSTANT CAPTION="DPC1010"/>

</CONDITION>

</FILTER>

but when i used orders restricted measure, report returned no data,actually there is data for this restriction.

can anybody tell me where is this mistake in the above syntax and also how can i add 0PLANT restriction to this syntax.

Thanks,

Harsha

[arijit_das · Accepted solution]

Hi,

In Select clause, write:

<EXPRESSION>@Select(Key Figures\Orders)</EXPRESSION> 

In Where clause, write:

<OPERATOR VALUE="AND">
  <FILTER KEY="[0MATERIAL].[LEVEL01]"> 
    <CONDITION OPERATORCONDITION="InList"> 
      <CONSTANT CAPTION="DPC1009"/> 
      <CONSTANT CAPTION="DPC1010"/> 
    </CONDITION> 
  </FILTER> 
  <FILTER KEY="[0PLANT].[LEVEL01]"> 
    <CONDITION OPERATORCONDITION="InList"> 
      <CONSTANT CAPTION="1000"/> 
      <CONSTANT CAPTION="1200"/> 
      <CONSTANT CAPTION="2000"/> 
    </CONDITION> 
  </FILTER> 
</OPERATOR>

Regards

[arijit_das · Accepted solution]

You can try the syntax below in the Select clause without any where clause:

<EXPRESSION>(@Select(Key Figures\Orders),{[0MATERIAL].[DPC1009],[0MATERIAL].[DPC1010]},{[0PLANT].[1000],[0PLANT].[1200],[0PLANT].[2000]})</EXPRESSION> 

Regards

[former_member206223 · Accepted solution]

Thanks for the reply,

<EXPRESSION>(@Select(Key Figures\Orders),{[0MATERIAL].[DPC1009],[0MATERIAL].[DPC1010]},{[0PLANT].[1000],[0PLANT].[1200],[0PLANT].[2000]})</EXPRESSION>

above syntax expression is giving error when i am running the webi report.

but if i include only one material like

<EXPRESSION>(@Select(Key Figures\Orders),[0MATERIAL].[DPC1009],)</EXPRESSION>

this is working fine.

and other expression with select and where clause is working fine but, the restricted measure is applying filter on entire query so in the report results it is displaying only those materials (DPC1009,DPC1010).

Is there any syntax modification required to create restricted measure using select expression itself, and pls let me know how can i use key object in syntax for eg ([0MATERIAL].[LEVEL01].[[20MATERIAL]].[Value])

like whether i need to use CAPTION,NAME or VALUE in Filter expression when using key object.

Thanks,

Harsha

[Former Member · Accepted solution]

Hi,

If you have 2 characteristics and more and 2 values and more per characteristic, the syntax is different than the one proposed.

Try this syntax:

<EXPRESSION>Aggregate( { { [0MATERIAL].[DPC1009],[0MATERIAL].[DPC1010] } * { [0PLANT].[1000],[0PLANT].[1200],[0PLANT].[2000] } }, @Select(Key Figures\Orders))</EXPRESSION>

Regards,

Didier

[arijit_das · Accepted solution]

Thanks Didier for this information. This is really useful.