Highlighted

Adding Two Fields

Explorer ,
Jan 21, 2016

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

TOPICS
Reporting

Views

1.8K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Adding Two Fields

Explorer ,
Jan 21, 2016

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

TOPICS
Reporting

Views

1.8K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jan 21, 2016 0
Advocate ,
Jan 21, 2016

Copy link to clipboard

Copied

Please show the text of the query for the report.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 21, 2016 0
Explorer ,
Jan 21, 2016

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 21, 2016 0
Advocate ,
Jan 21, 2016

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 21, 2016 0
Explorer ,
Jan 21, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 21, 2016 0
Advocate ,
Jan 21, 2016

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 21, 2016 0
Explorer ,
Jan 21, 2016

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 21, 2016 0
Advocate ,
Jan 21, 2016

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 21, 2016 0
Explorer ,
Jan 22, 2016

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 22, 2016 0
Advocate ,
Jan 25, 2016

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 25, 2016 0
Explorer ,
Jan 25, 2016

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 25, 2016 0
Advocate ,
Jan 25, 2016

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 25, 2016 0
Explorer ,
Jan 26, 2016

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.Offer.jpgTotalCost.jpgTotalCostProp.jpg

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 26, 2016 0
Advocate ,
Jan 26, 2016

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 26, 2016 0
Explorer ,
Jan 27, 2016

Copy link to clipboard

Copied

Here is my group management:

GrpMgmt.JPG

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 27, 2016 0
Advocate ,
Jan 27, 2016

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 27, 2016 0
Explorer ,
Jan 27, 2016

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 27, 2016 0
Advocate ,
Jan 27, 2016

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 27, 2016 0
Explorer ,
Jan 28, 2016

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2016 0
Advocate ,
Jan 28, 2016

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2016 0