current mtd ytd calculated columns

[bashabhaimd]

HI All,

i need two calculated columns mtd,ytd in my list report and this report developed by query based.cognos 8.3 and my db is mysql
here are the conditions:  in my prompt page i have two prompts
1.year
2.month prompts
when user select year prompt   2008 then my list report ytd column should show jan-01-2008 to till date
when user select month prompt jan,or ,feb..etc then my list report should show that month 1st date data to till date.
this is very urgent please help me

Thanks in Advance
baadshah

[CognosPaul]

Hi Basha,

Please don't double post your questions. It only clogs up the page.

In your query you need to filter on the year: [Namespace].[Time].[Year] >= ?Year? and [Namespace].[Time].[Year] <= Year(CURDATE())

Any measure you drag in will automatically show the all the values from the ?Year? to the current year, for example 2008 to 2011.

For the month try something like the following:
case when [Namespace].[Time].[Year] = year(CURDATE()) and [Namespace].[Time].[MonthKey]>= ?Month? then [Namespace].[Measures].[Measure] else 0 end