dateadd ... using MDX:8.4, RS

[kado]

Gurus,

Sourcing a Transformer cube within Report Studio I would like drive the output of my report with a User Selected MONTH. We have defined the Time Dimensions for Year, Quarter, Month, Week, QTD, YTD within our Transformer cube. I defined a MONTH prompt ?MonthPar? which works fine for one filtered month but I am having trouble defining relative dates for previous months. Essentially, I want the syntax to operate like the SQL function dateadd ...... dateadd(month, -1, ?MonthPar?) but get a crossjoin error. I also tried the Parallel period function without success.

I read a number of posts which talk about using the MUMS, caption syntax, a filter with a tuple, #prompt#, and potentially an If, then, or CASE ... but am not sure which is the best approach.

My mind is still stuck in a relational world and I am somewhat inclined to think that using a prompt to define relative dates would potentially be more efficient from a relational model. I need to drive the output of 6 different columns, plus a QTD, and a YTD and am thinking this would somewhat defeat the purpose of using a cube.

Any thoughts, guidance????

THANKS,
MDX Padawan Learner

[david.stachon]

It's funny....this should be quite easy but it's not....

First, for your month prompt query:

Data Item 1: Month (your Month level from the cube)  ...I assume it's called [Month]
Data Item 2: roleValue('_memberUniqueName',[Month])

(Data Item 2 will be the "Use" value in your drop down box)

In your report query, you'll need a normal data item with this: (I called it "Prompted Month Member"):

#prompt('p Month', 'memberuniquename', '[Firth Sales Cube].[Date].[Date].[Month]->:[PC].[@MEMBER].[20050701-20050731]')#

...the hard-coded MUN is there because there needs to be a default value. this can be anything.

Then, create a data item of type "Set Expression":

...in this example, it returns the prompted month and the previous 3 months relative to the prompted month.

SET( [Prompted Month Member],
        parallelPeriod ([Firth Sales Cube].[Date].[Date].[Month],1,[Prompted Month Member]),
        parallelPeriod ([Firth Sales Cube].[Date].[Date].[Month],2,[Prompted Month Member]),
        parallelPeriod ([Firth Sales Cube].[Date].[Date].[Month],3,[Prompted Month Member])
       )


...it is this set expression you will use on your report.

I've attached the xml spec.

hopefully, this helps you out.

[kado]

Very Nice, I got it to work using my cube!!!

THANK YOU!!

I tried to also integrate QTD and YTD into the same and another SET expression but got an ERROR saying that the 'parallel period' function requires operands from the same hierarchy.

Any ideas?

[david.stachon]

are you just trying to display the actual QTD/YTD that's in your cube? (meaning, the current QTD, and YTD)

or do an "As At" style QTD/YTD calculation? ...meaning, if your user selects Jun/2006, you want to figure out what QTD/YTD was "As At" that time period?

(hope that makes sense)

[CognosPaul]

Why not use lastPeriods?

Code Select Expand

lastPeriods (3,#prompt('p Month', 'memberuniquename', '[Firth Sales Cube].[Date].[Date].[Month]->:[PC].[@MEMBER].[20050701-20050731]')#)

It returns a set of the selected member, plus the previous 2 (or next 2 if you use -3) members.

[david.stachon]

yes, good call.

[kado]

Trying to do an "As At" .... So basically, I could wrap the lastPeriods in an IF depending upon which month is selected?

But then I would have to do some sort of DATEPART on 'p Month' .... Is that what you had in mind? Any idea what that DATEPART would look like?

Thanks for the solid feedback, very very helpful!!!!

[kado]

I see what you are saying about the lastperiods (after testing  :D) ... Just another way to do the SET expression and acquire previous months ....

Any thoughts on the QTD, YTD?

Also, I am wondering if you could recommend any good resources (web-sites, books, etc) ... for better understanding and manipulating Cognos specific MDX from Transformer cubes?

Thanks,
Matt

[david.stachon]

I don't think you'll need to do anything with date-part....

because you know your "member" (i.e. the actual month) ..likely, you'll be able to do this with "Ancestor" (up 1 level for QTD, and two levels for YTD).

...give that a try.

In terms of learning material, there is a specific Cognos course for Report Studio and dimensional sources ...it's pretty good.

What I really find helpful, is using Report Studio Express..then, switching to Professional, and see what it has generated for a query. Doing the same with Analysis Studio is okay too, but it produces a lot more code.

[kado]

Once again you are correct   ;D  ... THANK YOU ... The ancestor works perfect.

One last question since this has all been so helpful. What about if I want to get PY QTD and PY YTD? Which are also set up in my cube as their own hierarchies. I tried using the COUSIN and even put a negative sign on the ANCESTOR but it doesn't really seem to be either ... it's more of a SISTER or BROTHER (which aren't available functions).

Any thoughts???

[billylodz]

http://www.cognoise.com/community/index.php/topic,4815.msg16862.html#msg16862

[david.stachon]

I think you should be able to use two functions to get your PY members....

meaning, if you know your prompted month member, you use ancestor to figure out QTD ...then, PY QTD would be parallel period 4 of that member

something like: parallelperiod(ancestor(<prompted month member>,1),3)
(untested pseudo-code)

and PY YTD would be ancestor 2 of the prompted month member parallel period 1.

...that make sense?  :-\

[kado]

Makes sense and works great ... THANKS to you I think I may be getting the hang of some of this  8)

THANKS for the solid feedback!!!!!!!!  ;D

[meenu1.k]

Hi Friends,
I have the same requirement... YTD and three months trend.i get the required result by doing as  posted,But when i do union its giving value only for the largest month selected.Can you please help me in this .the delivery of the report is on coming Monday 27th march 2010