Problem with Math In Calculated Fields

Forum|Forum|18 years ago
October 18, 2007
2 replies
3050 views

I am calculating a group incident rate for data returned from a query. The formula is Number of cases multiplied by 200000 divided by number of hours worked. Cases in my report is the calculated field: calc.CaseSum (the sum of cases for the group) Hours is calc.SumHours (the sum of hours for the group). The actual values for these variables (for the first group are 48 and 29427171 respectively. When I create the following calculated field called rate using the formula: (calc.CaseSum * 200000) / calc.SumHours, Cold Fusion Generates a Runtime Error:

Invalid ColdFusion expression in report. If the expression is a string, ensure that it is within quotes. Error: (calc.CaseSum * 200000) / calc.SumHours is not a valid ColdFusion expression.

If I use the constant value "29427171" as the divisor, the report works albeit only for the first group. Any ideas; is this a bug, or am I misusing the product?

Addition: I forgot to mention I am using CF8. Also this formula worked fine as a Report Total before I introduced grouping and modified the calculated fields to reset on the change of a group.

Reporting

This topic has been closed for replies.

T

tony17112acst

Inspiring

Really,

Try dividing first, then multiplying. You shouldn't have to but in the mean time try it.

If you multiply first, you are generating a HUGE number; I wonder if that's where CF is getting an error.

If you divide calc.CaseSum by calc.SumHours FIRST ....THEN multiply by 200000, you are getting a smaller number BEFORE multiplying by 200000.

It's just a guess.

If it still craps out, try using CFSET to divide the two numbers, then another CFSET to multiply that answer by the 200000. I bet that'll work. Please let us know if it did.

-Tony

R

reallyrapidrayAuthor

Participant

Sorry, I've been on another project for awhile. This problem will certainly be a "show stopper" for me if I cannot resolve it. As I mentioned in my original post, I used a constant in the formula in lieu of the variable and the calculation worked. This would suggest that CF does not have a problem with a large number.

In spite of that reasoning, I tried Tony's suggested (thanks by the way!) with the identical outcome, only difference is the new formula is displayed in the error message.

Tony, you also suggested that I set the variables using CFSET... How would I do this within the report writer environment. I had tried a similar approach: to perform half the calculation i.e. that within the parenthesis, and assign that value to a separate "calculated field: and then perform the rest of the calculation on that variable with the same outcome.

I think that I may be dealing with a CF bug here, I'd like to find a workaround... I've noticed that CF8 has a new patch, perhaps after I apply it, I may be able to get this thing to work. I'm on another project right now so it will be a few days before I can test this theory, I report the result.

Should this fail, and no one can come up with a workaround, I will report this to Adobe.

C

Chris_Brandt

Participant

I'm seeing similar issues with even simpler calculated values.

I have a query field (query.Lectures) which is an Integer
I have a calculated field (calc.sumLectures) which is an integer sum of query.Lectures.

I get the same error:

Invalid ColdFusion expression in report.
If the expression is a string, ensure that it is within quotes. Error: query.Lectures is not a valid ColdFusion expression.

If I change the data type on the calculated field to 'String', the error stops, but the values don't actually add. I just get the result of the last record.

This same cfr runs great on CF7, but when running it under CF8 all I get is grief.
Any help Adobe? Dean?[

A

Anonymous

I've been tearing my hair our about the same error message for hours! I had the following in the Expression Builder..

query.totalamount / report.getCustCount(query.clientcode,query.country)

It ended up that report.getCustCount(...) was evaluating to zero for a small number of records in the report, which CF obviously didn't like. I removed the offending records and the report is now working perfectly. Now I'll go and figure out where I'm letting data like that into my report query

I'm using CF8 and its version of the CF Report Builder.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded