Reporting Tools for Dynamics GP Short Review

Another thing to mention on the top – there are special tools for Financial Reporting (Balance Sheet, P&L): FRx and in the latest version 2010 you can also consider Microsoft Management Reported as the alternative to FRx.  This list is for you to think about and do not pick the tool without making your homework.  We’ll try to give you each tool short review and which report should be considered for the tool:

1. FRx Financial Reporter.  Let’s begin with financial statements instrument.  We observed several situations, where customer made a mistake in our opinion and hired, let’s say Crystal Report designer to do all the reports for GP, including consolidated P&L.  Theoretically you can build Financial report in Crystal, but there too much business logic to program in underlying SQL Stored Procedure (which should be the base for the report).  First of all the financial year might be open or closed (in technical language it means that you need to pull GL entries either from GL20000 or GL30000).  Then, the second thing is – there are Account Summary tables.  We can continue, this is just the top of the iceberg.  FRx (or MMR) has its value in resolving Great Plains General Ledger business logic for you and leaving you just the work to build Row Formats, Column Layouts, Reporting Trees.  Plus, if you are larger organization or multinational corporation, the business might be structured as a set of legally separate subsidiaries and you need the consolidated versions of P&L and BS.  In FRx this is elegantly done via Reporting Tree

2. Report Writer.  This tools is internal and you typically modify such reports, as Sales Order Processing Blank Invoice Form (everyone wants corporate logo to be on the top of it, don’t you think so?), plus various AR/AP Aging, GL Trial Balance reports.  By the way – if you want to export data from old version of Great Plains (on Pervasive SQL 2000/Btrieve or Ctree) and you do not want pay for the consulting, consider to create custom report in RW and pick the table to export, like customer master RM00101m as the base for your report and print it into text file (then open it in Excel and do the magic of preparing to data conversions into your future dream Corporate ERP, such as SAP Business One, PeachTree, QuickBooks, or you name it – every ERP brand should have an option to integrate CSV or tab delimited text file)

3. Microsoft Dexterity.  Technically it is not just report design tool, this is the whole architecture for Great Plains Dynamics with its own Integrated Development Environment, Sanscript programming language.  In some situations you have to customize existing RW report in Dex to enable something, that is not possible in pure RW.  For example, let’s say – you would like to show Payroll Batch description to be printed on Payroll Registers.  The way you do it – you move report to Dexterity (making it alternative modified report from the standpoint of GP security concept), add rw_* function programmed in Dexterity Sanscript (this is required prefix to make custom function available for being seen and placed on modified report).  Please, be a bit conservative – Dexterity is not something very simple – you will have to hire Dexterity programmer with proven experience (you cannot turn your in-house IT coding guys into Dex software developers overnight).  But, it is doable and the option should be open and considered

4. Crystal Reports.  This tool is perfect in our opinion for various managerial reports (not Financial reporting): Sales Commission, Project Profitability (where you join sales and purchasing tables, associated with the same contract), Barcode labels (when you print them upon the POP Purchase Receipt posting).  Here, however we would mention you that other generic reporting methods, such as SQL Reporting Services, MS Access Reports, Excel should do similar job, assuming that you as report designer provide the connection to SQL Server database (might be native SQL Server client driver or ODBC DSN).  We would like to warn you about common mistake in Crystal Reports design for Microsoft Dynamics.  This is mid-market Corporate ERP, MRP, HR, Accounting application and its business logic is pretty advanced, meaning that it is not self apparent on the table record level.  If you would like to try the pass to design CR via the wizard, chances are high that you will be trapped in deadlock situation, where your report produces all kind of unexpected duplicate lines.  In order to avoid this problem, CR should be based on MS SQL Server Stored Procedure or at least SQL View

5. Couple of words on Great Plains Accounting for DOS or Windows (9.5, 9.2 and earlier).  This archaic Corporate ERP application is based on different technology and not Great Plains Dexterity.  In GPA you have reasonable set of reports, which you can print into text file (very helpful if you are doing data export for the following data conversion to your new small business accounting application), plus you can open its Btrieve based tables to ODCB connection via DDF files

6. Cross Platform Reporting.  There might be scenarios when you would like to join the data coming from Great Plains Dynamics and other databases.  The most common scenario is when you have you Sales Order Processing outside of the ERP application in custom database (Oracle, MS Access, Foxpro, IBM DB2, MySQL/PHP, etc.).  Consider creation of MS SQL Server so-called Linked Server, where you open up Oracle database for cross platform SQL View or Stored Procedure

7. Please, give us a call 1-866-304-3265, 1-269-605-4904, or email us help@alba866.com We are happy to help you and provide second opinion on failed FRX, Crystal Reports design, Report Writer.  By the nature of our service, we serve you USA, Canada nationwide and internationally.  Local walk in service is available in Western Michigan, Southern California, Chicagoland, Houston, Atlanta (SAP Business One only service is available in GA)