How to Default sysdate (Current Date) for Crystal Report and PeopleTools Query prompt automatically

If you have a Query or Crystal Report, that has a date prompt and you want to schedule the report daily, so that date value is defaulted to sysdate (Current date), you can do this as follows.

Open the Query in PeopleTools Query tool.

Go to Criteria tab. Right Click on Expression2 Column and Select Expression or Expr-Expr if you are using between operator.

Type the following in Edit Expression.

Type	Default Date	Expression
Criteria	current date	decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate),:1)
	current date - 1	decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate)-1,:1)
	current date + 1	decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate)+1,:1)

 

replace :1 with the actual prompt value. You must first create this prompt.

Now you need to pass 01/01/1900 as an input parameter if you want to run the query for current date. Passing any other values will make the query run for that date.

This way you can achieve both i.e. run the query for a user selected date or run a query for current date which can be used to schedule. Note : you can use any date as a replacement for sysdate and not just 01/01/1900.

Also you can default it to any day relative to current date for e.g. trunc(sysdate) - 1 or trunc(sysdate) + 1 etc.

If you want to know what parameter user has passed in your report, you can add the following expression as field in your query.

Create an expression of type Date in left hand side Under Expressions.

Type	Default Date	Expression
Field	Current Date	decode(:1,'1900-01-01',to_char(trunc(sysdate),'YYYY-MM-DD') ,:1)
	Current Date - 1	decode(:1,'1900-01-01',to_char((trunc(sysdate)-1),'YYYY-MM-DD') ,:1)
	Current Date + 1	decode(:1,'1900-01-01',to_char((trunc(sysdate)+1),'YYYY-MM-DD') ,:1)

I have tested this on Oracle 9.2.0.8 and PeopleTools 8.48.12.