using advanced query builder for a chart

[Guest]

I am trying to insert a query-based chart in my report template. However, the data is coming from a text file; thus I am creating the query object for the chart by parsing the text file. However, the "query.name" and "query.number" fields are not changing for each record from the original report query -- only the first record is used. Thus the first chart on the first page is correct, but the chart never changes for all the other pages (each page represents one record from the query). This is what I'm putting into the advanced query builder :

<cfset ThisQuery = QueryNew("ItemColumn,ValueColumn")>
<cfif FileExists("D:\charts\#query.name#_#query.number#.txt")>
<cfset ThisDataItem = 0>
<cfloop file="D:\charts\#query.name#_#query.number#.txt" index="ThisLine">
<cfif IsNumeric(ThisLine)>
<cfset ThisDataItem += 1>
<cfset QueryAddRow(ThisQuery)>
<cfset QuerySetCell(ThisQuery,"ItemColumn",ThisDataItem)>
<cfset QuerySetCell(ThisQuery,"ValueColumn",ThisLine)>
</cfif>
</cfloop>
</cfif>

So basically the filename is set according to first row (result) from the report query, but then the filename (and thus the chart) never changes even though all the other query-based fields on my page change properly (all the other query.BlahBlah fields update correctly). Is this a bug? I didn't think I was doing anything too crazy.

[Guest · Accepted solution]

Whew! I figured out a really awesome solution, which has a ton of uses.

First of all, as I should have realized, ANY variable in a coldfusion tag is going to be figured out when the report is called, NOT for every query result. In other words, query.xxx or report.xxx or whatever is NEVER going to work correctly inside of a tag, not even in a report function or the Advanced Query builder.

However, there is a straightforward way around this. Simply make a report function with arguments, and in your field's expression editor call the function and pass in the query or report variables:
#report.yourFunction(query.field1, query.field2)#
You can do all sorts of Coldfusion function magic and return whatever form of result you want.

For my particular example (dynamically creating a query object for a chart to be inserted), I had to add a not-so-obvious step: creating my query object with a shared scope (in this case, the Request scope), otherwise the variable is trapped in the function's scope. So this was my solution: I had a chart with the "Print When" option calling my function "getChartQuery" (passing in the query variables, of course). The function builds "Request.ThisChartQuery" and returns True if there's something in it (the "Print When" logic uses the boolean to decide whether to show the chart or not). Then my chart is still query-based, but the Advanced Query builder has NOTHING in it at all, except for the variable containing the query object ("Request.ThisChartQuery").

There was another solution I found where the report function makes the entire chart (using cfchart tags, etc) and saves it as a PNG file, and then returns the path to the image. Then you simply add an image field to the report, and you use the expression builder to call the function. Well, hope this helped someone!

EDIT: references are " http://www.adobe.com/go/ee54cc6" and " http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:40786"