Sales Register Report: Great Plains Dynamics GP Reporting Samples
If you are controller or responsible for tying numbers from General Ledger back to Sales Journal, then you may like to have one place, where you see your Monthly or Quarterly Sales Ledger distribution in General Ledger. Also, we heard observations from corporate accounting people, as well as from Dynamics GP functional consultants that this report is typically difficult exercise if tried to be designed in FRx or Dynamics GP Report Writer (assuming that you are not taking Great Plains Dexterity customization route). In this small publication we would like to give you hints on how to design this report in Crystal Reports:
1. Reporting Logic. This report should summarize all Accounts Receivable account Debits (with Returns, counted as negative debits), but only for transactions with SJ and RMJ types: meaning only Sales Order Processing Invoices, Returns and Receivables Management module Invoices, Debit memos, Credit memos. This AR Debit summary should not include any payments. Now from the Credit side you should look at all Sales related accounts: Sales, Freight, Miscellaneous Charges, Sales Tax Payable, VAT payable
2. Under the hood. We assume that you are on Microsoft SQL Server 2005 or 2000 DB platform and you will be building SQL query as SQL stored procedure and will base Sales Journal Register report on the SQL stored procedure, where you will supply Date From and Date To parameters fields. Ideal life looks very attractive, but in real life you see various alterations to SOP and RM documents distributions. This is why we recommend you to build SQL Select statement to get a list of all the accounts, participating in Sales Invoices and Returns distribution. You can get it from GL20000 table, again making restrictions on Journal type: SJ and RMJ. Accounts numbers and names should be queried to GL00100 table – Account Master
3. Sales Journal Transactions Audit. Include Select statement details in hidden body section of the report with the option Drill Down OK. If you review the structure of GL20000 table, you will realize that it includes Customer ID in ORMSTRID field, Customer Name in ODMSTRNM and Sales Invoice or Return number in ORDOCNUM field
4. Let us know. The paragraphs above should give you very good start. If you feel, that you are not really Dynamics GP technical consultant or Crystal Reports Developer, feel free to call or email us
Source by Andrew Karasev