Using CFML in reports

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

Advanced query mode

In some cases, you may want to use create a complex query, reuse an existing query, or encapsulate additional CFML processing as part of query creation for the report. In this case, you can 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 containing 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 these 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">
SELECT    LastName, FirstName, Dept_ID
FROM      Employee 
WHERE     (Dept_ID = #param.deptidin#)
</cfquery>

View comments in LiveDocs