Convert Values to Single Value in Universe Object

[Former Member]

Hi,

Our environment is BO 3.1 and all universes are on top of SAP BI 7.0.

How do we convert the values coming from a dimension to a single value?

I have created an object as "Active" by copying the existing "E01 Cutomer key" and added a Where clause in the object.

Select -

[ZEU_KUNNR].[LEVEL01].[[2ZEU_KUNNR]].[Value]

Where -

<FILTER KEY="[ZEU_KUNNR].[LEVEL01].[[2ZEU_KUNNR]].[Value]">

<CONDITION OPERATORCONDITION="InList">

<CONSTANT CAPTION='16278'/><CONSTANT CAPTION='16279'/>

</CONDITION>

</FILTER>

When I drag the above object Active (dimension) to the result object; Web I report works fine and retrieve only the values for - 16278 and 16279. But my requirement is to get the value as "Active" instead of filter values.

For eg.,

Active Subtotal2

=================

16278 $100

16279 $400

Instead of above we need to get like this:

Active Subtotal2

=================

Active $500

Is this possible in Universe? Do we have any MDX function/syntax to convert the values in Universe?

Thanks

Jai

Edited by: Jai on Sep 28, 2009 4:21 PM

[Former Member · Accepted solution]

Hi,

The best way to obtain your result would be to create a caluclated measure like this:

<EXPRESSION> IIF(  [ZEU_KUNNR].currentmember.properties.("[2ZEU_KUNNR]") = "16278", "Active",
IIF(  [ZEU_KUNNR].currentmember.properties.("[2ZEU_KUNNR]") = "16279", "Active",""),"")</EXPRESSION>

Then add the where clause you defined in the new calculated measure.

Unfortunately, all calculated measures and calculated members MUST return a numeric value with SAP MDX. In your case you want to return "Active" that is string.

This expression works fine with Microsoft Analysis Services.

So I recommend that you keep the object you defined and create a Web Intelligence formula that will group the 2 values in a single value "Active".

Didier

[Former Member · Accepted solution]

Thanks Didier. If this is a ROLAP universe we could have achieve this by "CASE" statement in Universe level easily. I am not sure when SAP is going to come out these difficulties for OLAP universes based on SAP Bex Query.

Users will not happy to do the same in both Universe and Web Intelligence.

The really challenge we are facing is currently users are using a different reporing tool where they can save the list of values as "user list".

For example based on L01 EU Customer; values 16278 and 16279 are saved as "Active" User list. This user list is saved in repository and the user can drag this to any report and the values appear as "Active".

We thought of achieve this by copying the dimension object (L01 EU Customer)in Universe and create Filter for the values. This works fine; but not able to get the values as "Active".

Thanks

Jai