Can we Implement YTD using MDX syntax in olap universe.

[Former Member]

Hi,

I am trying to implement YTD prompt from universe level. Can any one suggest how can i implement it using MDX syntax in olap universe.

Regards,

Anil Kumar.

[Former Member · Accepted solution]

Hi,

The correct syntax for using YTD is to retrieve the members for the time dimension you need like: 0CALYEAR, 0CALMONTH, 0CALDAY, etc.

Then write an expression such like this:

<EXPRESSION>SUM(YTD([0CALMONTH].CurrentMember), [Measures].[0D_NETVAL_S])</EXPRESSION>

You will find several MDX sample [HERE|http://www.sdn.sap.com/irj/boc/index?rid=/library/uuid/c0a45246-ce76-2b10-e688-f5c8206203eb]

Regards,

Didier.

[Former Member · Accepted solution]

Hi Didier,

Thanks for your response. I am sorry for late response and i tried this syntax <EXPRESSION> SUM(MTD(@Prompt('YYYYMM','A','Cal. year / month\L01 Cal. year / month',mono,free),

@(Select(Key Figures\Total Variance))) </EXPRESSION>

but it is throwing an error after execution of prompt can u help in resolving this problem.

Thanks and Regards,

Anil Kumar.

[Former Member · Accepted solution]

Hi,

In MDX, you need to generate the technical name to have you expression valid.

Moreover the correct syntax is: SUM(MTD(member),measure)

So in your case the prompt must be constrained on the technical name and cannot be free, the correct syntax is:

<EXPRESSION> SUM(MTD(@Prompt('YYYYMM','A','Cal. year / month\L01 Cal. year / month',mono,primary_key)),
@(Select(Key Figures\Total Variance)) </EXPRESSION>

Last, the result will be not really significant because you are asking for a MTD with a Year-Month as time member: the result will be the measure value for the selected month.

In your case you need to have a time series function for an upper level like YTD or QTD. If you want to use MTD, the the time member selected would be at a lowest level, such as week or day.

Regards

Didier

[Former Member]

Hi Anil,

Try this code:

<EXPRESSION>SUM(@Prompt(u2018Period to date?u2019,Nu2019,{u2018YTDu2019,u2019QTDu2019,u2019MTDu2019,u2019WTDu2019},mono,

constrained)(@Select(Key Figures\Billed Quantity)))</EXPRESSION>

This will promt the user to select which u201Cperiod to dateu201D to choose: YTD, QTD, MTD,WTD.

Hope it helps...

Thanks,

Sayed

[IngoH]

it will prompt the user but it doesn't solve the problem that the MDX syntax on BW doesn't have somthing like a "now()" which means it is not dynamically using the system data to pick up the current date.

Ingo

[IngoH]

Is this for BW or for MS Analysis Services ?

In case it is BW the easiest way is to use EXIT Variable in the underlying BW query.

Ingo

[Former Member]

Hi Ingo,

Thanks for your reply. I want to do in BO Universe designer. I am thinking it may help by using Between opertaor in mdx syntax. but it is not showing two prompts. it was showing one prompt.

[IngoH]

Hi,

the problem you have in the Universe is that the MDX syntax doesn't have a "NOw" - which means you wouldn't be able to do something like a YTD. the option is to use an EXIT variable.

ingo