Friday, December 07, 2007

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.

4 comments:

Jeromy McMahon said...

Thanks for posting this! Very good information.

Jeromy McMahon
http://jmcmahon33.blogspot.com

Larry said...

Really good informative information. Latest technology related topics are not covered so I liked http://peoplesoftsupport.blogspot.com for new Fusion related DEMOs.

Larry said...

Nicely explained article. I also visited a site that is proving free PeopleSoft training. Check it out - http://www.itwisesolutions.com/PsftTraining.html

Krishna said...

Thank you for the Post. Was very useful.