Skip to main content
Cozmo2
Inspiring
January 21, 2016
Answered

Adding Two Fields

  • January 21, 2016
  • 19 replies
  • 2810 views

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?

This topic is closed to new replies. Start a new post to keep the conversation going.
Correct answer EddieLotter

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

19 replies

Cozmo2Author
Inspiring
January 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

EddieLotter
EddieLotterCorrect answer
Inspiring
January 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

Cozmo2Author
Inspiring
January 21, 2016

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

EddieLotter
EddieLotter
Inspiring
January 21, 2016

Please show the text of the query for the report.

Cheers

Eddie

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices