Display date based on the parameter fields

[jsttuchm3]

I have an issue in applying a condition based on the parameters provided.

StartDate & EndDate are parameters where data is provided and the report is supposed to extract data where createddate from database falls with in the provided date range.

code written in record selection section is a follows

If ISNULL({?ShipmentCrtdDt}) or ISNULL({?ShipmentEndDt}) THEN
{FMX_SHPM_OPMZ_RPT.SHPM_CRTD_DTT} < currentdate
ELSE
cstr({FMX_SHPM_OPMZ_RPT.SHPM_CRTD_DTT},'MM/dd/yyyy HH:mm',0,"") in [cstr({?ShipmentCrtdDt},'MM/dd/yyyy HH:mm',0,"") to cstr({?ShipmentEndDt},'MM/dd/yyyy HH:mm',0,"")]

This is extracting all the data from the database and not based on date parameters.

How do i achieve this functionality in CR XI.

There are other parameters too on which the data is filtered apart from the date parameters.

The code in record selection part is as follows

If UCase({?DistributionCenter}) = "ALL"
Then {@DistCtr} Like '*'
Else UCase({@DistCtr}) = UCase({?DistributionCenter})
AND
If UCase({?CustomerName}) = "ALL"
Then {@CustName} Like '*'
Else UCase({@CustName}) = UCase({?CustomerName})
AND
If {?PlanID} <> 0
THEN {FMX_SHPM_OPMZ_RPT.PLAN_ID} = {?PlanID}
ELSE {FMX_SHPM_OPMZ_RPT.PLAN_ID} > 0
AND
If ISNULL({?ShipmentCrtdDt}) or ISNULL({?ShipmentEndDt}) THEN
{FMX_SHPM_OPMZ_RPT.SHPM_CRTD_DTT} < currentdate
ELSE
cstr({FMX_SHPM_OPMZ_RPT.SHPM_CRTD_DTT},'MM/dd/yyyy HH:mm',0,"") in [cstr({?ShipmentCrtdDt},'MM/dd/yyyy HH:mm',0,"") to cstr({?ShipmentEndDt},'MM/dd/yyyy HH:mm',0,"")]

Data is filtered based on the other parameters but date functionality is not working as desired.

Can someone help in this.

Thanks,
RP

 

[lbass]

Don't convert the dates to strings. Just use:

{FMX_SHPM_OPMZ_RPT.SHPM_CRTD_DTT} in {?ShipmentCrtdDt} to {?ShipmentEndDt}

Also as far as I know, you cannot have a null date parameter, so you would have to establish a defaults like 1999-9-9. Then in the formula use:

(
if {?ShipmentCrtdDt} <> date(1999,9,9) and
{?ShipmentEndDt} <> date(1999,9,9) then
{FMX_SHPM_OPMZ_RPT.SHPM_CRTD_DTT} in {?ShipmentCrtdDt} to {?ShipmentEndDt} else
if {?ShipmentCrtdDt} = date(1999,9,9) and
{?ShipmentEndDt} = date(1999,9,9) then
{FMX_SHPM_OPMZ_RPT.SHPM_CRTD_DTT} < currentdate
)

You also should have parens around each if/then section

(
If UCase({?DistributionCenter}) = "ALL"
Then {@DistCtr} Like '*'
Else UCase({@DistCtr}) = UCase({?DistributionCenter})
)
AND
(
If UCase({?CustomerName}) = "ALL"
Then {@CustName} Like '*'
Else UCase({@CustName}) = UCase({?CustomerName})
)
AND
(
If {?PlanID} <> 0
THEN {FMX_SHPM_OPMZ_RPT.PLAN_ID} = {?PlanID}
ELSE {FMX_SHPM_OPMZ_RPT.PLAN_ID} > 0
)
AND
(
if {?ShipmentCrtdDt} <> date(1999,9,9) and
{?ShipmentEndDt} <> date(1999,9,9) then
{FMX_SHPM_OPMZ_RPT.SHPM_CRTD_DTT} in {?ShipmentCrtdDt} to {?ShipmentEndDt} else
if {?ShipmentCrtdDt} = date(1999,9,9) and
{?ShipmentEndDt} = date(1999,9,9) then
{FMX_SHPM_OPMZ_RPT.SHPM_CRTD_DTT} < currentdate
)

-LB

 

[jsttuchm3]

Hi LB,

When the formula is validated it throws an error that
" This field name is not know " and highlights this
{?ShipmentEndDt} in the second line.

if {?ShipmentCrtdDt} <> date(1999,9,9) and
{?ShipmentEndDt} <> date(1999,9,9) then

 

[lbass]

This is the parameter you showed in your first post. Why would it not be 'known' to you?

-LB

 

[jsttuchm3]

I know what was the problem was. Fixed it and it works.

Thanks for the help LB