Optional Prompts with Default Values in Designer

Posted on by EV Technologies

Updated 4/12/2010 – I feel like a blockhead. I went to reference my own blog and found a copy and paste issue in my formula below. It’s fixed (I hope). Sorry!

Truth be told, today I felt like a total rookie. I got a requirement that screamed “don’t dare duplicate that report just so you can have a version to schedule and version to run on demand”. The requirement was to run a Web Intelligence report monthly through the job server (always running for the prior month when executed) but to also allow the user to open, refresh, and be prompted for a list of values. I spent a few minutes tinkering with some SQL that ultimately lead me to a simple pre-defined condition for my reports. I’m certain this has been blogged and forum posted to death by now, but I’m going to throw it out there for fun.

For starters, my case was pretty simple. The time period was actually stored as a string formatted as ‘YYYYMM’. So, running today, by default, would always return for the prior ‘YYYYMM’ or ‘201002’. Moving on to my filter logic I deduced that the flow needed to look like so:

If @Prompt = The Default Value Provided

Then Assume Prior Period

Else

Use the User Defined Period

Easy enough, right? Now to apply this logic to a filder. I’m going to go ahead and throw my formula out there, then break it down. I’ll note that I’ve developed this in Oracle, but mix and match to your needs.

SOME_TABLE.PERIOD =

CASE

WHEN @Prompt(‘Enter Period as YYYYMM (or use default for current)’,’A’,’Some class\Some LOV’,mono,free,Not_Persistent,{‘Current Period’},User;0) = ‘Current Month’ THEN TO_CHAR(ADD_MONTHS(SYSDATE,-1),’YYYYMM’)

ELSE

@Prompt(‘Enter Period as YYYYMM (or use default for current)’,’A’,’Some class\Some LOV’,mono,free,Not_Persistent,{‘Current Period’},User;0)

END

Let me explain before your eyes glaze over and you click the next results in your search. It really kind of happens like this:

  1. Prompt the user for the desired period. Within that prompt, supply a default value of ‘Current Period’ (happens right after the “Not_Persistent” argument. This prompt also allows the user to seletct from a properly formatted LOV.
  2. If the user just selected ‘Current Period’, which ultimately means they didn’t touch it, that is passed to the prompt which triggers the SQL to get the current date, use the Oracle ADD_MONTHS function to subtract a month, and format it as ‘YYYYMM’.
  3. If the user did select a value, it obviously is not equal to ‘Current Month’ so that string is actually what is used in the query.

I’m sure there are many many other applications as well to this, and probably some limitations. The great benefit here is what I said in the beginning. This filter can now drive user experience for selecting values for this report on demand while it works perfectly for scheduling.

2 thoughts on “Optional Prompts with Default Values in Designer”

  1. Thanks a lot ERIC.

    I made little changes to yours to have it working with the Calendar prompt.
    Below is a one i used for getting current day to 90 days previous

    @Select(Transaction Date\Transaction Date) BETWEEN ( CASE
    WHEN @Prompt(‘Select Start Date (or leave blank for 90day previous)’,’D’,,mono,free,Not\_Persistent,{’01/01/1800′},User:0;0) = ’01/01/1800 00:0:0′ THEN (getdate()-90)
    ELSE
    @Prompt(‘Select Start Date (or leave blank for 90day previous)’,’D’,,mono,free,Not\_Persistent,{’01/01/1800′},User:0;0)
    END)
    and ( CASE
    WHEN @Prompt(‘Select End Date (or leave blank for current)’,’D’,,mono,free,Not\_Persistent,{’01/30/1800′},User:1;0) = ’01/30/1800 00:0:0′ THEN (getdate())
    ELSE
    @Prompt(‘Select End Date (or leave blank for current)’,’D’,,mono,free,Not\_Persistent,{’01/30/1800′},User:1;0)
    END)

    Just a quick question the 0 you are using after user:0 at the end of the @prompt syntax is it for making the prompt optional

Leave a Reply