I'm just wondering if LastFullMonth can be pushed down to the database server when using it in the record selection, or do I have to write on optimised version of it in SQL?
I don't think so-since it is a CR function and CR functions don't usually make to the SQL. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
In SQL 2000 and some other databases you can create userdefined functions. I've never tried it, but you could use this to define a server side function that did lastFullMonth. Andrew Baines
Chase International
I don't really have much to do with the SQL side of things though, I'm just trying to optimise my Crystal functions as much as possible to speed up processing time.
It can pass it on my Oracle database, I just tested it.
{SOL_VW_RPT_CONTACT_DTL.UTCSTARTTIME} in lastfullmonth
will net:
WHERE
"SOL_VW_RPT_CONTACT_DTL"."UTCSTARTTIME" >= TO_DATE ('01-05-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"SOL_VW_RPT_CONTACT_DTL"."UTCSTARTTIME" <= TO_DATE ('31-05-2002 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
Post your entire record selection criteria and let's have a go at it.
Or, you might create start and end date formulas as in the following:
(note I use parameters to drive this so the user can select lastfullmonth, lastfullyear, etc, here's the lastfullmonth)
@startdate
If {?Predefined Date Range} = "Last Month" Then
If Month (CurrentDate) = 1 Then
DateTime((Year(CurrentDate)-1),12,1,0,0,0)
Else
DateTime(Year(CurrentDate),Month(CurrentDate)-1,1,0,0,0)
@enddate
If {?Predefined Date Range} = "Last Month" Then
If Month (CurrentDate) = 1 Then
DateTime(Year(CurrentDate)-1,12,1,23,59,59)
Else
DateTime(Year(CurrentDate),Month(CurrentDate),1,23,59,59)-1
record selection criteria:
(
({YourTable.YourDate} >= {@Date Range Start})
and
({YourTable.YourDate} <= {@Date Range End})
)
Note the parens.
My requirements and record selection criteria are much more elaborate than this, and I ALWAYS get all of the SQL to pass.
The issue is weather you can use it in a record selection formula, it weather it can be passed to and processed on the server, for performance considerations. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.