YTD , MTD reports from Cube

[mindbender]

How do we create YTD , MTD type reports from a cube. I want to give the user a prompt consisting of static values YTD , MTD based on which i want to dynamically adjust my date filter.

Also the current date function does not seem to work in the filter condition throwing the foll. error:GEN-ERR-0015 Initially, in data source type(s) 'pattern', function 'providerQuery' is not supported in 'OlapQueryProvider'. After decomposition, in data source type(s) 'PC', function 'fn:current_date' is not supported in 'OlapQueryProvider'.

Is the current date function not supported for the cube??

[mindbender]

How do we create YTD , MTD type reports from a cube. I want to give the user a prompt consisting of static values YTD , MTD based on which i want to dynamically adjust my date filter.

Also the current date function does not seem to work in the filter condition throwing the foll. error:GEN-ERR-0015 Initially, in data source type(s) 'pattern', function 'providerQuery' is not supported in 'OlapQueryProvider'. After decomposition, in data source type(s) 'PC', function 'fn:current_date' is not supported in 'OlapQueryProvider'.

Is the current date function not supported for the cube??

[COGNOiSe administrator]

You have to build those relative time periods in the Transformer model. Check PPTS documentation for a step-by-step guide. As for the error, raise it with Cognos support, or try converting the current_date to a string with the same format as your date category codes.

Darek

[MDXpressor]

Actually, with a cube you can deliver MTD or YTD calcs from the normal date hierarchy using a couple of nested OLAP functions.  For example:  Total(currentMeasure within set PeriodstoDate([Cube].[Date Dimension].[Date Level], [Date Member]).  What I am struggling with now is how to prompt for the level, but a solution may be to deliver both to the same report.  This is a pretty common request from our Business users, though it should be said that it tends to be coupled with growth analysis.

In the attached sample (authored against the Great Outdoors Company model in 8.1) I have prompted the user for the Current Month, delivered the Prior Month (same month from previous fiscal year), PeriodstoDate for both Prior and Current Month, as well as the summarized PeriodstoDate called QTD and Prior QTD, and respective Growth calcs.