Optional Prompts with Dynamic Default Value for Scheduling

For my client I had the following requirements:

A.  For any given report with Date prompts -  when the report is scheduled it should automatically populate the date prompt with the previous month.  So, if  a report is run on June 1, 2010, it should have an effective date of May 2010.

B.  For these reports with Date prompts, the users should also be able to refresh the report in InfoView and enter a date and have the report refresh.

In my environment the data source is MSSQL Server 2005.  I found an example of how to achieve this functionality with an Oracle back-end here:

http://evtechpartners.com/home/blog/13-optional-prompts-with-default-values-in-designer

My example leverages the concepts in the above link - but it is modified for SQL Server.

For this example, we have a MMMMYY date dimension (i.e. 201004)

Here are the steps I used to achieve the desired functionality:

1.  Create a new Condition in your Universe.  In this example, I am creating the condition in the SunsetHill_Accounting_Expenses class.

2.  Enter a name for the condition - and a description - then click on the double-arrow icon to enter the formula.

 3.  Here is the formula:

(Note:  In my database - SunsetHill_Accounting_Expenses stores the date dimension in YYYYMM format - substitute the appropriate value for your database)

SunsetHill_Accounting_Expenses.YearMonthCH =  
CASE

WHEN

@Prompt('Enter Period as YYYYMM (or use default for previous month)','A',
'Sunsethill Accounting Expenses\Yearmonthch',mono,free,Not_Persistent,{'YYYYMM'})= 'YYYYMM'

THEN

cast
(
 DATEPART("yyyy", DATEADD ("mm", -1, getdate()))
 as char(4)
)
+
CASE
     WHEN DATEPART("mm", DATEADD ("mm", -1, getdate())) < 10 THEN
  '0' +
  cast
  (
   DATEPART("mm", DATEADD ("mm", -1, getdate()))
   as char(2)
  )
     ELSE
  cast
  (
   DATEPART("mm", DATEADD ("mm", -1, getdate()))
   as char(2)
  )
END


ELSE

@Prompt('Enter Period as YYYYMM (or use default for previous month)','A',
'Sunsethill Accounting Expenses\Yearmonthch',mono,free,Not_Persistent,{'YYYYMM'})

END

*********************************

Here’s how it works:

i.  If the default value is used, there is SQL code to get the previous month in YYYYMM format.  When reports are scheduled, they will use the default value – which means that when the report is run, this calculation will be performed.

ii.  If the user runs the report in InfoView and selects a different date, that date will be used in the report.

4.  Parse the Query.  If you see the message below, everything is looking good.

 5.  Click Apply, then OK.

6.  The Condition now appears in the class.

 7.  To test the solution, I created a new WEBI report and added the Year Month Conditional Prompt condtion.

 8.  The first time I ran the report, I just left the default YYYYMM text. 

9.  As you can see, only transactions for March 2010 are appearing.  (This is because I ran the report in April 2010)

10.  the next time I ran the report, I entered 200912 as the YYYYMM.

 11.  As expected, only transactions from December 2009 appear in the report.