Crystal Reports: CAST function to modify data

SELECT "First" as rectype, field1 as a, field2 as b FROM firsttable

UNION ALL

SELECT "Second" as rectype, field3 as a, field4 as b FROM secondtable

The question now is when you get these data into your Crystal Report, what sort of field type appears in the "rectype" field?

The default is that the field a 254 character string field. If you want a field of a different size or type you can use the CAST function. The CAST() function takes a value of one type and produce a value of another type, similar to CONVERT(). Note that CONVERT() is what you'd use if you wanted to convert data between different character sets. For example, you want to convert from ASCII to hexadecimal.

Back to the CAST function. The types it can work with are:

• BINARY[(N)]
• CHAR[(N)]
• DATE
• DATETIME
• DECIMAL[(M[,D])]
• SIGNED [INTEGER]
• TIME
• UNSIGNED [INTEGER].

CAST has the syntax:

CAST value AS type

You could use it in the statement above to reduce the field to 8 chars with:

SELECT CAST("First" AS Char(8)) as rectype, field1 as a, field2 as b FROM firsttable

UNION ALL

SELECT CAST("Second" AS Char(8)) as rectype, field3 as a, field4 as b FROM secondtable

CAST supports SQL Server standard data types--so the field can be a small integer, string of fixed length, floating point or whatever other data format you require.

One point to remember is to make sure that all the data values fit within the new data type. Don’t throw large values into a small integer field or you will overflow the field and cause an error.

We've just scratched the surface of what CAST can do. You can find some detailed explanations online. But if you're going to use CAST very much, it's probably best to buy a good SQL Server book and develop a thorough understanding of CAST functions, Bit functions, full-text search functions, information functions, and the other functions you can work with in SQL.

If you're not familiar with SQL, here's a short intro to it. SQL stands for Structured Query Language.

If you have a report that uses an ODBC/SQL data source, look at "Show SQL query" in the database menu. This will display the SQL that Crystal Reports sends to the database server to retrieve records.

Look at the SQL each report creates and see the SQL the report writer generates. This is an easy way to learn this database language.

You can make some changes here, but we don’t recommend it because changes to selection criteria in SQL override the Select Expert and Selection Formula, and we believe those are more flexible and powerful tools.

You can also use SQL to retrieve data:

Sometimes we need to manipulate our data with SQL prior to processing a report.

For example, we combine a SELECT DISTINCT, UNION join and Sub SELECT to retrieve some data from our SQL server.

The SQL command can be added to the repository, so you can share it between several reports. If you wish to change your SQL, you need to detach the SQL from the repository, make your changes and then place it back in the repository.

The SQL Command feature is why Crystal Reports 9 does not include the Crystal SQL designer. You can do far more with Crystal Reports SQL commands. 

SQL command parameters

Use SQL command parameters. Using SQL commands as the source of data for your report has several benefits. This SQL command feature also includes the ability to include a parameter in the SQL command.

For example, we have created a parameter {?Region} and use that to select a single region 
from our table.

The SQL command now says Select * from Customer where Region = '{?Region}' 

The parameter editor in the SQL command dialog box lets you enter only a single default value. So if you wish to load more default values, return to the Field Explorer and add more default values to the parameter there.

This feature supports string, date and numeric parameters, but they must be a single value. Multi value parameters are not supported.

The real benefit of this feature is where you need to extract a few records from a large table. Doing the selection in an SQL command will have a major improvement in report performance. 

SQL Designer

If you do a "Full" install of Crystal Reports, you get the Crystal SQL Designer and Crystal Dictionary programs installed. These can be very useful additions to your reporting weaponry.

Click here for an article on the Crystal Dictionary.

The Crystal SQL Designer has an expert that can gently lead you through an inquiry against your database or you can enter your SQL directly. The retrieved records can then be used as the source for a report, or exported to another format.

So why use it? There are several advantages you might discover.

1) You can save data in your QRY file. This means you execute the SQL against the main database once, and then can process your Crystal Reports against the QRY file without compromising the performance of your main database.

2) You also have the ability to send the QRY file to a remote site, so a user there can process reports without requiring a connection to your original data

3) If the Application developer doesn’t want you to have permission to make changes to the Db, you can use this tool to process the SQL you would normally use in a view or stored procedure.

4) A full set of SQL functions is available here including Summary functions, and UNION joins.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.