Default value in @prompt (Date) - BOXIR2

system · January 16, 2007, 11:12pm

Apreciated much! Thanks a lot. This will be really helpful!

Thanks again!

Bhaumik Parikh (BOB member since 2005-12-07)

system · January 25, 2007, 4:57pm

Hello-
I’m interested in your @Prompt. I’ve been able to find documentation up to “constrained”, but haven’t found anything on next three place holders (…,{‘Prior Month’},User:0). Could you point me to some explanation of the three options available after free/constrained? Thank you very much.

You wrote:
@Prompt(‘Month Equal to:’,‘A’,‘class\Month’,Mono,Constrained,{‘Prior Month’},User:0)

BruceFocht (BOB member since 2004-03-05)

system · January 26, 2007, 6:42pm

Even I tried the way you have described. But i fail to understand the steps. Appreciate if you can write the steps to follow in detail.

Many Thanks!

Bhaumik Parikh (BOB member since 2005-12-07)

system · January 26, 2007, 10:24pm

Try to create simple report with prompt then view SQL. You can see all prompt’s parameters. When

checked ‘Keep last value selected’ in SQL shows ‘Persistent’

unchecked ‘Keep last value selected’ in SQL shows ‘Not_Persistent’

checked ‘set default value’ in SQL shows {’’}

putting number in ‘User:0’ you change the order of prompts(User:1,User:2 etc. ), first one is with 0

I tried to find something about this in pdfs but I could not.

Marfi (BOB member since 2006-12-18)

system · January 26, 2007, 10:27pm

Which step should I describe?

Marfi (BOB member since 2006-12-18)

system · January 29, 2007, 6:24pm

Ok I understod, the usage of Persistent and User:0. Now can you please describe in detail the step to follow for the same using Designer. I read about you creating a condition, but did not understand. Here is what i want to do:

@Select(Dates\Calendar Date) = @Prompt(‘Enter Date or Select Current Date for default’,‘D’,‘Dates\Calendar Date Prompt’,mono,free, Persistent,{‘Dates\Current Date’}, User:0)

What i have done above is I created an object which will return current date, then i use it with the other object to Union both of them so that the LOV of calendar date and current date objects are displayed together. Now I am trying to use current date LOV object in the clause after persistent. I dont know if this works or it has to be a date only.

If i try this in designer, it fails says invalid definition, and it does not even works in WebI. In the parenthesis after Persistent, I need to input default value of date. Can you suggest me the right way to do it.

Also, I understand that User:0, User: 1,… is just for the order of prompts. If i am incorrect please correct me!

Many Thanks for your continuous support.

Bhaumik Parikh (BOB member since 2005-12-07)

system · March 1, 2007, 10:13pm

Using date in prompt you have got an option of using calendar with button current date. In this case I do not see sense using LOV for date. If I were an user I would not like to search for date in a huge list of all dates.

My suggestion is to use prompts for month and year and put in LOV labels: Current Month , Current Year

I assume that year column in DB is changing that is why we would need LOV with Year values from DB and label ‘Current Year’. There are always 12 months (unless 13th is used on some purpose) so we can used fixed LOV.

First create object for Years and edit LOV SQL, put something like this

select table_name.year from table_name
union
select 'Current Year' from dual

check ‘Do not generate SQL…’

now create object condition:

(
  lpad(UNIVERSE.MONTH,2,'0') = @Prompt('Month Equal to:','A',{'01','02','03','04','05','06','07','08','09','10','11','12','Current Month'},Mono,Constrained,,{'Current Month'},User:0)
  or
  (
     'Current Month' = @Prompt('Month Equal to:','A',{'01','02','03','04','05','06','07','08','09','10','11','12','Current Month'},Mono,Constrained,,{'Current Month'},User:0)
      and 
     lpad(UNIVERSE.MONTH,2,'0')=to_char(sysdate,'mm')
  )
)


and 


(	
   to_char(Table.YEAR)= @Prompt('Year Equal to:','A','Class\Year_wCY',Mono,Constrained,,{'Current Year'},User:1)
   or
   (
     'Current Year'= @Prompt('Year Equal to:','A','Class\Year_wCY',Mono,Constrained,,{'Current Year'},User:1)
      and 
      Table.YEAR=to_number(to_char(sysdate,'yyyy'))	
   )
)

remember to hide Year object in universe, we do not want users to use it in query

When you parse there is error but in report is working fine

Marfi (BOB member since 2006-12-18)

system · March 8, 2007, 1:20pm

Marfi

In my case I have to get the last date from a table and this date is never the same from month to month.

I have been trying to put an object as the default but it returns the label of the object not the value.

Do you think we can do it?

Thank you

Yvon

Yvon P (BOB member since 2006-04-27)

system · April 4, 2007, 3:26pm

Have you tried putting max([date object]) instead of sysdate in my example? I think max([date object]) should retrive the last data value from db. Is this what you are looking for?

Marfi (BOB member since 2006-12-18)

system · April 17, 2007, 2:39pm

No what I was trying to achieve was to see the dynamic value not the label. Finally I was able to convince the users that was the only way I had to make it work the way they want.

Thank you

Yvon

Yvon P (BOB member since 2006-04-27)

system · April 18, 2007, 11:44am

Hi,

I am also facing the same problem. I have only 14 days of data. User wants whenever they open the report the default date should be max date on right side box (shuold not be previous selection).

I have written below code:

select File_date from table_name 
union 
select 'LATEST DATE'

check ‘Do not generate SQL…’

In filter i have written following code:

Reject_Rpt.File_Date = @Prompt('1.Select File Date','A','Reject\Rpt File Date',mono,constraint,,
{'LATEST DATE'},USER:0)
or
('LATEST DATE' = @Prompt('1.Select File Date','A','Reject\Rpt File Date',mono,constraint,,
{'LATEST DATE'},USER:0)
AND 
Reject_Rpt.File_Date = max(Reject_Rpt.File_Date)

Thanks in advance.

mohanrajb (BOB member since 2006-09-13)

system · May 28, 2007, 1:33pm

Hi,

I want to default the max date of date filed to the date prompt. as mentioned i put max date instead sysdate but no luck. can anyone help me how defalut the max date in prompt??

mohanrajb (BOB member since 2006-09-13)

system · August 1, 2007, 8:36pm

From what I have read on this and all other topics regarding default values in predefined prompts, I take it you can’t default an actual date, only a date label (like ‘Current Date’). Is that correct? There is no way to display the actual date? For instance, if my prompt was for Current Date, the default value today would show 8/1/2007. I do not want it to show a label of Current Date.

[Moderator Edit: Duplicate post which was made 27 minutes later was deleted. AMC]

sklenke (BOB member since 2006-03-28)

system · August 2, 2007, 11:43am

I once created a prompt which accepted normal dates, but also a certain value like 0. In the code for the predefined prompt in the universe I checked for the 0 value and then substituted the current system date.
Is that an option?

HenkK

HenkK (BOB member since 2004-03-02)

system · August 23, 2007, 3:34pm

Hi,
I have been trying to get default date using below Prompt syntax


(V_INTRCTN_DT_DIM.DT_ACTL <=  @prompt('Enter Day:','D','Date Criteria\Day',Mono,Free,Persistent,{'Today'},User:0))
OR
('Today' = @prompt('Enter Day:','D','Date Criteria\Day',Mono,Free,Persistent,{'Today'},User:0) AND 
V_INTRCTN_DT_DIM.DT_ACTL <= SYSDATE )

but its not wokring and throwing error says that The query cannot run because Prompt contains invalied date. I also need to use date LOV because I want Calendar in Prompt. V_INTRCTN_DT_DIM.DT_ACTL is a date Datatype in Oracle. Any thoughts on this.

wack_jack (BOB member since 2005-12-28)

system · August 23, 2007, 3:52pm

If you use the ‘D’ option in your prompt, then the value for that prompt will be validated against standard dates. You won’t be able to enter February 31, for example. So a text token like “Today” is not going to be valid.

Dave Rathbun (BOB member since 2002-06-06)

system · August 23, 2007, 4:44pm

Thanks for your reply Dave,
I also tried with ‘A’ in Prompt but still its not working. I converted all LOV to character format also. but no luck!!!

(TO_CHAR(V_INTRCTN_DT_DIM.DT_ACTL,'mm/dd/yyyy hh:mm:ss AM') <=  @prompt('Enter Day:','A','Date Criteria\Day',Mono,Free,Persistent,{'Today'},User:0))
OR
('Today' = @prompt('Enter Day:','A','Date Criteria\Day',Mono,Free,Persistent,{'Today'},User:0) AND 
TO_CHAR(V_INTRCTN_DT_DIM.DT_ACTL,'mm/dd/yyyy hh:mm:ss AM') <= TO_CHAR(SYSDATE, 'mm/dd/yyyy hh:mm:ss AM'))

Above syntax gives me wrong result because it compares with ASCII code.

wack_jack (BOB member since 2005-12-28)

system · August 23, 2007, 9:11pm

I think you have to check with the syntax in the help because there are too much parameters I think: it has to do with the list of values. Now you specify a LOV AND the {‘Today’} bit. Try skipping everything after “Mono, Free)”.
To speed up the SQL I would convert the date the user entered to a valid date (instead of the date column), the database server can still use indexes on the date column then.

HenkK

HenkK (BOB member since 2004-03-02)

system · April 16, 2008, 4:11pm

Hi,
I am able to achieve this but I have a different issue.
As the LOvs now become string instead of Dates, my date values are not coming sorted.
What I would like to see is

CURRENT DATE
04/16/2008
04/15/2008
04/14/2008
…
…
12/31/2007
12/30/2007
…

Is there any way to achieve this?

Any help is highly appreciated.

Thanks in Advance!!
John

jhartman (BOB member since 2006-02-08)

system · September 10, 2010, 2:39pm

Dear all,

I made 2 objects in 1 klas MAX_DATE and MIN_DATE, but I can’t see those objects back from Condition view, is this something to do with MAX and MIN functions?
If this is the reason how can I get the MAX en MIN date back as default
In the following condition:

TIMESTAMP_ISO(@Select(DATE)) Between TIMESTAMP_ISO(@prompt (‘From Date:’,‘D’,‘DATE’,mono,Constrained,Not_persistent,User:0)) AND TIMESTAMP_ISO(@prompt (‘To Date:’, ‘D’,‘DATE’,mono,Constrained,Not_persistent,User:1))

Be the way I tried the following conditions too but it didnt worked:

TIMESTAMP_ISO(@Select(DATE)) Between TIMESTAMP_ISO(@prompt (‘From Date:’,‘D’,‘DATE’,mono,Constrained,Not_persistent,{‘2010-01-01 00:00:00’},User:0)) AND TIMESTAMP_ISO(@prompt (‘To Date:’, ‘D’,‘DATE’,mono,Constrained,Not_persistent,{‘2030-12-31 00:00:00’},User:1))

TIMESTAMP_ISO(@Select(DATE)) Between TIMESTAMP_ISO(@prompt (‘From Date:’,‘D’,‘DATE’,mono,Constrained,Not_persistent,{‘MIN(DATE)’},User:0)) AND TIMESTAMP_ISO(@prompt (‘To Date:’, ‘D’,‘DATE’,mono,Constrained,Not_persistent,{‘MAX(DATE)’},User:1))

Im using BO XI R2 and I have connection with DB2 database.

manhal100 (BOB member since 2010-09-10)