Beginner Pivot Question

Epicor ERP 10
1

So I’m working with a pivot table in a BAQ. I see that the number of rows that shows up in the pivot is related to fields that you have in the lower level, even if they are not shown (or used). So I’m trying to pivot by date ranges and I have the inner subquery calculating a group based on the ranges. So I have group A, B,C, and D based on the parameters entered. I got rid of every field that I was not using with reduced the rows down to 4 per part number, with only one of the columns per row having number in it.

So I want one row, which each column populated for each part number. I can do that by running the pivot as a sub and them sum up the fields and get it to one row. Is this the best way to do that? It seems clunky and I feel like I am missing something simple… but maybe that is just what I get for not being able to custom code the SQL query myself. I’ve tried some google searches but haven’t found anything that quite gets to this problem. Is this something specific to E-10?

From this

to this

2

I had a similar problem and the crux of it was that I was trying to pivot on a calculated field. So I ended up with a three-tier query. The bottom tier was my raw data, including the calculated fields. The middle tier had only the fields on which I wanted to pivot (but at this level I didn’t need the components of the calculated fields, just the fields themselves). And the top level is the one that does the pivot.

Not elegant, but I think it works – at least it gets me only one row of data per part (using your example).

3

Why not group by the part?

sorry saw this was a 7 month old post. I am sure Brandon has this working by now

4

That’s basically what I did. Sum() which is an aggregate function and requires a group by. You can’t group by without an aggregate function (as far as I know).

I could be missing something though. Is there a better way Ken?

5

That is what I would have done!!!

6

Brandon,

I’m glad that I’m not the only one who thinks this is clunky. I had to create a monthly ship summary for one of my users, but she wanted it listed out in one row for each part number:

Part Number Year Jan Feb Mar …

And the way you did it was the only way I could come up with. I’d like to know if anyone else has a different approach.