How do you define a "Department"?
I would use the BKAPHPO table to get that information. You can use the following columns as Dpt
bkap_po_obycus = Job Number
bkap_po_entby = Buyer
bkap_po_loc = Location
bkap_po_gldpt = GL Account
This query will show you only totals, use it as an example to get the department details Then use Excel to query the data (You must have a ODBC Connection on your computer)
It is quite easy.
Option Explicit
'==============
Public curr_wb As Workbook
Public main_sh As Worksheet
Public var_sql As String
'==========================================
Public Const odbc_erp As String = "DBA_ERP" 'Your ODBC Connection
Public erp_conn As New ADODB.Connection
Public erp_rs As New ADODB.Recordset
Public erp_cmd As New ADODB.Command
'==================================
Private Sub get_data()
Dim i As Integer
Set curr_wb = ThisWorkbook
Set main_sh = curr_wb.Sheets("Main")
main_sh.Range("A4:C2000").Value = Empty
i = 4
'Open Conn
erp_conn.CursorLocation = adUseClient
erp_conn.Open odbc_erp, "USER", "PASS" 'If you have one
var_sql = "select MONTH(bkap_po_orddte) AS THE_MONTH,LTRIM(RTRIM(bkap_po_obycus)) AS THE_DPT,ROUND(sum(bkap_po_subtot),2) AS THE_AMOUNT from BKAPHPO where bkap_po_orddte >= '2019-01-01' and bkap_po_orddte <= '2019-12-31' group by THE_MONTH,THE_DPT;"
erp_rs.Open var_sql, erp_conn, adOpenStatic
Do While Not erp_rs.EOF
main_sh.Range("A" & i).Value = erp_rs("THE_MONTH")
main_sh.Range("B" & i).Value = erp_rs("THE_DPT")
main_sh.Range("C" & i).Value = erp_rs("THE_AMOUNT")
i = i + 1
erp_rs.MoveNext
Loop
'Close Recordset
erp_rs.Close
'Close Connection
erp_conn.Close
End Sub
Kelloggs
PD:
I just realized a flaw with this sql query. The Order Date could not be on the same month as of the Receiving
We can use the BKAPHPOL table, and the BKAP_POL_ARD column to get the month, if you want by month