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
You can use the filter function to return a set of dates.
For example, based on the GO Data Warehouse (analysis) package:
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:
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.