Pivot question

(AL) #1

When i convert a table in the BAQ to pivot, it gives me the option to summarize the field i want but as far as the columns or grouping goes, i have to manually specify by typing it in there? Is there a way to group by a field from the table itself instead of manually typing it in?
It only gives me the option of “specified constant list” where i have to manually type in the groups. In my case, i wanted weeks going across the table but i dont want to type that in there. i want to reference a field from the table to group it by.

(Scott Lepley) #2

@althomas, I believe we are stuck with this as it is, at least for now. I was reviewing this functionality just yesterday, hoping that 10.2.200 had new options for the filter values, but unfortunately there is still only one. In our case, we use the pivot subquery to give us a part/price lists/prices matrix with parts as the rows and price lists as the columns, which requires manually entering all (>100) of the price list codes into the constant list. The matrix works great… until the price list codes change! I hope someone else can give you (us) a more encouraging answer. If not, perhaps 10.2.300 will have a surprise…

(John Mitchell) #3

If you are asking what I think you are asking, the only way I have been able to get this to work is with a dynamic SQL statement.

Epicor blocks you from using a SELECT statement as a calculated field but could give you the same functionality from the UI by using a BAQ for the PIVOT columns. That sounds like a Feature Request to me.