Here, you can see “Optional Prompt” is dimmed as “False” and cannot be modified. It means the prompt is required to input value before running report. What if we set “Default Value” as blank and run the report?
Click on “OK”, the page does not respond. We are required to input a standard date value, e.g. 03/27/2012, before we are able to click on “OK” to proceed. So, the default value cannot be set as blank anymore for the prompt created within the Command Object, and the prompt cannot be optional.
I need to design a “Magic Date” value, something like January 1, 1900 as the placeholder. It is highly unlikely that any of my records will include that date as a valid entry. That is the first important point about this concept: I must pick a date that will never appear as part of my normal data. That is the magic date.
In the Command SQL query edit box, I have the code logic to substitute “1/1/1900” to whatever I want it to be. In the example below, it is the current date.
Meanwhile, I revise the Prompt Text to be more readable:
Stored Procedure
What if I use a Stored Procedure as the data source and input parameter is needed for the sproc? Prompt will be created automatically. The name is the same as the sproc parameter. The different part for the sproc prompt is that there is an option to “Set to Null”. I can use it to set the default value as Null. Please look at an example below:
Stored Procedure cr_unbilledDeliv_CalDate is used as the data source, 4 prompts – @startdate, @enddate, @Createdonfrom, @Createdonto are created when the data source is set:
There is a “Set to Null” option with every prompt. When I check on it, I am able to set the default as Null to be passed to the backend sproc.
In the sproc, I parse the Null date and set the default date according to the business requirement respectively:
Conclusion
With three approaches I illustrate – Blank Default for a standard prompt, Magic Date for Command Object prompt, Set to Null for Stored Procedure prompt, with some specific code logic support, I can convert the prompt default to today, yesterday, end of this week, end of last week, end of the month… just about anything else that I’ve already covered above. For extended adaption, I can use the concept of a “magic number” for numeric prompts and a “magic string” for text prompts as well, although those are generally not used as frequently. Last by not the least, Dave Rathbun wrote a post on how to design a prompt default within universe. Feel free to refer to it for more information.
