ColdFusion Report Builder: Charts with a dynamic query
If you place a chart in your report, you will more than likely need to define a series that uses a query. If the query is known and does not change, you can simply use the QueryBuilder within the Chart Wizard to define the query. If you want to change the query at runtime, you will need to follow the steps in this TechNote.
When you call the the cfreport tag in a ColdFusion page, you have a query attribute. This attribute refers to the 'report' query and does not refer to the chart series queries in any way.
In order to change a chart series query, you need to edit the Series definition inside the Chart Wizard. Using the QueryBuilder, you enter the Advanced area and specify a variable that contains a valid query that was populated prior to calling thecfreport tag. The key point is that the query variable needs to be in a shared scope. The request scope is typically the best choice.
In the QueryBuilder Advanced window, you do not need any code. All that is necessary is the query variable. The following ColdFusion code demonstrates how to populate a query. Place it into the request scope and then call the cfreport tag. Notice you do not send it in as a tag parameter, thequery parameter for the cfreport tag is only for the report query. Placing the query in the shared scope will allow the report to see the query.
<cfset request.Q = getQ()><cfreport template="test.cfr" overwrite="Yes" /><cffunction name="getQ" returntype="query"><cfset var qq = ''><cfquery name="qq" datasource="psdb" > select * from reporttest</cfquery><cfreturn qq></cffunction>
Below is what the the QueryBuilder Advanced window should look like in order to read in the query:
This content requires Flash
To view this content, JavaScript must be enabled, and you need the latest version of the Adobe Flash Player.
Download the free Flash Player now!
