Need help with Optional Prompts

[system]

Hi,
I had to create a date prompt where if user doesn’t enter anything in the prompt,the prompt should take it as null and run the query with date is null.
I created filter at Universe level where I set the default value in the prompt as blank.Below is the syntax:
@Select(Cust Create Dt\Cust Create Dt_optional) IN
@prompt(‘Enter Cust Create Dt:’,‘D’,‘Cust Create Dt\Cust Create Dt’,Mono,Free,Persistent,{’’},)

With this,I can see default value as blank while running the query at webi but the database level query is not running giving error as"The query cannot run because prompt contains invalid dates"

I looked through BOB,there are topics on optional prompts but I could not find solution over this particular issue,Please let me know if anyone has any work around over the issue.

Thanks in Advance.

---

pr123 (BOB member since 2008-09-22)

[system]

Designer doesn’t support optional prompts yet. They have to be created at the report’s query level. And they are available only in Web-I XI 3.x.

Alternatively in designer in all the versions, you can achieve the same by including ‘ALL’ in LOV. Please search B B. You’ll get enough topics. One such is this topic.

---

Jansi (BOB member since 2008-05-12)

[system]

Thanks for the quick reply…
Yes,I got to know this from BOB that designer doesn’t support optional prompts but in this case,I simply need to run the query with null values and i am not sure how to achieve that.

---

pr123 (BOB member since 2008-09-22)

[system]

You can ignore the entire condition itself.

You can try using the SQL generated by Web-I tool when optional prompts are created. But I would not prefer it.

---

Jansi (BOB member since 2008-05-12)

[system]

‘’ is not the same as null. “Optional” is not the same as NULL either. Also you cannot use an IN operand to check for NULL, you must use either IS NULL or IS NOT NULL only. Basically you can’t do what you’re asking in Designer.

---

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