Adding Two Fields

Report · Jan 21, 2016

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?

Report · Jan 21, 2016

Please show the text of the query for the report.

Cheers

Eddie

Report · Jan 21, 2016

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

Report · Jan 21, 2016

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

Report · Jan 21, 2016

I had to put the expression on the Placeholder Header Band. Now I am getting a blank report.

Report · Jan 21, 2016

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

Report · Jan 21, 2016

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>

Report · Jan 21, 2016

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

Report · Jan 22, 2016

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.

Report · Jan 25, 2016

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

Report · Jan 25, 2016

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>

Report · Jan 25, 2016

Bonnni, attachments from e-mail do not appear to transfer to the forum. You will need to post through the Web interface.

Cheers

Eddie

Report · Jan 26, 2016

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.

Report · Jan 26, 2016

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

Report · Jan 27, 2016

Here is my group management:

Report · Jan 27, 2016

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

Report · Jan 27, 2016

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>

Report · Jan 27, 2016

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

Report · Jan 28, 2016

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>

Report · Jan 28, 2016

You're welcome. I hope it is a little clearer how calculated fields work and when they should be used.

Cheers

Eddie

Adobe Community

Adding Two Fields

1 Correct answer