Month Function from User Response

[Former Member]

Post Author: NSMactic

CA Forum: Desktop Intelligence Reporting

I am using Bus Odj 6.5.4 and I have a query that has to use a month end posting date e.g. 31/08/2007 that is input via a prompt.

From this prompt I would like the report to display the Month and Year E.g. Month: 8 Year: 2007

I have tried using the UserResponse, DateTo and Month functions but I keep getting an #ERROR message

Is there a way to show this or am I doing something wrong?

Many thanks

[Former Member]

Post Author: Peter@KLM

CA Forum: Desktop Intelligence Reporting

Maybe this formula does what you like to have

FlagUR_Start_CY = ToDate(UserResponse("Query 1 with STE01" ,"3. Begin of period CY [yyyymm]") ,"yyyymm")

From the variable <_Flag_UR_Start_CY> you can extract the month and year if you like, but with a format function you can also display the date in the layout you want

FormatDate(<_Flag_UR_Start_CY> ,"mm/yyyy") resulting in 01/2008 or

FormatDate(<_Flag_UR_Start_CY> ,"mmm yyyy") resulting in Jan 2008

[Former Member]

Post Author: NSMactic

CA Forum: Desktop Intelligence Reporting

There is a way round it but I have had to bring in a Posting Date dimension from the database and use the following formula.

="Month: " & MonthNumberOfYear(Max(<Posting Date>)) & " Year: " & Year(Max(<Posting Date>))

If anyone has an easier way I would be grateful

Thanks