BAQ Pivot calculated fields can not use in 'selected fields'

[Tony (Wu)]

I try to create Pivot BAQ to summarize the amount by month. when I test the BAQ, the result comes up and is correct. but can not save this BAQ, because EPICOR pops up a warning.

when I click on the Pivot Subquery, it pops up following another warning message

and following showing the calculated fields ‘Month’ formula

I noticed that I created a similar BAQ before the EPICOR updated. they can be saved without any issue.

but if I open the BAQ, the system pop up with the same warning, look like the EPICOR changes the rule.

could anyone help to solve this problem?

thanks,

[Aaron_Moreng]

Sometimes naming the calculated field with a name that is also a SQL keyword can cause issues. Try renaming the calculated field with something like xMonth instead of Month to see if that helps

[nguyennkerp (Nguyen Khoi Nguyen)]

Hi, try to use integer.

[Tony (Wu)]

thank you for all the response. i try to rename the field name and also the data type to integer. the same issue pop up.

[Tony (Wu)]

problem solved.

I think the problem is that the key field Mth for the PIVOT clause can’t be filtered by any subquery before PIVOT.
I created the field Mth in Subquery1 and referenced to Subquery2, and the Subquery2 is filtered by some criteria, then referenced to the subquery of PIVOT. that caused the problem.
I deleted SubQuery2 and referenced Subquery1 to the subquery of PIVOT directly. the problem is solved.