Participant

Question

coldfusion reporting query question

Forum|Forum|1 month ago
May 13, 2026
2 replies
111 views

coldfusion report

Calculated Field SumForward

SumForward(Sum:iif((query.SumOfTrainingHours + calc.SumForward GTE 40),iif(calc.SumForward + query.Carryover LTE 40,query.Carryover,40-calc.SumForward), query.SumOfTrainingHours))

I’m not familiar with report query syntax

say querySumOfTraining hours = 20 and query.Carryover = -20

what is the order of processing within the query and what value does calc.Sumforward get as the query proceeds. And what is the final value for SumForward.

Thanks

JimBerkes

BKBK

Community Expert

Thanks for the additional information. Two things are questionable: why query-column indices are absent and why the need to use the complex iif structure.

As SumOfTrainingHours and Carryover are query-columns, I expected the code to use indices when referencing their data. For example,

query.SumOfTrainingHours[n] 
query.Carryover[n]

where the index n is an integer representing the row number. When you leave out the index, as you have done, ColdFusion will default to n=1. That is, to the first row.

Unless the code is in a loop. In which case there is no need to specify the index, as ColdFusion will iterate automatically to the next row

<cfloop query="myQuery">
<!--- 
No need to specify the index:

query.SumOfTrainingHours 
query.Carryover 

--->
</cfloop>


<cfoutput query="myQuery">
<!--- 
No need to specify the index: 

query.SumOfTrainingHours 
query.Carryover 

--->
</cfoutput >

Now, the second questionable point. The following line is complex, hence difficult to debug:

<cfset result = iif((query.SumOfTrainingHours + calc.SumForward  GTE 40), iif(calc.SumForward + query.Carryover LTE 40, query.Carryover,40-calc.SumForward), query.SumOfTrainingHours)>

You could replace it with something simpler, such as

<cfif (calc.SumForward + query.Carryover) LTE 40>
	<cfset tempResult=query.Carryover>
<cfelse>
	<cfset tempResult=40-calc.SumForward>
</cfif>

<cfif (query.SumOfTrainingHours + calc.SumForward) GTE 40>
	<cfset result=tempResult>
<cfelse>
	<cfset result=query.SumOfTrainingHours>
</cfif>

BKBK

Community Expert

The text is a bit confusing to me. Could you please share an excerpt of the code? Then we can see how the query object is defined.

JimBerkesAuthor

Participant

<select name="Report" class="FormField" id="Report">
                                   <option value="DLG-FMA-T01.cfr">Summary of Hours - All | ADMIN</option>
   IT’S CALLING THIS REPORT <option value="DLG-FMA-T06.cfr">Incentive Credited Report | ADMIN</option>
                                   <option value="DLG-FMA-T12.cfr">Incentive Not Credited Report | ADMIN</option>
                                   <option value="DLG-FMA-T10.cfr">Budget Estimate Report | ADMIN</option>
                                   <option value="DLG-FMA-T15.cfr">Incentive Payment Summary | ADMIN</option>
                                   <option value="ENVELOPE">Envelope | ADMIN</option>
   <option value="DLG-FMA-T09.cfr">Non-Participants with Hours Report | SYSTEM</option>
   </select>

Here’s the report query from the report writer

       <cfquery name="MyQuery" datasource="OfficialsTraining">
           SELECT QryVW_IncentiveCredited.*, QryVW_Incentives.Amt_RegistrationFee, QryVW_Incentives.Source, QryVW_Incentives.StartDate
           FROM dbo.QryVW_Incentives RIGHT OUTER JOIN dbo.QryVW_IncentiveCredited ON dbo.QryVW_IncentiveCredited.ContactID = dbo.QryVW_Incentives.ContactID AND dbo.QryVW_IncentiveCredited.CalendarYear = dbo.QryVW_Incentives.Source
           ORDER BY dbo.QryVW_IncentiveCredited.County, dbo.QryVW_IncentiveCredited.NameLast, dbo.QryVW_IncentiveCredited.NameFirst,dbo.QryVW_IncentiveCredited.ContactID,dbo.QryVW_IncentiveCredited.CalendarYear
       </cfquery>
       <cfreturn MyQuery>
   </cffunction>
</cfcomponent>

Query Data

Report line for Bowen

2026 Hours Hours Hours Needed

Earned From prior year Carried

Bowen, James Magistrate 20.00 13.00 33.00 0/0 0/0 0/0 1

hours from prior year

PreCalc

SumForward

iif((query.SumOfTrainingHours + calc.SumForward GTE 40), iif(calc.SumForward + query.Carryover LTE 40, query.Carryover,40-calc.SumForward), query.SumOfTrainingHours)

And my problem is I don’t know the rules of this statement what is the sequence of processing? If SumForward is the calculation what is its value in the if statement

Hopefully all this helps explain things.

I’ve been doing COLDFUSION off and on since cows came home. However, I never created a ColdFusion report and this is my first effort at fixing one. THANKS FOR YOUR HELP!! Jim

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded