Skip to Content

Calculated measures usage and recommendations 

 

In BOE XI3.x you can create calculated measures/key figures in universes.

Calculated Measures definitions are using MDX functions embedded in XML tags.

 

In order to create calculated measures, users must to create pure MDX expression embedded in XML tags: <EXPRESSION></EXPRESSION>

In this new expression, we authorize to use any Designer function such as:

    • @Prompt
    • @Variable

The check integrity ONLY validate the XML syntax and @Select function.

No MDX parser is provided in this version of Universe Designer.

 

We recommend using @Select as much as possible rather than the measure definition for multiple reasons:

    1. @SELECT is always resolved at query time
    2. Life Cycle Management only ensure integrity with generated objects not with objects created by a user: @Select always guarantee the validity of the object

 

@Select function must be used like this: @Select(Parent class\Object)

This function is case sensitive as well as class name and object name.

@Select always refer universe metadata and not database definition.

 

A calculated measure can be used in a pre-defined filter.

The expression can be embedded in the filter definition ore referenced by using an @Select function.

In such a case the filter definition must start as following:

<FILTER EXPRESSION=”@Select(Class\Object)“>

<FILTER EXPRESSION=”IIF(.[Quantity] = 2000, 1, 0)“>

     

    SAP MDX restrictions:

    The list below describes the main SAP MDX restrictions compared to Microsoft MDX functions: 

      1. Calculated measures must return numeric values only
      2. Set functions such as SETTOSTR, TUPLETOSTR, MEMBERTOSTR, STRTOARRAY are not supported
      3. Data type conversion functions such as CDate, CInt, CStr are not supported
      4. Case When Else statement is not supported
      5. Some operators such as <, >, <=, >= can not be used in any case
      6. etc
    To report this post you need to login first.

    3 Comments

    You must be Logged on to comment or reply to a post.

    1. Deepu Sasidharan
      Thanks for the blog!

      Can we have String functions for BW InfoObject values within the OLAP Universes. For instance if BW returns value “1000/PC0001” and I want to extract PC0001 from the above value (remove first 5 chars) can we do this within the Universe Designer?

      We tried the String functions mentioned in note 1087077 and did not work, parsing worked fine while running the report it resulted in an error (from your blog looks like parsing will only do the XML syntax check).

      We are on BW 7.01 SPS 4 and BOE XI 3.1 SP 2.

      Thanks.

      Deepu

      (0) 
      1. Didier MAZOUE Post author
        As I mentioned the main restriction in MDX calculated expressions is that you must return a numeric value.
        That doesn’t mean that you cannot use string functions.

        For instance you can use this string function in your universe that will return 1 for Company Code starting with “IDES”:

        IIF( LEFT([0D_CO_CODE].CurrentMember.Properties(“[50D_CO_CODE]”), 4)  = “IDES”, 1, 0)

        Regards,

        Didier

        (0) 
        1. Mohammed EL F

          Dear Didier,

          Im looking for a way to convert a string object to numc object in universe designer using MDX. Im using BO XI 3.1 and BW 7.0. I hope that you provide me a solution.

          Thanks in advance.

          Regards,

          Mohammed

          (0) 

    Leave a Reply