Hello out there - I have been dealt the honor of writing a custom report for a client. He is looking to see his companies revenue broken down by customer, and he also wants to be able to see which G/L account the revenue hit. This will be my first report to write. Does anyone know what tables I can pull the necessary information from and/or any tips on writing this? Any and all help will be much appreciated. Thanks in advance. Kevin
Start reading sales invoice (and CM’S) headers+lines (Tables 112,113,114,115) to get the customer sales. If in your sales lines there are NOT only G/L Accounts, you have to search the G/L Entry table (T17) with the Document No. (+ posting date) of the invoice (or CM). In that table you have your invoice devided over the different G/L Accounts.
Isn’t the customer no. in the field “source no.” of all sales posings??
All Customer Ledger Postings go through the “Cust. Ledger Entry”, and the Posting Account is Assigned from the “Customer Posting Group”, it is Marked on the Customer Ledger line as “Bal. Acc. No.”, Which is the G/L “Debtors Control Account”, so you could say Invoice 10001 went to Account 2310, The Customer Ledgers do not run 1,2,3,4,5,6,7,8,9 etc:, but take thier number from the “G/L entry” that it is associated to, If you then wanted to see the G/L Details for that ledger, you can get all the entries by linking to the “G/L Register” range (“From Entry No.” “To Entry No.”) then get the “G/L entries”, which is fine for a single posted document etc: I am not sure how this work’s if you post a batch, or from a Journal batch you will have to test that. It is true that there is a “Source Type” and “Source No.” on the G/L Entries, but again I don’t think that will give you any more than the “Customer Ledger Entries”, as they are only marked on the “Control Accounts”, and there is no suitable Key on the “G/L entry” table the “Customer Ledger Entries”, which will have a correct table key to use “Customer No.”, etc:, are best for you to start with, you can get Net, VAT and Gross Values as well as Original Value and Discount, so most of what you need might be there already. However Lets look at a batch, take 5 Orders with the same 5 different products, you will have 1 “Customer Ledger Entry” for each Invoice, 1 Debtors control for each Invoice, and could have 5 entries, one per line for easch invoice, but what happens with the Invoice lines is that it works at a document level, Document type, Document No. but the register will say From 123445 to 123471 and will apply to all Invoices from the batch, so you would have to filter out for each invoices for the details. If you did a report from the “G/L Entries” and the Invoice had 20 lines, you could wind up with report, With 1 Debtors Control Value, and a load of lines for the Product Postings, i don’t think that would be the requirement. So you can just use the Wizard, Table = “Cust. Ledger Entry”, look for a Key, “Customer No.” or “Customer Group, Customer No.”, if you want totals, Include the “Bal. Account No.” on the detail. Firstly there may a standard “Customer Ledger Report”, if there is open this and save as, then add the G/L Account No. which is “Bal. Acc. No.” field if that is all you need. Hope this is a help [;)]
Thanks for all of the input. I will be starting this report this week and now I have a starting point. Thanks again - Kevin