extract date with a year (prompt) Cognos 10.1

[Agadir]

Hello,

I have the following situation:

table_sales
product_id  334
sell_date  23-01-2013



I want to see the sales from a year that is prompted by the user.
So this has to be the output in the report:
Januari  Feb.  Mar. Apr. Etc.
232      323   233  260


At this moment i have this query:
Januari   total(if sell_date between '01-01-2013 and '31-01-2013' then (1) else (0))
Februari  total(if sell_date between '01-02-2013 and '29-02-2013' then (1) else (0))
etc



The problem is that it is a fixed year. How can i make the year in the query variable?


So the user starts the report and gets the prompt "Which year?:"
the user puts a year in and all the sales of that year will be shown.


I had an idea like this (but it does not work): Januari   total(if sell_date between '01-01-?year?' and '31-01-?year' then (1) else (0))


Thanks in advance.
:)

[Lynn]

Presumably you are filtering the report by year, so all you need to do is look at the month on the rows that get returned.

You filter could do something like

Code Select Expand


extract( year, [sell_date] ) = ?YearPrompt?


You can then use the extract function to get the month portion of a date. One data item could be used to retrieve the month and placed in the columns on a crosstab. Then create a second data item with your measure value.

Code Select Expand


case extract( month, [sell_date] )
when 1 then 'Jan'
when 2 then 'Feb'
...
end