select expert date criteria not being used in query 1

[beckybear]

Hi all.

I have a report in crystal 7 going to an oracle database. Within the select expert, I've set up a few criteria and also have set up date criteria for a date range. The problem is that the report does not use this criteria, nor does is show up when "Show SQL Query" is chosen. I've not modified the SQL directly and I can add and delete other criteria with the select expert and the other criteria always shows up. I've verified the database and used the reset button in the Show SQL Query window and can't get the date to be recognized.

The formula shown in my select expert is:

{TABLE.AGENCY} = "ABC" and
{TABLE.ENTRYDATE} >= Minimum({?dateRange}) and
{TABLE.ENTRYDATE} < (Maximum({?dateRange}) + 1)

where dateRange is a Date Range parameter. I've also tried using separate start and end date parameters like:

{TABLE.AGENCY} = "ABC" and
{TABLE.ENTRYDATE} >= {?startDate} and
{TABLE.ENTRYDATE} < ({?endDate} + 1)

With this formula, the start date is recognized, but not the end date. Can someone please tell me what's wrong with my syntax?

Also, I'm adding one to the end date because entrydate is an oracle type date field with both date and time.

 

[synapsevampire]

It's probably because you're doing something to the
max(?daterange) (the +1).

Crystal is very particular about when it will pass SQL.

Try altering this by creating a formula (DON'T use variables in it!!!) to compute the start and end dates, and then use these formulas in your record selection criteria.

Here's an example of what I use to pass to Oracle successfully for a yesterday parameter choice:

@Date Range Start
// If Yesterday
If {?Predefined Date Range} = "Yesterday" Then
DateTime(Year(CurrentDate-1),Month(CurrentDate-1),Day(CurrentDate-1),0,0,0)

@Date Range End
// If Yesterday
If {?Predefined Date Range} = "Yesterday" Then
DateTime(Year(CurrentDate-1),Month(CurrentDate-1),Day(CurrentDate-1),23,59,59)

Record Selection Formula:
(
({SOL_VW_RPT_UNIV_SUM_DTL.UTCTIME} >= {@Date Range Start})
and
({SOL_VW_RPT_UNIV_SUM_DTL.UTCTIME} <= {@Date Range End})
)


This ALWAYS passes.

I spent a reat deal of time working out how to pass through SQL, and though I can occasionally do it other ways, this gives me the best overall assurance.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear Beckybear;

Are you stating that none of your criteria shows in the Where Clause in Show SQL Query?

If that is the case, make sure that you have Use Indexes or Server for Speed checked in the Report Options.

Crystal Help Text:

If you select Use Indexes Or Server For Speed (in the Report Options dialog box), the program adds an ORDER BY clause to the SQL statement and a WHERE clause for the record selection formula, if possible.

So if this option is not checked no where clause is generated!

Hope this helps,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[mbarron]

Try this variation to your original select expert posting.


{TABLE.AGENCY} = &quot;ABC&quot; and
{TABLE.ENTRYDATE} in Minimum({?dateRange}) to (Maximum({?dateRange}) + 1)

Mike

 

[beckybear]

to synapsevampire

Thanks for the information. I tried something similar to what you said and neither date criteria (start or end date) is used properly. Do I need some kind of evaluation time specified?

@startDate:

DateTime(Minimum({?dateRange}),Time(0,0,0))

@endDate:

DateTime(Maximum({?dateRange}),Time(23,59,59))

Select Expert Formula:

{TABLE.ENTRYDATE} >= {@startDate} and
{TABLE.ENTRYDATE} <= {@endDate} and
{TABLE.AGENCY} = &quot;ABC&quot;

Interesting, however, was the fact that I created a subreport within this report, passed the start and end date and used them as parameters to select from the same table in the same way and everything worked fine. Is there some kind of evaluation time function I need to include in the formulas?

to rosemaryl
Thanks. I already have that option turned on. I can see the where clause, I'm just not seeing my date criteria in the where clause.

to mbarron
Tried this and it didn't work, but thanks for your input.

 

[beckybear]

One more note:

I just realized that my subreport didn't exactly work perfectly like I said. Even though my select expert formula says

{TABLE.ENTRYDATE}

<=

 {?Pm-@endDate}

,
the actual SQL (under Show SQL Query) is shown as

TABLE."ENTRYDATE"

<

 ?

Am I going crazy? If I have less than or equal to typed in the select expert, why is it going through to SQL as just less than?

 

[lyanch]

If you have a date parameter that is used in a comparison with a datetime field Crystal is pretty smart about it.

datetimeField <= DateParameter

is converted automatically to

dateTimeField < (DateParameter + 1 day)

or
dateTimeField <= Parameter = to 11/10/2002

is shown in the sql as

dateTimeField < Date(2002,11,11,0,0,0)

It beats having to figure in the time portion yourself.

Lisa

 

[rogar]

<<If you have a date parameter that is used in a comparison with a datetime field Crystal is pretty smart about it. >>

Maybe in CR 7 it isn't quite as smart as it is in CR 8.x??
Becky, if your {Table.Date} is a datetime variable, you may have to use a datetime parameter or a formula that converts your date parameter to a datetime type.

 

[synapsevampire]

I've yet to be presented with a scenario where the SQL couldn't be passed (I've had a few folk challenge me), so I'd like to see the exact record selection criteria used, and the SQL generated.

Yours is slightly different from my example, though I have a similar report that uses a subreport as you do.

I would try altering your formula slightly to NOT use a time function and see if that passes. Also do the date testing by itself, one at a time, then add in the other criteria. Once you get one to work, it tends to be OK, but if something above breaks, *sometimes* it breaks the following pass through.

This is an example of my last week criteria within the @date range start formula, note it doesn't use a time function:

DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),0,0,0)-DayOfWeek(CurrentDate)-6

Also wrap each section of the record selection in parens as in the following example:

(
({SOL_VW_RPT_IDLE_REASON_SUM.LOCALTIME} >= {@Date Range Start})
and
({SOL_VW_RPT_IDLE_REASON_SUM.LOCALTIME} <= {@Date Range End})
)
and
(
If {?Team}[1] <> 'All'
and
{?Team}[1] <> ''
then
({SOL_VW_RPT_IDLE_REASON_SUM.TEAMNAME} in {?Team})
else
if {?Team}[1] = 'All'
or
{?Team}[1] = '' then
True
)
and
(
If {?Node}[1] <> 'All'
and
{?Node}[1] <> ''
then
({SOL_VW_RPT_IDLE_REASON_SUM.NODENAME} in {?Node})

else
if {?Node}[1] = 'All'
or
{?Node}[1] = '' then
True
)

I avoid using formulas and variables with anything used in the record selection criteria (and within formulas referenced when I can), though sometimes one or both CAN work, they seem to cause problems.

I wouldn't be surprised if you've already worked this out...

I had a host of suggestions when I started on the latest wave of multiple parameter reports, and all of them worked in limited scenarios, when it got complex, they'd all break, and my latest system isn't 100% foolproof, but I have the best results using it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[beckybear]

Thanks for everyone's help. It's working now. I ended up putting in blank date criteria and seeing that it indeed wanted DateTime(#,#,#,#,#,#) as input - thanks k