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

Date Calculation with prompt value

Started by c84user, 07 Dec 2010 11:15:06 AM

Previous topic - Next topic

c84user

Drilling through to a target report and user has the ability to select the entire report period or one of the months provided.  I have accomplished what I assumed would be the hard part, i.e. passing the data item value for Month Value and calculating the dates and getting the query to work.  Now I need to display the dates that are actually used in the header of the target report.

My query filter calculates the dates like this
FOR Begin Date
CASE WHEN
?Month Value? = 1
THEN 
_add_months (?End Rpt Dt Param?,-1)
WHEN ?Month Value? = 2
THEN
_add_months (?End Rpt Dt Param?,-2)
WHEN ?Month Value? = 3
THEN 
_add_months (?End Rpt Dt Param?,-3)
ELSE
?Begin Rpt Dt Param?
END

and for END Date
CASE WHEN
?Month Value? = 1
THEN 
?End Rpt Dt Param?
WHEN ?Month Value? = 2
THEN
_add_months (?End Rpt Dt Param?,-1)
WHEN ?Month Value? = 3
THEN 
_add_months (?End Rpt Dt Param?,-2)
ELSE
?End Rpt Dt Param?
END


So I assume I need to do the same calculations in a layout calculation(s) in the header of the report so I can display the appropriate begin and end dates but am continually encountering errors whether I use the paramdisplayvalue or the paramvalue for each prompt variable.  I've tried cast_timestamp in many ways and haven't been able to accomplish.  Also can't find any posts that address this either.  I'd appreciate ideas and/or solutions?

MFGF

Hi,

A better idea might be to pop this syntax into a Query Calculation, and use a Singleton to display it in your report header.

MF.
Meep!

c84user

Thanks so much,  This worked like a charm.  However, now it's requiring a Month Value.  When the user drills through from the master report on the total period, a month value won't be provided.  Only if they pick one of the 3 months available on the master report will the Month Value be filled in.  Is there a way around this?