Crystal Reports 10: Is it possible to call a custom function from a command

Question

I have created 4 custom functions in Crystal Reports 10. These take as input a date-time value from a DB record, and determine whether that date-time falls inside the current Fiscal Year or the previous Fiscal Year.

These calculations were previously executed as stored procedures on an SQL Server but we are moving to another ticketing application (hosted at a vendor site) and do not currently have DB to do these calculations on.

Here is an example of one of these functions:

// Function name: isCloseDateWithinCurrentFY
Function (DateTimeVar closeTime)
// This replaces dbo.fn_FiscalYear
// Determine if the incident close date falls inside the current Fiscal Year
DateTimeVar startCurrentFiscalYear;
NumberVar currentMonth;
StringVar returnVal;

currentMonth := Month(CurrentDate);

If currentMonth >= 2 Then
    startCurrentFiscalYear := Date(Year(CurrentDate), 2, 1)
Else
    startCurrentFiscalYear := Date(Year(CurrentDate)-1, 2, 1);

If (closeTime >= startCurrentFiscalYear) Then
    "T"
Else
    "F";

When these calculations were on the SQL Server, they were utilized from a Crystal Report SQL command

SELECT
category,
subcategory,
close_time,
tyCount
FROM (
    SELECT 
        category=ISNULL(UPPER(category),'*Unspecified'),
        subcategory=ISNULL(UPPER(subcategory),'*Unspecified'),
        tyCount=SUM(CASE WHEN dbo.fn_FiscalYear(close_time)='T' THEN 1 ELSE 0 END)
    FROM 
        incident_tickets
    GROUP BY
        UPPER(category),
        UPPER(subcategory)
) tickets
WHERE
    tycmCount>0
ORDER BY
    category,
    subcategory

In my case I would like to replace the call to dbo.fn_FiscalYear with a call to my custom function isCloseDateWithinCurrentFY.

But is it possible to call a custom function from an SQL command?

Or is there some other way to restrict the returned records based on a calculation made on Crystal Report side?

TIA

score 0 · Accepted Answer · answered May 31 '13 at 16:47

0

No, you cannot use any sort of Crystal code in a SQL command since they're two completely different beasts. The SQL Command is basically just the query that Crystal will send straight to the database in order to get the records to process, after which point those records will be available to use with Crystal code. The SQL server has no way to interpret the Crystal function and Crystal has no way of translating it into SQL.

The easiest way to do this would probably be to just replace dbo.fn_FiscalYear with more SQL hard-coded directly into the query. More specifically, translate isCloseDateWithinCurrentFY into the equivalent SQL.

answered May 31 '13 at 16:47

Ryan

7,212
1
17
30

Thats what I thought, many thanks for your input Ryan. Would it perhaps be an alternative to use the custom function inside Select Expert? – user100487 May 31 '13 at 17:02
You can use Crystal code in the select expert but this won't be as fast as translating it into SQL since the actual query won't change and the processing will still happen on the client side. This question should help: http://stackoverflow.com/questions/2678869/round-date-to-fiscal-year – Ryan May 31 '13 at 17:04

Crystal Reports 10: Is it possible to call a custom function from a command

1 Answers1