Enter Either a Date or Space

building semantic layer
1

Have this report which has 4 four dates.

1)Start Clear By Date
2)End Clear By Date
3)Stat Execution Date
4)End Execution Date.

My question, is it possible for me to provide the user to enter Space in place of date.If yes, would this code work for the space "Contract.EFFECTIVE_DT >=
decode(@Prompt(‘1.Enter Contract Execution Date - Start (MM/DD/YYYY)’,‘D’,MONO,FREE),’ ‘,sysdate-5,@Prompt(‘1.Enter Contract Execution Date - Start (MM/DD/YYYY)’,‘D’,MONO,FREE))
and
Contract.EFFECTIVE_DT<=
decode(@Prompt(‘2.Enter Contract Execution Date - End (MM/DD/YYYY)’,‘D’,MONO,FREE),’ ',sysdate,@Prompt(‘2.Enter Contract Execution Date - End (MM/DD/YYYY)’,‘D’,MONO,FREE))

Tried the above code, its parsing but its not returning anything when i enter Space.

Any suggestions on either code or method to allow the users to enter space.

Thanks in Advance,
Bus_Admin

bus_admin (BOB member since 2006-09-08)

2

Bus_admin,

A date-type in most RDBMS engines is a serial number incremented by 86400 ticks per day (which helps it to accommodate 24 hours, 60 minutes, 60 seconds per day). As such, the data found within this column will either be a date value or null. You can pass a space to this column within a where clause, and it will syntax (as you’ve proven), however, the effect will always be false (i.e. no records will be selected). Using date-type within a BOE project is always cause for careful handling, whether its the date, time, or both that you want to deal with, it always involves careful consideration and handling. Perhaps you should rethink why you would want the user to have the option of entering a date or a space…

Thanks,
John

jsanzone :us: (BOB member since 2006-09-12)

3

Thanks for the respond john, the reason my user wants to enter a “space” in place of " Specific Date" is we are replicating from exisiting Acutate reports to Busines objects, in actuate the user can skip a few prompts like in this case the user need not enter date prompts and enter what he needs and the report returns data.
Now the user wants to replicate the actuate style reporting into BO and he would want to either enter something in date or just skip the date prompt all together, as u know business objects R-2 does not have optional prompts value yet.
I thought instead of skipping the date prompt, would let the user enter space bar in place of it.
How could i get the prompt return data.

Any suggestions are welcome,
Amar

bus_admin (BOB member since 2006-09-08)

4

I use a “magic date” and translate that to “all dates” via case logic. The magic date is often 1/1/1900 or some such date that should never appear in your actual data.

There’s a blog post (see signature for link) with more details if you need them. :sunglasses:

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