I created 4 value prompts for selecting months and years. I need to filter the data between the selected months and years.
?pStartmon? ?pStartYr?
December 2012
?pEndmon? ?pEndYr?
September 2013
Filters: [Month] >= ?pStartmon? and [Month] <= ?pEndmon?
[Year] >= ?pStartYr? and [Year] <= ?pEndYr?
For months filter, Jan 2013 does not fall under the criteria because Jan < December (?pStartmon?) . So my filters above returns no data though I have plenty of data from Jan 2013 to Sept 2013.
I do not want users to select any day. Is there a way to use date prompt for this criteria ? If not how should I modify my filters above to get the correct data ? Please help
Though not quite identical, this may help:
http://www.cognoise.com/index.php?topic=16798.0
I created two data items for starting and ending dates as follows
[Starting Date] = date(?pStartMonth? || '/' || '1' || '/' || ?pStrtYr?)
[Ending Date] = case when ( ?pEndMonth? in (1,3,5,7,8,10,12))
then (date(?pEndMonth? || '/' || '31' || '/' || ?pEndingYr?))
when ( ?pEndMonth? in (4,6,9,11))
then (date(?pEndMonth? || '/' || '30' || '/' || ?pEndingYr?))
else (date(?pEndMonth? || '/' || '28' || '/' || ?pEndingYr?))
end
Now filter [Date] between [Starting Date] and [Ending Date]