How Date Parameter leave Null

[kamkaro]

Hi All,
I am using Crystal Reports 2008, I have two parameters setup ... one is for a string and one is a date. I added these to the Select Expert and if they are both filled out by the user then the report works fine ...

For scheduling, if date parameter leave blank than date parameter should pull all the last Month data, but when I am leaving date Parameter blank, I got error "Date parameter is empty". I read something about making the date parameter into a string and then converting it afterwards but I really would like the user to be able to choose a date from a "calendar" versus having to type it in ...

Seems straightforward but I am not getting it .... Tried this but it came up empty if the date was ignored ..

[highlight #204A87]Where b.START_DT >= (if (isnull({?Start_date}) or {?Start_date} = '') then(select TRUNC(ADD_MONTHS((SYSDATE,yyyy/mm/dd), -1),'Month') from dual) else {?Start_date})[/highlight]


any insight is appreciated ...

 

[hilfy]

I'm assuming that you're using a command instead of joining tables - that's what the "where" clause you've posted indicates...

Try something like this instead:

Where b.START_DT >= Decode({?Start_Date}, null, TRUNC(ADD_MONTHS((SYSDATE, yyyy/mm/dd), -1, 'Month'), "", TRUNC(ADD_MONTHS((SYSDATE, yyyy/mm/dd), -1, 'Month'), {?Start_Date)

Since you are in the context of a query, you don't need to use the "Select ... from Dual" construct.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[kamkaro]

Hi Dell,
Yes you are right, I am using the command expert. After modified my code according to your suggestion, I am still getting the "Empty parameter value" error.

What should I do?

Thanks.

 

[ianoctdec]

Create a new parameter in the command (I name it ?RunningType), with two default values: OnDemand and Recurring; then modify your WHERE clause as:
b.START_DT >= Decode('?RunningType', 'OnDemand', {?Start_Date), TRUNC(ADD_MONTHS((SYSDATE, yyyy/mm/dd), -1, 'Month')))

When you schedule the report, select Recurring for ?RunningType, and fill in any dtae for the ?StartDate parameter, so that Crystal won't complain about a null parameter value.

Dana