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.
:)
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
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.
case extract( month, [sell_date] )
when 1 then 'Jan'
when 2 then 'Feb'
...
end