Parameter on Date and Time selection Range

[bpvsc]

I have a report in Crystal 8.5 and I am trying to input parameters to include from 1 date and time to another date and time. The date and time fields are numeric(AS400) and seperate fields. I want Crystal to include records between these date and time fields. There may be an issue where the parameter is 10:30 pm of one day to 6:30 am of next day(shift reporting) or from 5:30 pm of on til 5:29 pm of another. I cannot get the selection statement correct. Would appreciate any help.
{?Startdate}={O2OPUT00.O2PRDT} and
{O2OPUT00.O2PRTM}>=173000 or
{?Stopdate}={O2OPUT00.O2PRDT} and
{O2OPUT00.O2PRTM}<=173000 and
{O2OPUT00.O2PAKU} > 0.00
The above code works for 1 specific day but I need either for several days or specific hours within that day.
Thabnks

 

[synapsevampire]

To start with, your start and end dates should be >= and <= respectively.

Consider using a date range parameter:

{O2OPUT00.O2PRDT} in {?MyDateSelectionparm}

would then return all of the rows matching the parameter.

You logic seem convoluted, perhaps yo can explain what it is you wish to do with this record selection criteria.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[bpvsc]

Thanks for the response. Now let me explain....
I have a numeric date field and a numeric time field in AS400. I want to construct a parameter(s) that will return a report based on date and time. Since I am reporting with shifts, the enduser will need the ability to use a parameter such as 6:30am to 2:30pm the same day. I would also want the report to show a full 24 hour timespan or a week to date timespan based on parameter.
{?Startdate}={O2OPUT00.O2PRDT}- this is my start date
{O2OPUT00.O2PRTM}>={?Starttime}or - this is my start time
{?Stopdate}={O2OPUT00.O2PRDT} and - this is my end date
{O2OPUT00.O2PRTM}<={?Stoptime} - this is my end time. Is it better to create a date/time field for this for response time?
Thanx again for assistance

 

[Naith]

bpvsc,

To answer your last question: yeah, if you had your data in date/time format, your reporting life would be a little bit easier here. But that isn't to say that you can't get what you want based on your current architecture.

You said in your initial post that you wanted Crystal to 'include records between these date and time fields'. Based on your selection criteria, Crystal's only going to return records with dates which exactly match the Start/Stop parameters. You should switch your Start/Stop parameter criteria to:
{?Startdate} >= {O2OPUT00.O2PRDT}
{?Stopdate} <= {O2OPUT00.O2PRDT}

You haven't illustrated any time parameters in your example. I'm sure you already realise that you would need to replace your hardcoded 173000 with a couple of time parameters.

Consider making the default value of the first time parameter midnight, and the value of the second 11:59pm (or 0 and 235959 if they're going to be numerical parameters), so if they aren't filled, (when reports are just being date driven) it won't matter.

Whip out your 'or' in the selection, because otherwise your report will just get everything after the start date, or everything before the stop date.

I guess your end result would look something like:

{O2OPUT00.O2PRDT} >= {?StartDate} and
{O2OPUT00.O2PRTM} >= {?StartTime} and
{O2OPUT00.O2PRDT} <= {?Stopdate} and
{O2OPUT00.O2PRTM} <= {?StopTime} and
{O2OPUT00.O2PAKU} > 0.00

Naith

 

[bpvsc]

This works great if I use it within ths same date and bypass time but if I have an instance to report from 1730 of one date til 1729 of the next day I get no data returned. Here is my statement:
{?Status} = {?Status} and
{@App Status} and
{AMAPPN00.AMAPDT}>={? startDate} and
{AMAPPN00.AMAPTI}>={?starttime} and
{AMAPPN00.AMAPDT}<={?enddate} and
{AMAPPN00.AMAPTI}<={?endtime} and
{AMAPPN00.AMSHMT} > &quot;&quot;
Any suggestions?....Thanks

 

[synapsevampire]

Use a DATETIME parameter, and then build a proper datetime by concatentaing the date and time fields from your database for use by the Record Selection Criteria.

So:

{@DatabaseDatetime} formula would be:

datetime({AMAPPN00.AMAPDT}+{AMAPPN00.AMAPTI})

You'll need to do a little massaging here depending upon your format, check out the help for this.

You may gain performance by using a SQL Expression if CR allows, or better yet by creating a proper DATETIME in the database.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

You don't need

{?Status} = {?Status} and
{@App Status}

It's just

{?Status} = {@App Status}

Blast. I've just seen the light...
If you're going to be looking for this report to reflect date ranges greater than one day AND you try to include time values other than the default midnight and 23:59, you're going to run into problems.

In your case, the report has run off to the database thinking that it's got to bring back everything where the time is greater than 5.30pm but earlier than 5.29pm - which obviously will return nothing, regardless of the dates being entered or not.

I think you're going to have to lose your time parameter functionality, and just drive the report on dates - because you don't have the luxury of working with datetime fields.

Once you return all the data matching the date criteria to the report, use a formula to force time criteria.

if {AMAPPN00.AMAPDT} = {? startDate} and
{AMAPPN00.AMAPTI}>={?starttime} then
{AMAPPN00.FIELD}
else
if {AMAPPN00.AMAPDT} = {?enddate} and
{AMAPPN00.AMAPTI}<={?endtime} then
{AMAPPN00.FIELD}
else
if {AMAPPN00.AMAPDT} > {? startDate} and
{AMAPPN00.AMAPDT} < {?enddate} then
{AMAPPN00.FIELD}
else '';

I thought this was all a bit too easy for you using numbers as dates and seperate numbers as times, but I guess I wasn't thinking clearly enough to see you running into that problem.

My sincerest apologies about that.


Naith

 

[Naith]

Your fields are numerical. If you concatenate them like datetime({AMAPPN00.AMAPDT}+{AMAPPN00.AMAPTI})
it'll actually add them up.

If you can convert them totext, fair enough. But if it's possible to do all this on the database, that would certainly get my vote.

Naith

 

[synapsevampire]

Naith: Why not just convert the date and time fields to a datetime using the datetime() in CR and use that?

If performance is a concern, do it using a SQL Expression, or back on the database.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[bpvsc]

Please explain the following field({AMAPPN00.FIELD}) I am not understanding the reason for it and what it should contain. Thanks for being so patient.

 

[Naith]

({AMAPPN00.FIELD}) is whatever the name of the field is that you need to see displayed. Replace it with your real database field.

Naith

 

[bpvsc]

OK I understand that but I have used that field as a datetime field. Now I am getting error message that a date time is required at the end of my statement.I know it must be something simple I am missing but I must not see the forest for the trees!!! Help
{?Status} = {?Status} and
{@App Status} and
{AMAPPN00.AMAPDT}>={? startDate} and
{AMAPPN00.AMAPDT}<={?enddate} and
{AMAPPN00.AMSHMT} > &quot;&quot; and
if {AMAPPN00.AMAPDT} = {? startDate} and
{AMAPPN00.AMAPTI}>={?starttime} then
{@Appdatetime}
else
if {AMAPPN00.AMAPDT} = {?enddate} and
{AMAPPN00.AMAPTI}<={?endtime} then
{@Appdatetime}
else
if {AMAPPN00.AMAPDT} > {? startDate} and
{AMAPPN00.AMAPDT} < {?enddate} then
{@Appdatetime}
else '';

Thank goodness it's Friday.

 

[Naith]

Amen to that.

Your date expectancy problem is caused by your last else. else '' expects you to have worked with strings, but if you're placing date fields, just try turning '' into Date(9999,9,9) for now or remove the last else altogether, and you should be okay.

By the way, at the beginning of your formula {?Status} only needs to equal {@App Status}, not {@App Status} and {?Status}. Additionally, take out these two lines:

{AMAPPN00.AMAPDT}>={? startDate} and
{AMAPPN00.AMAPDT}<={?enddate} and

So your formula now looks like:

{?Status} = {@App Status} and
{AMAPPN00.AMSHMT} > &quot;&quot; and
if {AMAPPN00.AMAPDT} = {? startDate} and
{AMAPPN00.AMAPTI}>={?starttime} then
{@Appdatetime}
else
if {AMAPPN00.AMAPDT} = {?enddate} and
{AMAPPN00.AMAPTI}<={?endtime} then
{@Appdatetime}
else
if {AMAPPN00.AMAPDT} > {? startDate} and
{AMAPPN00.AMAPDT} < {?enddate} then
{@Appdatetime}
else Date(9999,9,9);

Have a good weekend, if we don't speak again today.

Naith