HI ,
i have a date prompt.i that date prompt i need to show one new value like previous month for this i added one static choice.now my requirement is whenever i click on that previous month it should show previous month total records(no need to mind today date).how can i dot his ? :( please suggest me.
thanks
bvk
the requirement you're listing is ambigous, IMHO.
first of all, how can you have a static choice for prior month in a prompt? Unless you mean to say it's not going to be a month prompt at all but just something the user selects when the user would like to see the report run for the prior month.
If that's the case, then _add_months function might be able to help. In your query, you might have to add a detail filter which would look something like
paramvalue(?monthselector?) = 1 and cast(extract(month,[date]),varchar(10)) = extract(month, _add_months(current_date, -1))
OR
cast(extract(month,[date]),varchar(10)) = extract(month, _add_months(current_date, -1))
where
monthselector = parameter name of the prompt that has the prior month selection
1= whatever the use value of the static choice is in the above prompt
[date] = your date query item from FM
Think the above expression would work for getting data for either the current or prior month
alternatively, you could put an IF THEN expression within a data item (something like if paramvalue(?monthselector?) = 1 then addmonths = -1 else addmonths = 0) and then in the detail filter you would just have :
cast(extract(month,[date]),varchar(10)) = extract(month, _add_months(current_date, addmonths))