If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

how to get previous month total report?

Started by bvk.cognoise, 13 Sep 2011 11:18:30 AM

Previous topic - Next topic

bvk.cognoise

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
Regards
BVK

Arsenal

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))