New with this release of Crystal Reports, is the functionality to use custom SQL commands as the data source for your report. Using this method, you can create a virtual table that contains all of the fields you want to use in your report. This functionality offers a flexible alternative to using Crystal Reports' own database and linking functionality, and can help you reuse the investment you have made in other report tools, or existing SQL statements. To summarize, SQL commands are the basis of a report.
SQL Expressions are used within a report, to create new values to display; for example, projected sales figures can be generated by applying a mathematical expression to the previous year's sales.
To see this feature in action, create a new Visual Basic .NET Windows Application within Visual Studio .NET called SQLCommand. (This project is available in the code download at the location C:\CrystalReports\Chapter06\SQLCommand.) Select Project | Add New Item and then choose Crystal Report from the available templates. Call the file sql_command.rpt, and click on Open. We will first step through the setup of a basic report again.
This will open the Crystal Report Gallery and allow you to select an expert to help you get started. In this example, we are going to select the Standard Report Expert, but SQL commands can be used with any of the experts listed.
The first step of the Standard Report Expert is selecting the data source for your report - double-click the node for ODBC (RDO) and select Xtreme Sample Database as our sample data source. This will open the dialog overleaf where you can double-click on Add Command to do just that.
Important
When adding an additional data source to an existing report using Database | Add/Remove Database, the option to add a SQL command is also available. When you select Add Command, another dialog will open and allow you to enter a SQL statement to serve as the data source for your report. Enter SELECT * from Customer in the window.
Click on OK, and Crystal Reports will treat the results of this query as a virtual table. You can now use the fields you have specified in your select statement in your report.
Click Next to move on to the Fields tab, and where you would normally see a table to select data from, you will see your command. It behaves just like a table in this dialog, so click on the + to expand the command, and you will see the results of your SQL command, in this case the Customer table.
Now select the fields you want to see on your report, which in this case are:
Customer Name
Contact First Name
Contact Last Name
City
Region
That's all you need to do to make a basic report based on a SQL command, so click on Finish to generate the report, and your Report Designer should show these fields in the Details section of sql_command.rpt. You know how to preview your report now, but here is a reminder of the basic steps:
Drag the CrystalReportViewer (under Windows Forms in the Toolbox) to Form1.vb
Drag ReportDocument (under Components in the Toolbox) to Form1.vb
Double-click on the CrystalReportViewer that sits on your form to generate the procedure that loads your report when you run the report
Insert the following code in the procedure:
CrystalReportViewer1.ReportSource = New sql_command()
When you run your report, it should look something like the report in the following screenshot, and you now know how to create a report based on a Virtual Table created from a SQL command.
After you have worked with SQL commands in Crystal Reports.NET, you will realize that the error messages returned aren't always the most informative. A lot of the problem with this has to do with the error messages that are returned from the database, as opposed to Crystal's own error handling and messaging.
In an effort to keep errors down to a minimum, take the following points into consideration:
The dialog to enter a SQL statement has no syntax checker or editing tools. To ensure your SQL statement will work, test it first in your own SQL query tool (SQL*PLUS, Query Analyzer, Microsoft Query).
The SQL statement that you enter must include a SELECT statement and return a result set, and cannot contain any data-definition or manipulation commands.
One way to improve report processing in Crystal Reports.NET is to use SQL expressions in your report instead of formulas written in Crystal Reports. These SQL expressions are passed back to the database and all of their processing occurs there. To understand how to use SQL expressions in a report, open Employee_Profile_Basic from the code download.
Open employee_profile.rpt in the form designer, locate the section of the Field Explorer marked SQL Expression Fields, and right-click directly on the section. From the right-click menu, select New... and enter a name for your SQL expression.
In this case we are going to create a SQL Expression called New Salary that will show the effect of a 6% increase on the employee's Salary. Once you have given your SQL expression a name and clicked OK, the SQL Expression Editor will open, which you can use to create a SQL expression using the available fields, functions, and operators shown.
If the SQL Expression Editor looks familiar, this is because it is really the Crystal Reports Formula Editor in a different guise. The standard functions and operators have been replaced with SQL functions and operators.
The SQL Expression Editor is not that flash on features, but does include a syntax checker. Enter your expression by double-clicking on the field you want to work with (Salary), then do the same for the multiply operator, and finally enter 1.06 to represent the 6% increase in salary. To check the syntax of your expression, click the X+2 button located in the toolbar, and you will hopefully see the following dialog:
When you have finished editing the SQL expression, click on the Save and Close icon in the upper left-hand corner to exit.
Sometimes when the syntax checker passes your expression, complex expressions may still fail when executed, because unfortunately the checker doesn't know everything; however, it will point out any basic errors or typos.
Important
Only use the functions and operators supported by your database. If in doubt, consult with your database administrator on the correct usage of syntax. Your SQL Expression should now appear in the list in the Field Explorer and you can drag and drop this field onto your report. The field should appear just like any other field shown on your report, showing that the SQL expression itself has been added.
You can see that the SQL expression can be identified as a % character precedes the name, New Salary.
When your report runs or is previewed, this calculation will occur on the database server and the results are returned to your report, just like any other database field. This report you have created is the same as Employee_Profile_SQLExp, which is available in the code download for this book.
