Hello Everyone,
I am trying to create a BAQ Pivot to show Monthly Total of STK-MTL for specific part numbers. Parameter is: Enter year and it should show the Monthly usage for that year. Is Pivot the best approach or there is something else better?
I’m just a newbie and trying to improve my knowledge thru this wonderful community. Thank you in advance.
Good day, Paul! Welcome to the forum!
Pivot is not something I’m well practiced in, so I played around this morning to give myself a little more experience. See if the below gets you close…
1st Query (InnerSubQuery)
Start with a query of only the PartTran table. Add a Table Criteria so it only returns records where TranType = STK-MTL
Here are my display columns:
Note… I don’t know why I called them “OrderMonth” and “OrderYear”… this has nothing to do with “Orders”… so, may want to give them a better name. I think I was thinking about Purchase Orders while I was putting this together.
Calc Fields:
Because your MonthlyQty is a aggregate calculated field, you’ll need to “Group By” all the others:
2nd Query (Top Level)
I pulled in the Part Table and then joined the 1st query on Company = Company and PartNum = PartNum
I created a Parameter called Year:
Then added that to the subquery’s Table Criteria, that way, it will only return records where the OrderYear = the year typed into the Parameter.
Here is the Pivot added to that subquery:
So, I’m summing the Calculated_MonthlyQty)… based on OrderMonth IN a constant list where I just manually added columns 1 - 12 to the constant list.
Display Fields:
Testing the query, I get these results… it prompts me for a year:
2023:
2024:
So, you can see the sum values change depending on the year you enter.
You can further add parameters if you want your user to enter a specific part number… or you can add a constant list in as a table criteria on the Part Table if you want to limit your results to a set of specific parts as you mentioned in your post.
Might not be exactly what you’re looking for… but hopefully it gets you started and you can build it out from there.
Cheers!
If you are NOT a database/IT person (I am an accounting/finance guy)
You can use the report that is already in the system…
Material Management > Inventory Management > Reports > Material Transaction Detail
Output Format = CSV
Enter your dates
Go to "Filter " tab, then “Transaction Type”
Go to “Part” tab
Enter your part or parts
Lastly, if you have your schedule setup you can have this report AUTO-GENERATE at EOM.
Hi David,
Thanks for welcoming me. I have tried the method you suggested and It Work Perfectly. Thank you! Much appreciated.
I have added the list of part numbers on Part table and got the results I wanted.
2025
Hi John,
Thank you for your response, and this would really come in handy as well. Much appreciated
