I wanted to know, if this is possible to be calculated at report level only. I don’t have access to universe or database. I am using Deski XI against Oracle.
In one of my DP i pull historical 6 months data. If current month is Feb, 2009 i get data from August, 2008 to Jan, 2009. User would select these 2 values in prompts.
Using UserResponce() functions and other date function can i calculate the number of weeks between these 2 months.
I need the number that tells me the number of weeks between the selected months. I am currently using Format 2 in the prompts in my report (obviouslly ).
Would you be more precise as to how we can go about changing a User Response() string output to a date. And if we are able to convert it to a date what date would that be. Will it make sure that it converts it the start date of the month i.e “date 1”.
Kindly, help in converting the User Response Month to date.
This is what was the final formula that i had created in my report and i was expecting it to work but its returning #ERROR message. Here are the details objects that were created at report level.
LW:
=Week(ToDate(Concatenation(Replace(UserResponse("6 Months" ,"4. Enter 6th month prior to previous month:") ," AP" ,"") ,"01") ,"yyyymmdd"))
Both of these objects return #ERROR message and hence the NOW variable too has #ERROR. Made changes to the UserResponce() to what you had provided by pointing the data provider that needs to be used from the report.
Please try splitting up the code into smaller ones and test them out. I would suggest you to especially check ToDate() part. Are you sure the date format of the prompt value is YYYY APMM? When I tried it out here, I’m able to bring the result. :?
Their is “FY” prefix to YYYY. Their were whole bunch of other changes that is taking toal on me on this report. I would really appreciate if you can help me in getting the updated formula for PW.
Don’t know whats going on now. Tried removing all the replace and other functions and tried seeing the character that would be returned by using UserResponce() function and it still returns #ERROR. Please see below.
=UserResponse("6 Months" ,"4. Enter 6th month prior to previous month:")
I even tried
=UserResponse(DataProvider(<Accounting Period(6 Months)>) ,"4. Enter 6th month prior to previous month:")
Why does it not return the value that was entered in the prompt. . Please!
Okay, the last try. Try this. Go to Insert (from the menu bar) -> Special Field -> Query prompt -> Select any one of the query prompt and see what formula is getting generated.
The formula is similar to what we are generating. Just copied that UserResponce() and placed it in a Detail variable at report level and it is giving me the values that we need i.e FY2009 AP02 for 6 months prior to previous month and FY2009 AP07 for Previous Month. This is fine.
Now after doing that when i try to do replace() function for FY text it is not returning the new value which should have been “2009 AP02” rather shows me the same value i.e “FY2009 AP02”.
Here’s the partial formula that i tried:
=Replace(UserResponse ("6 Months" , "4. Enter 6th month prior to previous month:") ,"FY" ,"")
This is returning FY2009 AP02 only, which is strange as was the earlier case.
It works great!!! . Still dont get the result that i am getting when i define a variable. Dosen’t that sound strange that if i do the same calculation as if its a formula it returns me the result, but if i do it by defining a variable it just gives me the UserResponce() function results.
Got the result at-last that i am looking for. Just wondering y is it not returning me the result when i define a variable?
Thanks a million Jansi for all your help and support.
Glad it worked at last. But still mystery remains.
Moderator Note: Please do not use Instant Message abbreviations on BOB. We want to keep BOB easy to understand for our International community. Thanks.
).
(BOB member since 2008-03-14)
my mistake. The Prompt format is FYYYYY APMM.
on this report. I would really appreciate if you can help me in getting the updated formula for PW.
(BOB member since 2008-03-14)
. Please!
