Wednesday, December 2, 2015

Crystal Reports : Using SQL Expressions


I'll be honest, I rarely use these. But they can be really powerful on a MSSQL back-end. The first thing you need to know is that they allow you to submit TSQL directly to the server.

So, if I was after the Year value of a date field, the Crystal Formula would look like this;

YEAR({TABLE.DATEFIELD})

...where the SQL Expression would use native Transact SQL, like this;

DATEPART(yyyy, TABLE.DATEFIELD)

The big difference is how these two recordsets are generated. If I plug this into the Select Expert using Crystal Formulas, ALL records are downloaded from the server, and THEN the formula is processed. If you use the SQL Expression, it becomes a part of the Crystal SQL Statement, and only SELECTs the appropriate rows.

But it wouldn't work with, say, Access, or Interbase. The SQL Expressions are server-specific. What works with MSSQL may need a different syntax for Oracle or mySQL. But if you're reporting against millions of rows, this could dramatically reduce refresh times.

I've also seen it used in hyper-specialized environments to call Custom SQL Functions, as the SQL Expression is just a code-delivery system. Any exposed SQL object, including three-part qualifiers, should be allowed. I.e. DATABASE.DBO.TABLE.

3 comments:

aliya seen said...

By learning these programs we can laso have mobile usability guidelines forparticulr reason. This is positive for website.

caiyan said...

gucci outlet
pandora
cheap uggs
birkenstocks
birkenstock shoes
ugg outlet
true religion outlet
ugg boots
abercrombie kids
rolex daytona
20161018caiyan

oracle fusion procurement online training said...

Thanks for posting this weblog i, in truth, loved it and placed up a few unique blogs approximately oracle........visit our internet web page related to oracle fusion procurement training for more information please visit site
oracle fusion procurement online training
oracle fusion procurement training