Copy link to clipboard
Copied
I have two query fields that are created by a case statement. I created a Calculated Field in my report: Data Type Float, Calculation: Nothing, Perform Calculation On: query.Tuition + query.RoomBoard, Initial Value query.Tuition + query.RoomBoard, Reset Field When: Group Changes, Reset Group: ID
I get the following error when I run the report: query.Tuition + query.RoomBoard is not a valid ColdFusion expression.
What am I doing wrong?
It looks like you want that sum for each record, right?
If that's the case then you cannot use a calculated field. Insert a field into your detail band, choose "Manually entered expression" and then use "query.Tuition + query.RoomBoard" as the expression.
Cheers
Eddie
Copy link to clipboard
Copied
Please show the text of the query for the report.
Cheers
Eddie
Copy link to clipboard
Copied
Here it is:
/*Returning Students*/
SELECT DISTINCT tmpfinaid.soc_sec, tmpfinaid.ref_id AS AwardDesc, tmpfinaid.tot_off_gross AS AwdAmt, name.last_name, name.first_name, name.name_le1, MMTemp.st_addr, MMTemp.add_addr, MMTemp.city, MMTemp.state, MMTemp.zip, nmact.act_memo, 'Rtn' AS Instr,
finaideurekaawddesc.finaidawdcat_cod, nmbudget.nmbudget_tuition, nmbudget.nmbudget_RM_BRD, nmbudget.nmbudget_Stud_Ser,
CASE
WHEN tmpfinaid.sem1_off_gross = 0 THEN ' Spring of ' + SUBSTRING(tmpfinaid.sch_yr,1,2)+ SUBSTRING(tmpfinaid.sch_yr,5,2) ELSE ' Fall of ' + SUBSTRING(tmpfinaid.sch_yr,1,4) END AS EntryTerm,
CASE
WHEN finaideurekaawddesc.finaidawdcat_cod like 'E%' OR finaideurekaawddesc.finaidawdcat_cod like 'OT' THEN ' Gift aid & scholarships'
WHEN finaideurekaawddesc.finaidawdcat_cod = 'FG' THEN ' Need based aid'
WHEN finaideurekaawddesc.finaidawdcat_cod = 'FL' THEN 'Federal Direct Loans'
ELSE 'Work Study' END AS CatType,
CASE
WHEN name.camp_cod = 'AdultLearn' THEN 17911
WHEN name.camp_cod <> 'AdultLearn' AND nmbudget.nmbudget_tuition > 0 THEN nmbudget.nmbudget_tuition + nmbudget.nmbudget_Stud_Ser
WHEN name.camp_cod <> 'AdultLearn' AND tmpfinaid.sem1_off_gross > 0 and tmpfinaid.sem2_off_gross > 0 THEN 20510 ELSE 10255
END AS Tuition,
CASE
WHEN nmbudget.nmbudget_RM_BRD > 0 THEN nmbudget.nmbudget_RM_BRD
WHEN name.name_le1 = 1 THEN 0
WHEN name.name_le1 = 0 AND tmpfinaid.sem1_off_gross > 0 AND tmpfinaid.sem2_off_gross > 0 THEN 8835
WHEN name.name_le1 = 0 AND tmpfinaid.sem1_off_gross = 0 AND tmpfinaid.sem2_off_gross > 0 THEN 4417
WHEN name.name_le1 = 0 AND tmpfinaid.sem1_off_gross > 0 AND tmpfinaid.sem2_off_gross = 0 THEN 4418
END AS RoomBoard
FROM tmpfinaid, name, nmfinaid, MMTemp, nmact, finaideurekaawddesc, nmbudget
WHERE MMTemp.Operator = '#session.token#'
AND tmpfinaid.token='#session.token#'
AND MMTemp.soc_sec = tmpfinaid.soc_sec
AND tmpfinaid.sch_yr = nmfinaid.sch_yr
AND tmpfinaid.soc_sec = name.soc_sec
AND tmpfinaid.soc_sec = nmfinaid.soc_sec
AND tmpfinaid.soc_sec = nmact.soc_sec
AND finaideurekaawddesc.finaidawddesc_cod NOT IN ('FDPL', 'ALTE')
AND tmpfinaid.awd_id = finaideurekaawddesc.finaidawddesc_cod
AND nmact.act_date = (SELECT MAX(nmact.act_date)from nmact where nmact.soc_sec = MMTemp.soc_sec and nmact.activity_cod = 'AM' group by nmact.soc_sec, nmact.activity_cod)
AND NOT EXISTS (SELECT tmpfinaid.soc_sec FROM finaideurekaawd WHERE finaideurekaawd.soc_sec = MMTemp.soc_sec AND finaideurekaawd.sch_years = tmpfinaid.sch_yr)
AND name.soc_sec *= nmbudget.soc_sec
UNION
/*New Students*/
SELECT DISTINCT name.soc_sec, finaideurekaawddesc.finaideurekaawddesc AS AwardDesc, finaideurekaawd.finaideurekaawdorig AS AwdAmt,name.last_name, name.first_name, name.name_le1, MMTemp.st_addr, MMTemp.add_addr, MMTemp.city, MMTemp.state, MMTemp.zip, nmact.act_memo, 'New' AS Instr, finaideurekaawddesc.finaidawdcat_cod, nmbudget.nmbudget_tuition, nmbudget.nmbudget_RM_BRD, nmbudget.nmbudget_Stud_Ser,
CASE
WHEN SUBSTRING(MMTemp.term_int, 5,2) = '01' THEN 'Spring of ' + SUBSTRING(finaideurekaawd.sch_years,1,2)+ SUBSTRING(finaideurekaawd.sch_years,5,2) ELSE ' Fall of ' + SUBSTRING(finaideurekaawd.sch_years,1,4) END AS EntryTerm,
CASE
WHEN finaideurekaawdcat.finaidawdcat_cod like 'E%' OR finaideurekaawdcat.finaidawdcat_cod like 'OT' THEN ' Gift aid & scholarships'
WHEN finaideurekaawdcat.finaidawdcat_cod = 'FG' THEN ' Need based aid'
WHEN finaideurekaawdcat.finaidawdcat_cod = 'FL' THEN 'Federal Direct Loans'
ELSE 'Work Study'
END AS CatType,
CASE WHEN name.camp_cod = 'AdultLearn' THEN 17911
WHEN name.camp_cod <> 'AdultLearn' AND nmbudget.nmbudget_tuition > 0 THEN nmbudget.nmbudget_tuition + nmbudget.nmbudget_Stud_Ser
WHEN name.camp_cod <> 'AdultLearn' AND SUBSTRING(MMTemp.term_int, 5,2) = '01' THEN 10255 ELSE 20510
END AS Tuition,
CASE
WHEN nmbudget.nmbudget_RM_BRD > 0 THEN nmbudget.nmbudget_RM_BRD
WHEN name.name_le1 = 1 THEN 0
WHEN name.name_le1 = 0 AND SUBSTRING(MMTemp.term_int, 5,2) = '01' THEN 4417
/* Spring Only 4418 */
WHEN name.name_le1 = 0 AND SUBSTRING(MMTemp.term_int, 5,2) = '08' THEN 8835
END AS RoomBoard
FROM MMTemp, name, nmact, finaideurekaawd, finaideureka, finaideurekaawddesc, finaideurekaawdcat, tmpfinaid, nmbudget
WHERE MMTemp.Operator = '#session.token#'
AND MMTemp.activity_cod='AM'
AND MMTemp.soc_sec=name.soc_sec
AND MMTemp.activity_cod=nmact.activity_cod
AND name.soc_sec=nmact.soc_sec
AND name.soc_sec=finaideurekaawd.soc_sec
AND name.soc_sec=finaideureka.soc_sec
/* AND NOT EXISTS (SELECT tmpfinaid.soc_sec FROM tmpfinaid WHERE tmpfinaid.soc_sec = MMTemp.soc_sec AND finaideurekaawd.sch_years <= tmpfinaid.sch_yr) */
AND finaideurekaawd.finaidawddesc_cod=finaideurekaawddesc.finaidawddesc_cod
AND finaideurekaawddesc.finaidawddesc_cod NOT IN ('FDPL', 'ALTE')
AND finaideurekaawd.finaidawdcat_cod=finaideurekaawdcat.finaidawdcat_cod
AND nmact.act_date = (SELECT MAX(nmact.act_date)from nmact where nmact.soc_sec = MMTemp.soc_sec and nmact.activity_cod = 'AM' group by nmact.soc_sec, nmact.activity_cod)
AND name.soc_sec *= nmbudget.soc_sec
UNION
/*Student View*/
SELECT DISTINCT tmpfinaid.soc_sec, tmpfinaid.ref_id AS AwardDesc, tmpfinaid.tot_off_gross AS AwdAmt, last_name, name.first_name, name.name_le1, address.st_addr, address.add_addr, address.city, address.state, address.zip, nmact.act_memo, 'Rtn' AS Instr,
finaideurekaawddesc.finaidawdcat_cod, nmbudget.nmbudget_tuition, nmbudget.nmbudget_RM_BRD, nmbudget.nmbudget_Stud_Ser,
CASE
WHEN tmpfinaid.sem1_off_gross = 0 THEN ' Spring of ' + SUBSTRING(tmpfinaid.sch_yr,1,2)+ SUBSTRING(tmpfinaid.sch_yr,5,2) ELSE ' Fall of ' + SUBSTRING(tmpfinaid.sch_yr,1,4) END AS EntryTerm,
CASE
WHEN finaideurekaawddesc.finaidawdcat_cod like 'E%' OR finaideurekaawddesc.finaidawdcat_cod like 'OT' THEN ' Gift aid & scholarships'
WHEN finaideurekaawddesc.finaidawdcat_cod = 'FG' THEN ' Need based aid'
WHEN finaideurekaawddesc.finaidawdcat_cod = 'FL' THEN 'Federal Direct Loans'
ELSE 'Work Study'
END AS CatType,
CASE
WHEN name.camp_cod = 'AdultLearn' THEN 17911
WHEN name.camp_cod <> 'AdultLearn' AND nmbudget.nmbudget_tuition > 0 THEN nmbudget.nmbudget_tuition + nmbudget.nmbudget_Stud_Ser
WHEN name.camp_cod <> 'AdultLearn' AND tmpfinaid.sem1_off_gross > 0 and tmpfinaid.sem2_off_gross > 0 THEN 20510 ELSE 10255
END AS Tuition,
CASE
WHEN nmbudget.nmbudget_RM_BRD > 0 THEN nmbudget.nmbudget_RM_BRD
WHEN name.name_le1 = 1 THEN 0
WHEN name.name_le1 = 0 and tmpfinaid.sem1_off_gross > 0 and tmpfinaid.sem2_off_gross > 0 THEN 8835
WHEN name.name_le1 = 0 and tmpfinaid.sem1_off_gross = 0 and tmpfinaid.sem2_off_gross > 0 THEN 4417
WHEN name.name_le1 = 0 and tmpfinaid.sem1_off_gross > 0 and tmpfinaid.sem2_off_gross = 0 THEN 4418
END AS RoomBoard
FROM tmpfinaid, name, nmfinaid, address, nmact, finaideurekaawddesc, nmbudget
WHERE tmpfinaid.token='#session.token#'
AND '#session.module#' <> 'admn' /* Blocks this query from an Adminstrator (Staff)) */
/* AND tmpfinaid.token LIKE '%[0-9]'*/ /* Blocks this query from an Adminstrator (Staff)) */
AND tmpfinaid.soc_sec = address.soc_sec
AND address.preferred = 1
AND tmpfinaid.sch_yr = nmfinaid.sch_yr
AND tmpfinaid.soc_sec = name.soc_sec
AND tmpfinaid.soc_sec = nmfinaid.soc_sec
AND tmpfinaid.soc_sec = nmact.soc_sec
AND tmpfinaid.awd_id = finaideurekaawddesc.finaidawddesc_cod
AND finaideurekaawddesc.finaidawddesc_cod NOT IN ('FDPL', 'ALTE')
AND nmact.act_date = (SELECT MAX(nmact.act_date)from nmact where nmact.soc_sec = tmpfinaid.soc_sec and nmact.activity_cod = 'AM' group by nmact.soc_sec, nmact.activity_cod)
AND name.soc_sec *= nmbudget.soc_sec
ORDER BY name.last_name, name.first_name, CatType, tmpfinaid.ref_id
Copy link to clipboard
Copied
It looks like you want that sum for each record, right?
If that's the case then you cannot use a calculated field. Insert a field into your detail band, choose "Manually entered expression" and then use "query.Tuition + query.RoomBoard" as the expression.
Cheers
Eddie
Copy link to clipboard
Copied
I had to put the expression on the Placeholder Header Band. Now I am getting a blank report.
Copy link to clipboard
Copied
You're trying to place the expression in a header band? Are you trying to see the sum total of all records in the report in a single field in the report header?
It is unclear what you are expecting to see in the report.
Cheers
Eddie
Copy link to clipboard
Copied
Yes.
Bonni
---
Bonni Harris
Database Analyst
Eureka College
300 E. College Avenue
Eureka, IL 61530-1500
309-467-6467
On Thu, Jan 21, 2016 at 12:09 PM, EddieLotter <forums_noreply@adobe.com>
Copy link to clipboard
Copied
Okay, then you do need to use a calculated field, but there is additional work to do because you are putting the field in the report header.
Edit your calculated field and change "Calculation" from "Nothing" to "Sum" and change "Initial Value" from "query.Tuition + query.RoomBoard" to "0" and change "Reset Field When" from "Group" to "Report".
Now click on the field in the report header and change the "Evaluation Time" property from "Now" to "Report".
Cheers
Eddie
Copy link to clipboard
Copied
Cleared my cache and started over. When I run the report the Total cost is not correct. If tuition is 20,510 and RoomBoard is zero I get $102,550 for total cost.
Copy link to clipboard
Copied
Without details it's impossible to know where you went wrong.
Post a screenshot of your Report Builder window with the total cost field selected so we can see the properties of the field.
Also post a screen shot of the details of your calculated field.
Cheers
Eddie
Copy link to clipboard
Copied
Attached are the two screen shots you requested. I also included a shot of
what the award letter is displaying (offer.jpg). I have no idea where the
highlighted field is coming from.
Bonni
---
Bonni Harris
Database Analyst
Eureka College
300 E. College Avenue
Eureka, IL 61530-1500
309-467-6467
On Mon, Jan 25, 2016 at 9:42 AM, EddieLotter <forums_noreply@adobe.com>
Copy link to clipboard
Copied
Bonnni, attachments from e-mail do not appear to transfer to the forum. You will need to post through the Web interface.
Cheers
Eddie
Copy link to clipboard
Copied
Below are the two screen shots you requested. I also included a shot of what the award letter is displaying (offer.jpg). I have no idea where the highlighted field is coming from.
Copy link to clipboard
Copied
Cozmo2 wrote:
Cleared my cache and started over. When I run the report the Total cost is not correct. If tuition is 20,510 and RoomBoard is zero I get $102,550 for total cost.
That is because the calculation is on all records in the report. When I asked:
Eddie Lotter wrote:
Are you trying to see the sum total of all records in the report in a single field in the report header?
You responded:
Cozmo2 wrote:
Yes.
From your screenshots you appear to be placing the calculated field in a group header, not the report header.
If that is the case then you need to reset the calculated field on the proper group.
Please show a screenshot of your report group management dialog box.
Cheers
Eddie
Copy link to clipboard
Copied
Here is my group management:
Copy link to clipboard
Copied
Okay, change the calculated field back to Reset Field When: Group Changes, Reset Group: ID like you had before.
Change the report field's "Evaluation time" property to "Now".
Cheers
Eddie
Copy link to clipboard
Copied
The Total Cost still is not printing and a field is printing that I do not
know where it is coming from (the 102550.00)
Bonni
---
Bonni Harris
Database Analyst
Eureka College
300 E. College Avenue
Eureka, IL 61530-1500
309-467-6467
On Wed, Jan 27, 2016 at 9:09 AM, EddieLotter <forums_noreply@adobe.com>
Copy link to clipboard
Copied
Cozmo2 wrote:
a field is printing that I do not
know where it is coming from (the 102550.00)
Your meaning is not clear here. How can you not know where it is coming from?
Cheers
Eddie
Copy link to clipboard
Copied
We did some system maintenance last night (clearing of cache) and the
problem is now fixed. Thanks you for hanging in there with me!
Bonni
---
Bonni Harris
Database Analyst
Eureka College
300 E. College Avenue
Eureka, IL 61530-1500
309-467-6467
On Wed, Jan 27, 2016 at 1:34 PM, EddieLotter <forums_noreply@adobe.com>
Copy link to clipboard
Copied
You're welcome. I hope it is a little clearer how calculated fields work and when they should be used.
Cheers
Eddie