YTD, MTD, Prior Year, Prior Month calculations in SAP BW Universe

[Former Member]

I am finding the SAP BW based universe a totally different animal so far and my question comes from my inexperience in this domain.

In a normal universe, one can use CASE Statement and Calendar or Time table to help do these calculations. Since BW based universes are based on a super BEx query, how are these calculations done in the universe? Are these calculations done in the BEx query itself?

Are there any standards of what should be handled on the BEx side vs. what should be handled on the Universe side?

Also, what's the syntax of a CASE statment or using Substring (or similar function) in a BW universe?

Thanks!

[Former Member · Accepted solution]

Hi,

You can define calculated measures in OLAP universes using MDX language.

To define a Calculated Measure, users must create manually new Measures in the Universe.

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

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

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

u2022 @SELECT

u2022 @PROMPT

u2022 @VARIABLE

The check integrity will validate the XML syntax and any of the Designer functions described above.

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

This paragraph describes how to define calculated measures in MDX and using @Select and @Prompt functions.

CASE statement is not supported in MDX but you can use IIF statement instead of.

It is important to notice that the result of a calculated measure/member in SAP BW (using MDX) must be numeric due to a current limitation f the SAP MDX engine.

Here is 2 samples of an MDX expression using YTD:

<EXPRESSION>

SUM(YTD(@Select(Key Figures\Billed Quantity

</EXPRESSION>

<EXPRESSION>

SUM(YTD([Measures].[D3B1M57O3MN5499LF2X9UNHKA]))

</EXPRESSION>

Regards,

Didier

[Former Member · Accepted solution]

Is there a resource to see what xml is supported? Also, where do you put the expression tags?

[Former Member · Accepted solution]

Didier,

Thanks for your response. Can you also please throw some light on where to draw the line between BEx and Universe? In other words, is it recommended to do these kind of calculations in the BEx?

Thanks,

Kashif

[Former Member · Accepted solution]

Can you describe where you placed the expression in the universe object? Is this a standard measure object with the syntax in the select?

[Former Member · Accepted solution]

Yes, that's where the expression would go. However, there are very few basic examples in the designer guide and that's what i am struggling with. If anyone has some examples that can be shared, it will be really helpful.

[Former Member · Accepted solution]

Hello Didier,

could you please give me an example of an MDX IIF function used in an OLAP universe (on SAP BW query)?

Are there any other functions (like substr(), to_char(), to_date()) to use in an olap universe?

Thanks for your help!!

Joachim

[bhaveshdoshi · Accepted solution]

Hi there,

I would like to have following syntex in BW Universe, the same has been created and worked fine in RDBMS universe.

case

when RAG_Status_New.Description = 'Green' then @Select(Internal Variables\Green Sphere Image)

when RAG_Status_New.Description = 'Amber' then @Select(Internal Variables\Amber Sphere Image)

when RAG_Status_New.Description = 'Red' then @Select(Internal Variables\Red Sphere Image)

end

The @Select(Internal Variables\Green Sphere Image) and other variables are also not coming from BEX but created in universe using 'boimg://redsphere.gif' syntax.

How do I do this using MDX syntax? Please reply this asap, as we are running against the timeline.

Cheers,

Bhavesh

[Former Member · Accepted solution]

Here are a lot of examples:

https://boc.sdn.sap.com/files/OLAP%20Universes%20-%20How%20to,%20samples%20and%20recommendations_0.p...

Thanks to Didier!

Joachim

[Former Member · Accepted solution]

Hi Bhavesh,

You can do such expression with Microsoft Analysis Services 2005.

See example on AdventureWorks:

CASE MemberToStr([Product].[Product Categories].CurrentMember)
WHEN "[Product].[Product Categories].[Category].&[4]" THEN "AAA"
WHEN "[Product].[Product Categories].[Category].&[3]" THEN "BBB"
ELSE "CCC"
END

Unfortunately in SAP such syntax is not supported.

You can use IIF statement instead of CASE but unfortunately again calculated measures or calculated members in SAP BW MUST return numeric values: this is one of the main MDX limitations in SAP.

Regards,

Didier