Skip to Content
0

Date Range optional prameter

Jun 02, 2017 at 06:41 PM

50

avatar image

Optional Parameters and Nulls

Using Crystal Version CR Developer Version 14.0.4.738 RTM

Designing a report that has 3 different date fields and when it is ran, the user will only be providing a date range for 1 of the 3 fields.

Someone mentioned “optional parameters” and they thought this may help.

Been reading up on them and it seems like it would, but I have not been able to get the examples to work with my selection formula.

My current selecting formula is:
{MyTable.Ship Date} in DateTime ({?BeginShipDate}) to DateTime ({?EndShipDate}) and
{MyTable.State} like {?State} and
{MyTable.Ticket Number} like {?Ticket} and
{MyTable.Date Call Received} in DateTime ({?BeginDateCallReceived}) to DateTime ({?EndDateCallReceived}) and
{MyTable.Date Paid} in DateTime ({?BeginDatePaid}) to DateTime ({?EndDatePaid})

What would the syntax be to allow for no dates to be provided for 2 of the 3?

Thanks
Bennie

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Brian Dong Jun 02, 2017 at 09:59 PM
0

Hi Ben,

When I create optional parameters, I add a default value to it. So for dates, I use a date completely out of range like 9999-12-31.

Now you can have your selection formula check for that value and query accordingly like:

(If {?BeginShipDate} <> Date (9999, 12, 31) And {?EndShipDate} <> Date (9999, 12, 31) then 
    {MyTable.Ship Date} in DateTime ({?BeginShipDate}) to DateTime ({?EndShipDate}) 
Else {MyTable.Ship Date} < DateTime (9999, 12, 31, 0, 0, 0)) and

{MyTable.State} like {?State} and
{MyTable.Ticket Number} like {?Ticket} and 

(If {?BeginDateCallReceived} <> Date (9999, 12, 31) And {?EndDateCallReceived} <> Date (9999, 12, 31) then 
    {MyTable.Date Call Received} in DateTime ({?BeginDateCallReceived}) to DateTime ({?EndDateCallReceived}) 
Else {MyTable.Date Call Received} < DateTime (9999, 12, 31, 0, 0, 0)) and

(If {?BeginDatePaid}<> Date (9999, 12, 31) And {?EndDatePaid} <> Date (9999, 12, 31) then 
    {MyTable.Date Paid} in DateTime ({?BeginDatePaid}) to DateTime ({?EndDatePaid}) 
Else {MyTable.Date Paid} < DateTime (9999, 12, 31, 0, 0, 0)); 

Not the parenthesis around each of the If statements.

Good luck,

Brian

Share
10 |10000 characters needed characters left characters exceeded
Ben Marthin Jun 06, 2017 at 02:40 PM
0

Thanks Brian,

Have not tried your suggestion but I do not see any allowance for Null or empty date fields. Before I posted my question I believe I tried something similar and records were not returned as the field was ether null or empty.

Does your suggestion account for null or empty fields?

Again thanks for your time and efforts.

Ben

Share
10 |10000 characters needed characters left characters exceeded
Brian Dong Jun 06, 2017 at 08:19 PM
0

Hi Ben,

We can append an or to the condition to check for null dates using Date (0, 0, 0) like:

(If Not ({?BeginShipDate} In [Date (0, 0, 0), Date (9999, 12, 31)]) And Not ({?EndShipDate} In [Date (0, 0, 0), Date (9999, 12, 31)]) then 
    {MyTable.Ship Date} in DateTime ({?BeginShipDate}) to DateTime ({?EndShipDate}) 
Else {MyTable.Ship Date} < DateTime (9999, 12, 31, 0, 0, 0)) and
{MyTable.State} like {?State} and
{MyTable.Ticket Number} like {?Ticket} and 
(If Not ({?BeginDateCallReceived} In [Date (0, 0, 0), Date (9999, 12, 31)]) And Not ({?EndDateCallReceived} In [Date (0, 0, 0), Date (9999, 12, 31)]) then 
    {MyTable.Date Call Received} in DateTime ({?BeginDateCallReceived}) to DateTime ({?EndDateCallReceived}) 
Else {MyTable.Date Call Received} < DateTime (9999, 12, 31, 0, 0, 0)) and
(If Not ({?BeginDatePaid} In [Date (0, 0, 0), Date (9999, 12, 31)]) And Not ({?EndDatePaid} In [Date (0, 0, 0), Date (9999, 12, 31)]) then 
    {MyTable.Date Paid} in DateTime ({?BeginDatePaid}) to DateTime ({?EndDatePaid}) 
Else {MyTable.Date Paid} < DateTime (9999, 12, 31, 0, 0, 0)); 

The formatting got messed up in the copy-paste but it's now checking for a NULL date as well as the default date for each.

Brian

Share
10 |10000 characters needed characters left characters exceeded
Ben Marthin Jun 14, 2017 at 08:00 PM
0

Brian,

Just now circling back to this report.

I tried your suggestion and when the report runs after providing the values for the parammeters, it is displaying an error: "Parameter has no value" and one of the parameter fields Ieft blank is highlighted. The parameter is set to optional.

Any thoughts as to what the issue may be and how to address?

Thanks

Ben

Share
10 |10000 characters needed characters left characters exceeded