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.