SQL Expression Confusion...

[gabster]

Hello everyone...

I am trying to grasp the concept of using SQL expressions in my reports.
I understand so far that the SQL expressions are done on the server instead on the client machine, etc.
Also I have an ODBC connection to my SQL server.

Now, for some reason I do not have the function tree activated in my SQL Expression Editor. I see the plus sign ("+&quot near it but when I click on it, it does nothing.

Also it seems that the SQL expression uses some sort of a language (like Cristal Syntax) - is this right?

Is there any resource online (beside cristaldecisions.com - where I didn't find anything)...

Thanks a lot,
Gabster

Thanks a lot,
Gabi.

 

[sidanshu]

hi Gabi
Try the site
msdn.microsoft.com and search for "crystal reports and vb"
you may get some help.
Anshu

 

[rhinok]

Good morning,

The Functions that are available in SQL Expressions are database-specific functions, meaning that they are true SQL functions.

The functions that are available to you are limited via your ODBC or Native Driver connection, therefore, there is no documentation from Crystal Decisions because the available functions are determined and limited by your driver. For example, I connect to Oracle 8 via Native Drivers so I can only use a specific set of Oracle Functions.

SQL Expressions can only use those functions that are available. You cannot use Crystal Syntax, If-then-else statements, etc... You cannot see Crystal Formulas in the SQL Expression editor - you can only see and use database fields.

Because of these limitations, many people don't or can't use SQL Expressions in their reports, however, I do find them to be useful on occasion - especially if you build your reports off straight tables.

1) SQL Expressions are processed Server-side. The will be included as fields in the SELECT clause of your SQL Statement.

2) Crystal Formulas can't be used in SQL Expressions, however, SQL Expressions can be used in Crystal Formulas.

3) SQL Expressions can be used in the Record Selection Editor.

4) SQL Expressions can be used as Fields on your report. Since they are processed server-side, you can create additional fields from your tables that would otherwise be generated as client-side formulas.

Following is an example of two SQL Expressions that I wrote and how I use those expressions:

\\%EndDate1
LAST_DAY(ADD_MONTHS("Table"."Date_Field", 1))

\\%EndDate2
LAST_DAY(ADD_MONTHS("Table"."Date_Field", 2))

\\@QtrEndDate
\\I have a chart that is based on Quarter End Date that requires labels in 'MMM-yy' format
\\Notice that I reference the SQL Expressions as if they were database fields.
DateVar QtrEndDt;
StringVar QtrEndTxt;

If
Month({Table.Date_Field}) In [1,4,7,10]
Then
QtrEndDt := Date({%EndDate2})
Else
If
Month({Table.Date_Field}) In [2,5,8,11]
Then
QtrEndDt := Date({%EndDate1})
Else
If
Month({Table.Date_Field}) In [3,6,9,12]
Then
QtrEndDt := {Table.Date_Field};

QtrEndTxt := ToText(QtrEndDt,'MMM-yy');

QtrEndTxt

 

[gabster]

Thanks a lot for the help provided...

Would you suggest to use SQL Expression fields in a ODBC connection in order to increase server side processing performance?

Thanks you,
Gabster

 

[rhinok]

I would recommend SQL Expressions, when appropriate, if you are building your reports from actual Tables (as opposed to custom Views, for example) whether you connect via ODBC or Native Drivers.

When is it appropriate to use SQL Expressions? The easiest example is if you can substitue a SQL Expression for a formula, thereby substituting server-side processing for client-side processing. Simple Example:

//@ConcatenatedStringFormula
{table.string1} + {table.string2}

VS.

//%ConcatenatedStringExpression
("table"."string1&quot + ("table"."string2&quot

Both the formula and the expression return the same value, however, the expression is processed more efficiently. This, in turn, can lead to better report performance.