Skip to main content
C
Cozmo2
Inspiring
January 21, 2016
Answered

Adding Two Fields

  • January 21, 2016
  • 2 replies
  • 2793 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 has been closed for replies.
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

2 replies

C
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

C
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