Dynamics GP Reporting: FRx, Crystal, ReportWriter, SSRS Notes For Developer
If your Microsoft Dynamics GP is implemented successfully, next step is probably Reporting Tools selection for specific reports you have on your list. As you may expect, reports could be categorized and each category has its own preferred tool. If you are coming from Crystal Reports designer career path, then we respect your possible statement that Each report could be (theoretically) designed in Crystal Reports, however for mid-market Corporate ERP, such as Dynamics GP, business logic might be too complicated for compiling something like Profit and Loss Statement in Crystal Reports. Lets try review reports types and preferred reporting tool for each type. As a comment to the style - we will try to be technical enough and at the same time not too technical to keep balance for IT and non IT professionals in your organization. Lets begin with financial reporting:
1. FRx Financial Reporting. The advantage of FRx lays in the connection automation to General Ledger tables. FRx is simple and at the same time very powerful in opening up GL for various corporate ERP platforms: Dynamics GP, AX, SL, NAV, plus range of non-Microsoft, such as Epicor. If you are Controller or VP Finance, it should be easy for you to get basic FRx training in reporting block creation, such as Row Format, Column Layout, Reporting Tree, and Catalog (or final report, such as Balance Sheet, P&L, Statement of Cash Flow). Reporting Tree enables Consolidated reporting: consolidation of multiple companies (even if some of them are present on Excel worksheets), or account segments (or even parts of the segments, or account wildcard based). For those of you who are programmers - FRx catalog is hosted in Microsoft Access database. If you login FRx, then go to Company->Specification Sets look at Default and you will see that it is likely in FrxRpts.f32 file. You can open this file in Microsoft Access (assuming that you know how to open password protected MS Access Database). And here you can program FRx row format mass updates automation routines, for example. In MS Access FRx database is very simple in discovery, so we had several customers, who decided to export FRx reports into MS SQL Server views and base new Crystal Reports on these views to completely migrate from FRx to Crystal Reporting. In our opinion - this is not absolutely necessary or even recommended, but lets move on to the next tool Crystal Reports Designer
2. Crystal Reports. For Dynamics GP professionally written Crystal Reports are typically based on SQL View or Stored Procedure, where Stored Procedure allows you temporary tables deployment, where you can deploy complex table records SQL Cursors. CR is best (or you may prefer SSRS, which has similar reports design philosophy) for Great Plains so-called Managerial Reporting, such as Commission Report (based on your own and often complex formulae, with consideration of Credit Card fees, for example), Bill of Lading, International Reporting (Chinese hieroglyph invoice, calling data from GP Sales Order Processing Tables: SOP10100 and SOP10200) and Custom SQL Server database, where you log Chinese invoice related info, entered directly in Chinese. If you are new to Crystal Reports design, we would like to encourage you into studying how CR could be based on SQL View first and then review Stored Procedures. These approaches are more efficient, comparing to Crystal Reports Wizard. Natural question for CR designer is where could I find Dynamics GP tables structure - you can install Dynamics GP SDK from CD#2 or simply in GP user interface Dynamics GP->Tools->Resource Description->Tables. This is safe to create SQL Views and Stored procedures directly in Dynamics or GP Company database
3. Dynamics GP Report Writer. This is natural choice for GP forms modification, such as Sales Order Processing Blank Invoice Form, SOP Historical Blank Invoice, AP Graphical Check. RW allows you to place your company logo on the form, move fields to desired positions, make them bold or italic (be sure that you switched to Graphical version of the report). We heard some complaints from our Great Plains customers, that RW is too limited and non WYSIWYG compliant. This is true to some extent - RW is Great Plains Dexterity programmed module and Dex in turn is the shell coded in C++, where not all current graphical bells and whistles are available
4. Microsoft SQL Server Reporting Services or SSRS. This reporting tool is progressing rapidly, it is web based and has good advantage in software license cost, comparing to Crystal Reports. However if you consider basing reports on SQL View or Stored Procedure, both tools: SSRS and Crystal Reports are about to be similar. Well, there is good news - you can preinstall set of SSRS reports from Dynamics GP CD directly, especially if you are on GP version 10.0
5. Dynamics GP SmartList. In Smart List you can create simple reports and export them into MS Excel. Smart List reports are defaulted to show only 1,000 records, please be sure that you supersede this parameter, before exporting to Excel
6. Other reporting tools. As Dynamics GP is hosted in Microsoft SQL Server, you can deploy your tool of choice, which is ODBC compliant, and can connect to Great Plains company database: MS Access, Excel and other tools.
7. How to get help? Please, feel free to call us: 1-866-528-0577, outside of USA: 1-630-961-5918 or email us help@albaspectrum.com We are very technical and real Dynamics GP Dexterity, SAP Business One SDK programming gurus. We have Great Plains Software Development Factory and could support unlimited Dynamics GP Customization and Programming needs. Plus we speak English, Chinese, Spanish, Portuguese, Russian, and not only as native speaking sales folks, but as real technical consultants. If you prefer skype: albaspectrum
Andrew Karasev, Alba Spectrum , help@albaspectrum.com 1-866-528-0577 http://www.albaspectrum.com Serving ERP and MRP community since 1994, local service in Chicago, Houston, San Diego, Atlanta, Los Angeles. Information portal: http://www.pegasplanet.com We serve you USA and Canada nationwide from our Dynamics GP and SAP Business One call center: California, Illinois, New York, Florida, Minnesota, New Jersey, North and South Carolina, Georgia, Louisiana, Texas, Ontario, Quebec, Washington, Missouri, Iowa, Nebraska, Kansas, Colorado, Nevada, New Mexico, Utah, Arizona, Montana, Oregon, New York, New England. International clients welcome from Australia, New Zealand, Oceania, South Africa, UK, Brazil, Caribbean, Russia, Central America: Honduras, Costa Rica, San Salvador, Panama, Nicaragua, Guatemala