• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

The remaining text does not appear to be part of the formula error

Crystal Reports 2016

I have a formula where I want to sum a field based on a Datetime field called E911Data.EntryDateTime using a parameter value -1 to get the records from the previous year then that of the year contained in the parameter.

I get a The remaining text does not appear to be part of the formula error after sum({E911Data.Agency}).  What is causing this error?

sum({E911Data.Agency}) where Year({E911Data.EntryDateTime}) = {?Pm-?Year} -1
1 Solution
Crystal doesn't have a WHERE clause on the sum

To do this you need 2 formulas

Value Formula
Name - PreviousYearValues
If  Year({E911Data.EntryDateTime}) = {?Pm-?Year} -1  then

Open in new window

The second formula does the sum
Name - SumPreviousYear

Open in new window

Create both formulas
Put the SUM formula on the report where you need the sum displayed.  The other formula doesn't have to be on the report.

Another way to do this would be to drop the value formula on the report
YOu can then RIGH CLICK it and select summary
Set up the summary as a sum and put in the footers where you want it

MikeM670Author Commented:
Thanks.  I did not realize you could not use a Where clause with a Sum().  Sometimes I tend to test things in sql before crystal.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now