Here is the code that I'm using:
<cfscript>
variables.FYCURRENT = application.objFiscalCalendar.getFiscalYear();
variables.FYPLUS1 = variables.FYCURRENT + 1;
variables.FYPLUS2 = variables.FYCURRENT + 2;
variables.FYPLUS3 = variables.FYCURRENT + 3;
variables.FYPLUS4 = variables.FYCURRENT + 4;
variables.FYPLUS5 = variables.FYCURRENT + 5;
variables.MINUS1 = variables.FYCURRENT - 1;
</cfscript>
<cffunction
name="QueryChangeColumnName"
access="public"
output="false"
returntype="query"
hint="Changes the column name of the given query.">
<!--- Define arguments. --->
<cfargument
name="Query"
type="query"
required="true"
/>
<cfargument
name="ColumnName"
type="string"
required="true"
/>
<cfargument
name="NewColumnName"
type="string"
required="true"
/>
<cfscript>
// Define the local scope.
var LOCAL = StructNew();
// Get the list of column names. We have to get this
// from the query itself as the "ColdFusion" query
// may have had an updated column list.
LOCAL.Columns = ARGUMENTS.Query.GetColumnNames();
// Convert to a list so we can find the column name.
// This version of the array does not have indexOf
// type functionality we can use.
LOCAL.ColumnList = ArrayToList(
LOCAL.Columns
);
// Get the index of the column name.
LOCAL.ColumnIndex = ListFindNoCase(
LOCAL.ColumnList,
ARGUMENTS.ColumnName
);
// Make sure we have found a column.
if (LOCAL.ColumnIndex){
// Update the column name. We have to create
// our own array based on the list since we
// cannot directly update the array passed
// back from the query object.
LOCAL.Columns = ListToArray(
LOCAL.ColumnList
);
LOCAL.Columns[ LOCAL.ColumnIndex ] = ARGUMENTS.NewColumnName;
// Set the column names.
ARGUMENTS.Query.SetColumnNames(
LOCAL.Columns
);
variables.COLUMN_LIST = LOCAL.ColumnList;
}
// Return the query reference.
return( ARGUMENTS.Query );
</cfscript>
</cffunction>
<!--- Call the function that gets column names --->
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"dept_name","Dept"),"project_id","ProjectID")/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"project_name","ProjectName"),"planning_project","PlanProject")/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"responsible_dept_cd","RespDept"),"print_plan_page","PrintPP")/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"fy","FY"),"actuals","Actuals")/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"fy1_amount",'FY#variables.FYCURRENT#'),"fy2_amount",'#variables.FYPLUS1#')/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"fy3_amount",'#variables.FYPLUS2#'),"fy4_amount",'#variables.FYPLUS3#')/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"fy5_amount",'#variables.FYPLUS4#'),"fy6_amount",'#variables.FYPLUS5#')/>
<!--- list of variables for columns --->
<cfset variables.reportitems = variables.COLUMN_LIST>
<!--- need to order by the reportitems, reason for using a query --->
<cfquery name="qgetdata" dbtype="query">
select * from theReportData order by #reportitems#
</cfquery>
<cfset variables.columns = variables.reportitems>
<!--- set vars for special chars --->
<cfset TabChar = Chr(9)>
<cfset NewLine = Chr(13) & Chr(10)>
<!--- output data using cfloop & cfoutput --->
<cfsavecontent variable="variables.body">
<cfoutput>
<table border="1">
<tr>
<cfloop list="#variables.columns#" index="col">
<td bgcolor="##C0C0C0"><b>#col##TabChar#</b></td>
</cfloop>
</tr>
<cfloop query="qGetData">
<tr>
<cfloop index="col" list="#variables.columns#">
<td>#qGetData[col][currentRow]#</td>
</cfloop>
</tr>
</cfloop>
</table>
</cfoutput>
</cfsavecontent>
<cffile action="write" file="#variables.fileLocation#" output="#variables.body#" >
<cfheader name="content-disposition" value="#variables.CFHeaderValue#">
<cfcontent type="text/xls" file="#variables.fileLocation#">
I get the following error:
Query Of Queries runtime error.
The select column reference [DEPT] is not a column in any of the tables of the FROM table list.
I have the following in a reports.cfc:
<cffunction name="GetAppr" access="public" output="false" returntype="query">
<cfargument name="username" required="true" type="string">
<cfquery datasource="#application.dsn#" name="GetAppr">
SELECT
dept_name,
project_id,
project_name,
planning_project,
responsible_dept_cd,
print_plan_page,
fy,
actuals,
fy1_amount,
fy2_amount,
fy3_amount,
fy4_amount,
fy5_amount,
fy6_amount
FROM
cip.cip_report_approp_plan_bu
WHERE
username = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.username#">
ORDER BY
project_id
</cfquery>
<cfreturn GetAppr />
</cffunction>
