Generating Reports

<cfdocument> is designed to create printable versions of Web pages. These Web pages may be reports (many will be), and may involve such features as:

• Banded reports

• Calculated totals and sums

• Repeating and non-repeating regions

• Embedded charts

Understanding the ColdFusion Report Builder

While these reports can indeed be created manually, there is a better way, using the ColdFusion Report Builder. The ColdFusion Report Builder is a stand-alone program used to define Cold Fusion Report templates. It can be run on its own, and also directly from within Dreamweaver by double-clicking on a report file.

NOTE

At this time, the ColdFusion Report Builder is a Windows-only utility. But reports created using the Report Builder can be processed by ColdFusion on any platform, and reports can be viewed on any platform.

The ColdFusion Report Builder creates and edits a special ColdFusion file with a .cfr extension. Unlike .cfm and .cfc files, .cfr files can't be edited with a text editor; the ColdFusion Report Builder must be used. .cfr files are report templates that may be used as is, or invoked from CFML code as needed (as we'll explain below).

To launch the Report Builder select ColdFusion Report Builder from the Windows Program > Macromedia > ColdFusion MX 7 program group.

NOTE

If the ColdFusion Report Builder isn't installed on your machine, run the installer named CFReportBuilder.exe. It can be found in /CFIDE/installers under the ColdFusion Web root.

Using the Setup Wizard

The first time the ColdFusion Report Builder is run, a setup wizard will be launched (as seen in Figure 16.14). This wizard configures the Report Builder so it's ready for use.

Figure 16.14. The ColdFusion Report Builder setup wizard configures the Report Builder for use.

Click the Next button to provide basic information (as seen in Figure 16.15). Once you have made your selections, click Next and you will be asked to specify how the Report Builder should connect to ColdFusion (as seen in Figure 16.16).

Figure 16.15. Specify the default measurement unit to be used by the Report Builder.

Figure 16.16. The Report Builder needs to know how to connect to ColdFusion.

The ColdFusion Report Builder uses RDS to connect to ColdFusion, and you will be prompted for the server name and login information (as seen in Figure 16.17).

Figure 16.17. RDS login information must be provided to gain access to full Report Builder functionality.

NOTE

You can use the ColdFusion Report Builder without RDS, but you won't be able to use the query builder, chart wizard, and some other functionality.

Once you have completed the wizard (and the Finish screen seen in Figure 16.18 is displayed), you'll be ready to start building reports.

Figure 16.18. When the Finish screen is displayed, the report Builder is ready for use.

NOTE

You can rerun the setup wizard at any time by selecting File, New in the Report Builder, and then selecting the Server Setup Wizard option.

Introducing the ColdFusion Report Builder

The ColdFusion Report Builder screen looks a lot like other report writing tools that you may have used. The screen, seen in Figure 16.19, contains several sections you should be aware of:

• The large open space in the middle of the Report Builder is where reports are defined and edited.

• The toolbox on the left contains buttons to insert images, fields, sub-reports, and more into reports, as well as buttons used to manage element alignment.

• On the top of the screen are toolbars for file opening, editing, fonts, etc.

• The Properties panel at upper right displays the properties for any report item, and allows for quick property editing.

• The Fields and Parameters panel on the lower right is used to access query columns, calculated fields, and input parameters.

Figure 16.19. The Report Builder interface is used to define and edit ColdFusion reports.

The ColdFusion Report Builder allows for multiple .cfr files to be open at once if needed.

Using the Report Wizard

The simplest way to create a report, and indeed the fastest way to learn the Report Builder, is to use the Report Creation Wizard. We'll start by creating a report of movie expenses. Here are the steps to follow:

1.	Select File >New (or press the New button) to display the ColdFusion Report Builder Gallery dialog (seen in Figure 16.20). Figure 16.20. The ColdFusion Report Builder Gallery is used to create new blank reports or to launch the report wizard.
2.	Select Report Creation Wizard, and click OK to launch the wizard. First we need the query columns to be used in the report. You may click Add to add the query columns manually (see Figure 16.21), Import to import a query from an existing report, or Query Builder to launch the Query Builder (seen in Figure 16.22). We'll use the Query Builder, so click that button. Figure 16.21. Query columns can be added manually. Figure 16.22. The SQL Query Builder simplifies the process of generating report SQL statements.
3.	The SQL Query Builder has two modes, Figure 16.22 shows the SQL Query Builder interactive mode; you can also click the Advanced check box to enter the SQL manually (as seen in Figure 16.23). We'll use the SQL Query Builder mode. On the left you'll see a list of available data sources; expand the ows data source to display the available tables. Figure 16.23. The SQL Query Builder simplifies Advanced mode can be used to enter SQL manually. NOTE You won't see available data sources if RDS isn't used.
4.	Drag the Films and Expenses tables into the SQL Query Builder (as seen in Figure 16.24). Notice how the SQL statement changes to reflect the table selections. Figure 16.24. The SQL Query Builder shows SQL changes as selections are made.
5.	To join the Films and Expenses tables, select the FilmID column in one of the tables and drag it to the FilmID column in there. A link will indicate that the tables are joined (as seen in Figure 16.25). Figure 16.25. Tables can be joined using a simple drag and drop. NOTE To change the join type, right-click on the box in the line that links the tables.
6.	Double-click on the MovieTitle column in Films to select that column.
7.	Double-click on the ExpenseDate, Description, and ExpenseAmount columns in Expenses (in that order) to select those three columns.
8.	The report needs to be sorted by MovieTitle and then by ExpenseDate, so click on the Sort Type column for MovieTitle and select Ascending, then do the same for ExpenseDate (as seen in Figure 16.26). Figure 16.26. SQL ORDER BY clauses can be created by selecting the desired sort type and sequence.
9.	Now that the SQL selection is complete, test it by Test Query button. The query results will be displayed in a pop-up window (as seen in Figure 16.27). Figure 16.27. Test your generated SQL queries using the integrated Test Query option.
10.	Close the query results window, and click Save to save the query (and columns) into the wizard (Figure 16.28). Figure 16.28. The SQL Query Builder inserts selected columns back into the wizard.
11.	Click Next, and the wizard will prompt you for any report grouping (used to create report bands). We want the report grouped by movie, so double-click MoveTitle to move it to Group by Fields column (Figure 16.29). Then click Next. Figure 16.29. Select field to group by to define report bands.
12.	You will then be prompted for a report layout, page orientation, and paper size. Select Left Aligned, and then click Next. TIP As you click on any report layout, a sample preview shows you what it will look like.
13.	You will then be prompted for the report style. Default should be used for all reports except sub-reports (reports embedded in other reports). You can also specify whether or not to generate totals for numeric fields, as well as the number of columns desired. Leave all the values as is, and click Next.
14.	To select a color theme to use, pick one of the colors, then click Next.
15.	The final wizard screen (Figure 16.30) prompts for a title, headers, and footers. Enter Movie Expenses as the title, and click Finish to generate your report. Figure 16.30. Specify the report title and optional headers and footers.
16.	When the wizard ends, your new report will be displayed in the Report Builder (as seen in Figure 16.31). Figure 16.31. The wizard generates a complete report and displays it in the Report Builder.
17.	The report has a title already, but it has a generic CompanyName at the top. ComanyName is static text, and it can be edited by simply double-clicking on the text to display the Edit Label Text dialog box (see Figure 16.32). Change the text to Orange Whip Studios and click OK. Figure 16.32. Static text can be edited by double-clicking on it.
18.	Save your new report (select File->Save, or click the Save button), name is expenses.cfr and save it in the /ows/16 folder.
19.	The final step is to preview the report, to make sure that it's working as intended. Click the Preview button (the one with a globe with a lightning bolt through it), or press F12. A preview (in FlashPaper format, by default) will be displayed (as seen in Figure 16.33). Figure 16.33. Preview your reports using the integrated preview feature.

Notice that the query fields and calculated fields in the Fields and Parameters panel have been populated with the information provided to the wizard (see Figure 16.34). You can edit these if needed.

Figure 16.34. Use Query Fields to edit database query fields, and Calculated Fields to define calculated fields for your report.

Here is a very important item: The report displays today's date when run, but how is that calculated? Double-click on the date field and you'll see a screen like the one seen in Figure 16.35. This is the Expression Builder, and it can be used to embed any CFML expressions into your report. If you want to convert text to uppercase, access special variables, or perform any special processing, you can do so using CFML expressions. This is one of ColdFusion Report Builder's most powerful features.

Figure 16.35. CFML expressions can be embedded into dynamic portions of reports.

TIP

You can zoom in on the report you're working on by clicking the x1, x2, and x4 buttons above each report.

Running Your Reports

To run your report, invoke the full URL to it from within your browser, as seen in Figure 16.36. The report will be displayed, exactly as it was when previewed in the ColdFusion Report Builder.

Figure 16.36. ColdFusion reports can be accessed directly via their URLs.

Invoking Reports from Within ColdFusion Code

Being able to run reports in browsers is useful, but other reporting tools can do that, too. What makes ColdFusion reports unique is their ability to be altered at runtime.

Look at Listing 16.13. It uses a tag named <cfreport> to embed a report into a .cfm file.

Listing 16.13. Report1.cfmBasic Report Invocation

 <!--- Name:        Report1.cfm Author:      Ben Forta Description: Invoke a ColdFusion report Created:     01/10/05 ---> <cfreport template="Expenses.cfr"           format="PDF" /> 

If you were to run this code, it would generate the same report as before, but now you're generating it in your own CFML instead of it's being generated automatically. Why is this of value? Look at Listing 16.14, a modified version of this code.

Listing 16.14. Report2.cfmPassing A Query To A Report

 <!--- Name:        Report2.cfm Author:      Ben Forta Description: Invoke a ColdFusion report Created:     01/10/05 ---> <cfquery name="Expenses" datasource="ows"> SELECT    Films.MovieTitle, Expenses.ExpenseDate,           Expenses.Description,           Expenses.ExpenseAmount FROM      Films, Expenses WHERE     Expenses.FilmID = Films.FilmID ORDER BY  Films.MovieTitle, expenses.expensedate </cfquery> <cfreport template="Expenses.cfr"           query="Expenses"           format="PDF" /> 

Listing 16.14 uses <cfquery> to create a database query, and then passes that query to the <cfreport> tag overriding the query within the report. This query is exactly the same as the one used within the report, but now that you can see how queries can be created and passed to reports, you can start to see this feature's power. After all, you already know how to dynamically create queries; using that knowledge, you can create a form that prompts for the information to be included in the report, allowing you to create truly dynamic reports.

Let's look at an example. Listing 16.15 is a simple form (seen in Figure 16.37), it allows for the selection of a movie or specifying all movies.

Listing 16.15. ReportForm1.cfmReport Front End

 <!--- Name:        ReportForm1.cfm Author:      Ben Forta Description: Report form front-end Created:     01/10/05 ---> <!--- Get movie list ---> <cfquery datasource="ows" name="movies"> SELECT FilmID, MovieTitle FROM Films ORDER BY MovieTitle </cfquery> <html> <head> <title>Orange Whip Studios Expenses Report</title> </head> <body> <cfform action="Report3.cfm"> Select movie: <cfselect name="FilmID"           query="movies"           display="MovieTitle"           value="FilmID"           queryPosition="below">  <option value="">--- ALL ---</option> </cfselect> <br> <cfinput name="sbmt"          type="submit"          value="Report"> </cfform> </body> </html> 

Figure 16.37. Form front ends can be used to allow users to select report contents.

The form is Listing 16.15 prompts for a movie, and passes FilmID to Report3.cfm shown in Listing 16.16.

Listing 16.16. Report3.cfmDynamic Report

 <!--- Name:        Report3.cfm Author:      Ben Forta Description: Invoke a ColdFusion report Created:     01/10/05 ---> <cfparam name="FilmID" default=""> <cfquery name="Expenses" datasource="ows"> SELECT    Films.MovieTitle, Expenses.ExpenseDate,           Expenses.Description,           Expenses.ExpenseAmount FROM      Films, Expenses WHERE     Expenses.FilmID = Films.FilmID <cfif FilmID NEQ "">  AND Films.FilmID = #FilmID# </cfif> ORDER BY  Films.MovieTitle, expenses.expensedate </cfquery> <cfreport template="Expenses.cfr"           query="Expenses"           format="PDF" /> 

Listing 16.16 is the same as Listing 16.14, but this time the <cfquery> is being created dynamically, so the query can select either all expenses or just expenses for a specific movie. The same <cfreport> tag is used, but now the report can display expenses for all movies, or just a single movie (as seen in Figure 16.38).

Figure 16.38. Being able to pass queries to reports allows for highly dynamic reporting.

This functionality is so important that the ColdFusion Report Builder can actually automatically create calling CFML code for you. To try this, return to the ColdFusion Query Builder (with the expenses.cfr report open), and click on the Code Snippet button (the one with tags on it). You will see a screen that contains calling CFML code, either a single .cfm (Figure 16.29) or a .cfc and .cfm. (Figure 16.40). You can select the code style using the radio buttons at the bottom of the page, and then click Save to save the generated ColdFusion code read for you to use (and modify, if needed).

Figure 16.40. The Report Builder can also generate a .cfc containing the query, and a .cfm invoking that query and calling the report.

Figure 16.39. The Report Builder can generate calling CFML code.

And A Whole Lot More Too

We've only scratched the surface, the ColdFusion Report Builder is a powerful tool that in truth is deserving of far more space than can be devoted here. But it is also an easy-to-use tool, and one that you are encouraged to experiment with. Some features worth paying attention to are:

• The various properties available when clicking on different report sections and elements.

• The Chart button which launches a Chart Wizard used to embed charts within reports (actually, the Wizard generates <cfchart> tags, the same tags used earlier in this chapter).

• The Subreport button, used to embed one report inside of another.

• The Print When property that can be used to conditionally include or exclude parts of reports.

• Input parameters, used to pass name=value pairs to reports at runtime.

• Text Styles which allow styles to be used for formatting.

NOTE

Be sure to visit the book Web page at http://www.forta.com/books/0321223675 for online lessons and tutorials on these features.