Crystal reports parameters using a command to an Oracle database

0
votes

I am trying to create a report that has three optional parameters using a command in Crystal Reports 2008. The only one I can't get to work is the date parameter (seems Oracle does not treat dates the same as SQL Server).

I want the user to be able to pick and choose which parameter they would like to use; one, two, or all three. It works beautifully when I run it in TOAD and hard code the dates, but it will not run in Crystal reports using the syntax to create the parameters.

I have done this a thousand times in a SQL server environment but can't get it to work in Oracle.

The field is type is DATE in the Oracle table.

Here is the syntax from my Report command for the 3 parameters:

AND ( ( CLIL. ITEM_TAG IN ('{?tag}') OR CLS.DESCRIPTION IN( '{?desc}')
OR trunc (CLIL.ISSUE_DATE) BETWEEN to_date ('{?StartDate}', 'mm/dd/yyyy') and to_date ('{?EndDate}', 'mm/dd/yyyy' )))
1
oracle11gcrystal-reports-2008

1 Answers

0
votes

Crystal reports doesn't accept the Oracle syntax.. you need to use the functions provided by the CR to do manuplations of dates... and then use that in CR.

Create a Start Date and End Date as date parameters in CR and then use those.

(CLIL.ISSUE_DATE) >= {?StartDate} and (CLIL.ISSUE_DATE) < {?EndDate}

Here start date and end date are Date datatype parameters.

if (CLIL.ISSUE_DATE) is a datetime parameter then use the function Cdate provided by the crystal.