Synthax for date range filter on a cube

[Ricky@MDPD]

I'm trying to create a date prompt to filter results to any date range.

Can someone tell me the exact synthax for the filter?

The date dimention is made up of the following levels: year/quarter/month/day

I'm trying to achieve: cube_date > ?StartDate? and cube_date < ?EndDate?

Any feedback greatly apreciated

[CognosPaul]

You can use the filter function to return a set of dates.

For example, based on the GO Data Warehouse (analysis) package:

Code Select Expand

filter(
   [Employee expense].[Time dimension].[Time dimension].[Day],
   [Employee expense].[Time dimension].[Time dimension].[Day].[Date]>=#prompt('StartDate','date')# and
   [Employee expense].[Time dimension].[Time dimension].[Day].[Date]<=#prompt('EndDate','date')#)

This is filtering the [Day] level by checking the [Date] attribute against the parameters StartDate and EndDate.
You can read this as:

Code Select Expand

filter([Namespace].[Dimension].[Hierarchy].[Level],[Namespace].[Dimension].[Hierarchy].[Level].[Attribute] >= #prompt('promptmacro','datatype')#)

Remember that in this example the Date attribute had a Date datatype. In some cases the date is stored as an integer YYYYMMDD. You could then use the macro #join('',split('-',prompt('Date','DATE')))#. The prompt will return a date '2009-08-05', the split and join will then strip it of the hyphens, and return an integer 20,090,805.