Default value in @prompt (Date) - BOXIR2

building semantic layer
1

Hi,

I need to have sysdate (defaut answer) when I use this prompt :
@Select(objet) >= @prompt(‘question’,‘D’,‘Calendrier\Date_jour’,Mono,Free)
I have not find answer about this pb…

I find, under WebI XIR2 :
DATE_CREATION <= @prompt('Date_saisie : ',‘D’,Mono,Free,Persistent,User:0)
Do you have the signification about “Persistent,User:0” ?

Thanks !
Carole

Carole :fr: (BOB member since 2006-09-15)

2

Hi

There is one trick you can do to have in prompt dynamic value as default. You can extend the syntax of prompt with default value let say “current month”, then create condition object that would give you sysdate when “current month” is chosen. Parsing will give you error but it works I did it in XIr1

Marfi :poland: (BOB member since 2006-12-18)

3

Can you please elaborate more on this. I have a req where i have to put current month or last business day in prompt default value. How can i achieve using Designer and implement in WebI.

Thanks!

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

4

I really want to know about that too kindly provide the details
Cheers
-Arpan

arpan :us: (BOB member since 2006-03-22)

5

That is what I did. Users wanted to have 2 prompts for year and month with default values PRIOR MONTH and CURRENT YEAR. What is more they wanted to choose values just from the list, not type in. So in the list of values of month and year I had to include labels PRIOR MONTH and CURRENT YEAR by making union (in Properties tab of object definition click edit/sql and paste following script):

SELECT DISTINCT lpad(table.MONTH,2,‘0’) FROM table union select ‘Prior Month’ from dual

Month was number before and now is converted to string. I suggest to create additional month and year objects apart from orginal ones, so that they would be used just for prompts, we do not want users to use them in queries since labels do not mean anything without prompt. This was the first step.

Then I created condition (I am showing just part of the script to give an idea) with prompts:

‘Prior Month’= @Prompt(‘Month Equal to:’,‘A’,‘class\Month’,Mono,Constrained,{‘Prior Month’},User:0)
and
to_char(table.MONTH)= to_char(add_months(sysdate,-1),‘mm’)

and

‘Current Year’= @Prompt(‘Year Equal to:’,‘A’,‘class\Year’,Mono,Constrained,{‘Current Year’},User:1)
and
table.YEAR=to_number(to_char(sysdate,‘yyyy’))

when users choose those labels they will actually choose info from sysdate (for Oracle). I used extended prompt format so that every time you run report you get always labels as default values in prompts. Please remember if you are using month and year together, you should not forget about combination of labels and actual month and year numbers (e.g. ‘Prior Month’ + 2000; 05+‘Current Year’ etc.)

Hope this help. In my case it is working perfect. :smiley:

Marfi :poland: (BOB member since 2006-12-18)

6

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

Thanks again!

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

7

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 :us: (BOB member since 2004-03-05)

8

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)

9

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 :poland: (BOB member since 2006-12-18)

10

Which step should I describe?

Marfi :poland: (BOB member since 2006-12-18)

11

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)

12

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 :lol:

Marfi :poland: (BOB member since 2006-12-18)

13

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)

14

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 :poland: (BOB member since 2006-12-18)

15

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)

16

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 :india: (BOB member since 2006-09-13)

17

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?? :frowning:

mohanrajb :india: (BOB member since 2006-09-13)

18

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)

19

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 :netherlands: (BOB member since 2004-03-02)

20

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)