Adobe ColdFusion 8

Using CFML in reports

CFML is the scripting language for the Report Builder. By leveraging CFML, you can create reports that select and format data to meet your needs. You use CFML in the following areas of the Report Builder:

  • Advanced query mode
  • Report functions
  • Expressions

Advanced query mode

In some cases, you might create a complex query, reuse an existing query, or encapsulate additional CFML processing as part of query creation for the report. To use a query in these ways, you use advanced query mode to create CFML that returns a query. When you click the Advanced button at the top of the Query Builder, the Report Builder displays a text entry area in which you can enter CFML that generates a query. ColdFusion executes this tag at report execution time and passes the query result set to the report.

Note: When you use advanced query mode, the Query Builder does not create query fields automatically. You must create the associated query fields manually.

The CFML used in advanced query mode must include a query object whose name matches that in the Variable that contains the query object field. You can use any CFML tag that returns a query object or the QueryNew function. The CFML can use multiple query objects, but can only return one.

Note: If you set an empty variable (for example, <cfset name=" ">), the Report Builder throws a Report data binding error.

This example CFML uses the cfhttp tag to retrieve a query:

<cfhttp url="http://quote.yahoo.com/download/quotes.csv?Symbols=csco,jnpr&format=sc1l1&ext=.csv"
method="GET"
name="qStockItems"
columns="Symbol,Change,LastTradedPrice"
textqualifier=""""
delimiter=","
firstrowasheaders="no">

Another possible use of advanced query mode is to test for passed parameters in the URL or FORM scopes and use those parameters to retrieve data, as the following example shows:

<!--- First look for URL parm. URL overrides cfreportparam. --->
<cfif isDefined("url.deptidin")>
    <cfset param.deptidin = url.deptidin>
</cfif>

<!-- Then look for FORM parm. Overrides URL parm. --->
<cfif isDefined("form.deptidin")>
    <cfset param.deptidin = form.deptidin>
</cfif>

<cfquery name="CFReportDataQuery" datasource="cfdocexamples">
SELECTLastName, FirstName, Dept_ID
FROMEmployee 
WHERE (Dept_ID = #param.deptidin#)
</cfquery>

Using report functions

Report functions are user-defined CFML functions that you code using the Report Function Editor and invoke in report fields. You can use them to format data (such as concatenating and formatting all the field that make up an address), to retrieve data, and for many other purposes.

Three built-in functions are unique to Report Builder: InitializeReport, BeforeExport, and FinalizeReport. For more information, see the Report Builder online Help.

Report Builder built-in functions

  1. Select Report > Report Functions from the menu bar.

    The Report Function Editor displays.

  2. Click the Add Default Functions icon (the first on the left).

    The built-in functions are added to the left pane.

  3. Select a function from the left pane.

    Commented code associated with the function appears in the right pane.

  4. Modify the code and click OK.

Create a report function

  1. Select Report > Report Functions from the menu bar.

    The Report Function Editor displays.

  2. Click the plus sign to add a new report function.

    The Add Report Function dialog box displays.

  3. Specify a name and click OK.
  4. The Report Function Editor places a cfreturn tag in the text entry area.
  5. Code the function, and click OK. This is a ColdFusion user-defined function so all UDF rules and features are available for use. The following example shows a report function that concatenates address fields:
    <cfargument name="Name" required="yes"/>
<cfargument name="Address1" required="yes"/>
<cfargument name="Address2" required="yes"/>
<cfargument name="City" required="yes"/>
<cfargument name="State" required="yes"/>
<cfargument name="Zip" required="yes"/>

<cfset variables.CRLF = Chr(13) & Chr(10)>
<cfset variables.ResultVar="">

<cfif Trim(arguments.Name) NEQ "">
    <cfset variables.ResultVar='#arguments.Name#'>
</cfif>
<cfif Trim(arguments.Address1) NEQ "">
    <cfif variables.ResultVar NEQ "">
        <cfset variables.ResultVar='#variables.ResultVar & variables.CRLF#'>
    </cfif>
    <cfset variables.ResultVar='#variables.ResultVar & arguments.Address1#'>
</cfif>
<cfif Trim(arguments.Address2) NEQ "">
    <cfif variables.ResultVar NEQ "">
        <cfset variables.ResultVar='#variables.ResultVar & variables.CRLF#'>
    </cfif>
    <cfset variables.ResultVar='#variables.ResultVar & arguments.Address2#'>
</cfif>
<cfif variables.ResultVar NEQ "">
    <cfset variables.ResultVar='#variables.ResultVar & variables.CRLF#'>
</cfif>

<cfset variables.ResultVar='#variables.ResultVar & arguments.City & ", " & arguments.State & " " & arguments.Zip#'>

<cfreturn variables.ResultVar>

Use a report function

  1. Place a dynamic field on the appropriate report band.

    The Add Field dialog box displays.

  2. Specify Manually Entered Expression, and click OK.

    The Expression Builder displays.

  3. Specify "report.functionname", and click OK.

Using expressions

Many elements of the Report Builder (including query fields, calculated fields, input parameters, images, and report object attributes) are single operand ColdFusion expressions. Because these elements are expressions, you can manipulate them with CFML functions.

The Expression Builder is a graphical interface that lets you quickly apply CFML functions to Report Builder elements. Uses for the Expression Builder include the following:

  • Many of the report object attributes (such as PrintWhen) accept expressions, which you can associate with query parameters, input parameters, or ColdFusion page variables. You can tie report attributes and columns to display based on run-time data or user preference.
  • Concatenating fields
  • Formatting fields
  • Calculated fields
  • Accessing and displaying ColdFusion page variables and scopes

For information on using the Expression Builder, see Report Builder online Help.

For more information on expressions, see Using Expressions and Number Signs.