This formula cannot be used because it must be evaluated later

[Former Member]

I have a formula that I have created that I want to bring into the record selection formula. But when I go to the record selection screen and browse the "Report Fields" my formula field that I created is not listed there.

Knowing some Crystal Syntax I knew how Crystal was going to title my formula. I named the formula "Month 1" so Crystal would then create it as {@Month 1}.

When I typed in {@Month 1} in my record selection I got back the following error when I checked the record selection formula for errors. "This formula cannot be used because it must be evaluated later"

My Month 1 formula contains the following

-

---

if {dim_Date.FiscalMonthNBR} = {Command.Previous Fiscal Month} then

Sum ({fct_CurrentSales.Cases}, {@upc}) /

(tonumber(Maximum ({vw_dim_Product.CA_MPN_JMelBoxesPerPallet}, {@upc}))) / {Command.Previous Fiscal Month}

-

---

Let me explain what this is doing, there are 2 divide statements. First we need to get the result of Sum ({fct_CurrentSales.Cases}, {@upc}) /

(tonumber(Maximum ({vw_dim_Product.CA_MPN_JMelBoxesPerPallet}, {@upc})))

Then take that result and divide it by / {Command.Previous Fiscal Month}.

Am I doing something wrong? Is there a way to get this formula to evaluate sooner rather than later so I can be able to bring it into my record selection formula? I don't understand why I can't bring this formula into the record selection.

Thanks

[former_member260594 · Accepted solution]

Hello Levi,

Record Selection evaluate before summaires. As you are referencing aggregates in the month1 formula they cannot process until after the record selection has occurred and data is returned to the report.

I would recommend doing the aggregation on the database side either in a stored procedure or in a SQL Expression.