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?
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.
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?