YTD, MTD, Prior Year, Prior Month calculations in SAP BW Universe
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?
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].&" THEN "AAA" WHEN "[Product].[Product Categories].[Category].&" 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.