PartTran baq help

Kinetic ERP
,
1

Today has been long and my brain isn’t liking this puzzle and decided to ask for help.

I’m needing to have every part in PartTran to have 1-12 for months of the year with the number of times a transaction has happened against it within that month; even if it’s zero.

Note: I’m restricting to time frames and manufactured parts.

2

You can make 12 calculated fields,
image

Then in each field’s code, you can put this for the corresponding month number:
image

Keep in mind this doesn’t take into account years. It will count all occurrences, doesn’t matter which year it is. You may have to give it a date range if you want to get only this year.

sum(
  CASE
    WHEN datepart(month, PartTran.TranDate) = 12 /*enter month number here*/
      THEN 1
    ELSE 0
  END
)
3

That would work. Would like to get the months in the same column.

4

All months into one column? like “Jan: 14, Feb: 1, Mar: 3, …”?

5

Like 1 50
2 25
3 659

6

There is a guide in Epicor help on how to do a pivot which I believe is what you want

7

JM707_Pivot.baq (35.9 KB)
Here is a Pivot Table Example.
I put this baq in a dashboard, added to the menu structure then added it to the context menu for PartNum.
You can right click and get the shipping history for a manufactured part or the issued history for a purchased part since may company the two part types don’t mix these type of part transactions.
The nice thing about using the Pivot Table approach is speed which SQL generates the table is awesome.
Also totaled and calculated monthly averages and if you scroll to the right get Part Plant Min and Max also the newer fields SavedMinimumQty and SavedMaximumQty for planners to use this screen when using the new “Min Max Safety Mass Update” program.
Otherwise delete fields and use as you like.

1 Like