The Cold Fusion Web Database Construction Kit
Using CFML, the Cold Fusion Markup Language, and its supporting functions, you are able to create dynamic reports that are driven by queries to live data. Creating reports manually, however, is no substitute for an industrial strength report writer.
To enable you to easily create powerful dynamic reports, Crystal Reports Professional version 5 is bundled with Cold Fusion. With Crystal Reports, you can create sophisticated reports, charts, and graphs that can be embedded into your Cold Fusion templates.
In this chapter, you will learn how to create basic reports with Crystal Reports Professional and how to embed reports into Cold Fusion.
Crystal Reports Professional has long been recognized as one of the most powerful and easy-to-use report writers available. This is why so many software vendors, including Allaire, have chosen it as the report writer to be bundled with their applications.
Crystal Reports Professional is popular because:
It is the last feature in this list that makes Crystal Reports Professional so appealing to Cold Fusion developers.
For help with installing Crystal Reports Professional, see Chapter 4, "Installing and Administering Cold Fusion."
NOTE: Crystal Report does not have to be installed on the computer running your web server and Cold Fusion. All the files that Cold Fusion uses to interact with Crystal Reports' RPT files are installed during the Cold Fusion installation.
So, if you're ready, let's create a report.
A Note from the AuthorCrystal Reports Professional version 5 is an extremely powerful and capable report writer. Complete coverage of all of its features is beyond the scope of this book.
This chapter will not teach you everything there is to know about Crystal Reports Professional. Instead, it'll teach you how to create a basic report with the report writer and how to execute that report from within your Cold Fusion templates.
Crystal Reports Professional comes with complete online help and step-by-step tutorials. If you are planning on using the report writer, I strongly urge you to use these tools.
Crystal Reports Professional only reads your data files, it does not write to them. Therefore, there is nothing that you can do within the report writer that will corrupt or damage any data, so don't be afraid to experiment.
To learn how to use the report writer, we'll start by creating a simple report--an employee directory.
TIP: You can use Crystal Reports on any computer, not just on the web server machine, but make sure that the ODBC data source you'll use is set up exactly the same way it is on the web server machine. This way, you can be sure that reports created on one computer will work on another.
For starters, let's load the Crystal Reports program. Click its icon to display the welcome screen as shown in Figure 14.1. You'll be presented with two options: one to create a new report and the other to open an existing report. Select the New Report option to display the Report Gallery, shown in Figure 14.2. The Report Gallery displays eight popular report types from which you may select but also allows you to create your own type.
Figure 14.1 The Crystal Reports Professional welcome screen prompts you to either create a new report or open an existing one.
Figure 14.2 The Report Gallery allows you to pick a report type or create a custom type of your own.
We're going to create a standard report, so select the Standard button to display the Create Report Expert.
The experts are interactive tools that walk you through much of the report design process. Crystal Reports' experts are made up of multiple screens in a tab dialog, as shown in Figure 14.3. You may jump to any screen by clicking the tab at the top. Alternatively, you may use the << Back and Next >> buttons to walk through the screens while building your report.
Figure 14.3 The Create Report Expert walks you through the process of creating a report.
To build our report, follow these steps:
Figure 14.4 The Expert graphically displays any links established between different tables.
Figure 14.5 Crystal Reports Professional previews reports using live data that enable you to see exactly what a finished report will look like.
Figure 14.6 Use the Design screen to make any changes to a report.
You've just created a complete report using the Crystal Reports report writer. You may print this report at any time by selecting Print from the File menu.
NOTE: Many of the report writer's formatting features cannot be supported by HTML, so some advanced formatting will be lost when the report output displays in a Web page. For example, Crystal Reports allows you to specify single, double, or mixed borders around text, all of which are rendered into HTML as standard TABLE borders. Before rolling out your reports for public use, make sure you test them thoroughly to ensure that the output is acceptable.
Reports created with Crystal Reports Professional can easily be printed. Our interest is in its HTML publishing capabilities because Crystal Reports is most often used for printing.
Reports are embedded into Cold Fusion Templates using the <CFREPORT> tag. When Cold Fusion processes a template and encounters a <CFREPORT> tag, it executes a Crystal Reports engine which processes the report and creates an HTML version of the report output.
NOTE: Cold Fusion returns an error message if you try to process a report that is already open in Crystal Reports. You must close any reports in the report writer before using them with Cold Fusion.
We'll now create a template to process the report we just created. Listing 14.1 contains the code to process report EMPLIST.RPT. As you can see, the code listing is standard HTML, except for the <CFREPORT> tag. <CFREPORT> requires a single attribute, REPORT, which contains the name (and fully qualified path) of the report to execute. Create a template containing the code in Listing 14.1 and save it as EMPLIST.CFM in the C:\A2Z\SCRIPTS directory.
<HTML> <HEAD> <TITLE>Employee List</TITLE> <HEAD> <BODY> <CFREPORT REPORT="emplist.rpt"> </CFREPORT> </BODY> </HTML>
Now let's test the report. Load your web browser and go to URL http://yourservername/a2z/emplist.cfm. After a few seconds of processing, your browser display should look like the one shown in Figure 14.7.
Figure 14.7 Cold Fusion executes a Crystal Reports Professional engine to process embedded reports.
Crystal Reports Professional enables you to interactively create powerful reports which you can easily embed into Cold Fusion.
Cold Fusion also allows you to pass parameters to the Crystal Reports processing engine enabling you to customize the reports on-the-fly.
For example, the phone list report we created earlier displays all employees in all departments. How could you create a report to display the employees in a single department?
You could create multiple reports and specify a filter condition in each one. One report would filter only the Sales department employees, another would filter just the Shipping & Receiving department employees, and so on.
It can be done, but it is definitely not a scaleable or manageable solution. A better alternative would be to use the same report and modify it on-the-fly by passing additional information to it.
The code in Listing 14.2 shows how this is done. The <CFREPORT> tag is the same, but we've added a line between the <CFREPORT> and </CFREPORT> tags. The code {Departments.Department} = "Sales" is passed to the Crystal Reports engine as a selection filter. In this case, only employees who have a Departments.Department value of "Sales" will be included in the report.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
<HEAD>
<BODY>
<CFREPORT REPORT="emplist.rpt">
{Departments.Department} = "Sales"
</CFREPORT>
</BODY>
</HTML>
NOTE: Field names passed to Crystal Reports must be fully qualified with the table name and must be enclosed within curly braces.
The selection criteria may even be made up of Cold Fusion tags, functions, and fields. For example, instead of hard coding the filter to a department called "Sales," we could have compared it to a passed field name. In fact, the entire filter condition could be contained with a <CFIF> conditional statement so that the same template could display as many different employee lists as needed.
The Cold Fusion interface to the Crystal Reports Engine enables you to specify options and attributes other than the ones discussed in this chapter. See the <CFREPORT> section of Appendix A, "Cold Fusion Reference," for more details.
In this chapter, you learned the basics of using Crystal Reports Professional version 5 and how to embed reports into Cold Fusion templates. You also learned how to pass filter conditions to the Crystal Reports engine enabling you to easily reuse reports to display related but different data sets.
Crystal Reports is a powerful and capable report writing tool, and one well worth getting to know.
© Copyright, Macmillan Computer Publishing. All rights reserved.