Generating Reports - macromedia COLDFUSION MX 7 Web Application Construction Kit [Electronic resources] نسخه متنی

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.

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).

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).

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.

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.

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:

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.

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.

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.

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>
&l275>
<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>

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).

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).

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.