Each Corporate ERP application should be exposed to Crystal, SSRS, or another reporting tool designer via ODBC or native database driver connectors. In generic case, where you are report programmer and you are trying to get the initial help, check if your Corporate ERP or Accounting application has Software Development Kit (SDK), where its technology layer should be documented for Software Developers, Coders and Reports Designers
In our small publication we will concentrate on Microsoft Dynamics GP tables structure,
fields, tables groups and how to get further information in reports design. The most typical reports, as we heard from Dynamics GP, Great Plains Dynamics customers are Sales Commission, Sales by Product/Item Class/Region, Product Profitability (where you link Sales Order and Purchase Order Processing modules data for specific item class or item group), Item Barcode printout report (especially in Retail or Warehouse Management operations, where customers prefer to have Dynamics GP as initial barcoding source, versus having it in such applications as Microsoft Retail Management System), Project Profitability Report, etc. As bonus topics, we will also touch some international business Corporate ERP issues, especially when your corporation is planning to expand to Russia or Brazil. If your case is not listed here, feel free to contact us directly. Let's begin:
1. Dynamics GP SDK. As current version of GP 10.0 as well as earlier versions: 9.0, 8.0, 7.5, 7.0, 6.0, 5.5 is or were distributed on CDs, you can install SDK from CD #2. We expect version 11.0 to be distributed on DVD (it should be released in May or in first half of 2010). Microsoft Dynamics GP SDK has complete tables diagram, which you could print out or review on your screen. This method is very good for profound analysis (when you expect really complex report, combining several GP modules in one report). Dynamics GP SDK sometimes is confused and referred as GP eConnect (this SDK is initially dedicated to ecommerce Dynamics GP developers, currently eConnect covers majority of Dynamics GP modules and functionality. For version 10.0 eConnect SQL stored procedures are installed automatically, as they are required by 10.0 version technology)
2. Shortcut to get quicker to the info about GP table, field or form. If you think that there is no need for you to invest your time in Dynamics GP SDK tables structure diagram, you can simply log in Great Plains and then click on Dynamics GP -> Tools -> Resource Description -> Tables (fields, or windows). Feel free to do self discovery - as typical example we would like to give you Sales Order Processing Work Transaction Tables Group: Select Tables, product name Microsoft Dynamics GP, Series Sales, sort by Table Group Technical Name, and then locate Sales Work. You should be able to find SOP10100 (Sales Transaction Work - this is your SOP document header table) and SOP10200 (Sales Transaction Amounts Work - this is SOP Document line table). These tables are very popular in eCommerce reporting and integration scenarios for Microsoft Dynamics GP Great Plains
3. Additional advises on Crystal Report, SSRS design. In our Dynamics GP consulting practice we saw multiple cases, when Crystal Reports designer failed completely in report delivery. Typical mistake was to try CR wizard in linking GP tables on the report level. We strongly suggest you to consider separation of reports presentation and data pulling logics. Crystal Reports, SSRS - these are very good reporting tools, but they do not have to be perfect in reports base SQL query programming. If you like to enjoy flexibility and total control on the report results, consider separating report SQL pulling login in MS SQL Server, Union, View or Stored Procedure. If you follow this advice, such issues as rows duplication (this bug is very difficult to catch, as CR doesn't allow debugging service, and usually you are blamed for duplication when report is already in production)
4. Reports design for older versions of Great Plains Dynamics, eEnterprise or Great Plains Accounting for DOS, Windows or Mac. Here you have to deploy ODBC connections to Pervasive SQL 2000 (or Btrieve, however large number of Great Plains Accounting installations were ported to Pervasive SQL 2000 SP3 or 4). If you are on Pervasive SQL 2000, you should review DDF files generation (for GPA and Great Plains Dynamics on Pervasive SQL/Btrieve) or Ctree ODBC driver connection methods. You can also consider building reports in Microsoft Access, where you have robust linked table option, or MS SQL Server 2000/2005 linked server construction (this method is highly recommended as it allows you to combine cross-platform data on the same report, for example from Oracle base Sales Order Processing external application and Great Plains Dynamics on Pervasive SQL 2000)
5. Crystal Reports for SAP Business One. SB1 is recommended if you are relatively small multinational corporation with headquarters in USA, Canada, Australia, New Zealand or South Africa with Microsoft Dynamics GP Corporate ERP application and you are expanding to such regions as Brazil, China, Russia, Germany, Japan, Korea, where Dynamics GP is not available in its localized version. Please, review SAP B1 SDK in order to understand SBO tables structure and interrelations. There is also the option to review table and field names if you log in SAP Business One, select File -> View System Information and then place your cursor over the form and field - technical info (table and field names) will be shown in the left down corner status bar. SAP B1 was initially designed with International Business considerations and we know examples when major multinational corporation decided to switch completely from Microsoft Dynamics GP (Great Plains) to SAP Business One in its headquarters after implementing SAP Business One for Brazilian subsidiary. SAP Business One crosses the borders of ASCII table and it support Unicode and hieroglyphs: Chinese, Japanese, Korean (which is very limited or almost not possible in Dynamics GP - you can try limited Crystal Reports for Great Plains with Chinese language support, if you consider NJ Star extensions)
6. Dynamics GP/SAP Business One puzzle if you are moving to Russian, Ukrainian, Kazakh market as multinational company. Technically Russian language is compliant to ASCII standard. However Russian Federation has very complex accounting reporting and compliance legislation. Microsoft Dynamics GP is not localized for Russian market (you can consider Axapta or Navision, if your foreign subsidiary Corporate ERP budget permits). SAP Business One is available and certified in Russia (where it has tough competition with local Accounting packages, such as 1S Bukhgalteria). We plan to publish special cycle of articles, dedicated to Russian Corporate ERP market, where we will be discussing SAP, Microsoft Dynamics and local packages pluses and minuses, FAQ in 2010
7. Dynamics GP / SAP B1 combination for Brazilian Subsidiary. Here you might face the situation, similar to described above for Russia. In Brazil you also have Microsoft Dynamics AX and NAV localized (with some restrictions) and SAP Business One is very advanced, but facing tough competition from local Corporate ERP packages, such as Microsiga
8. Please, give us a call 1-866-528-0577, help@albaspectrum.com http://www.albaspectrum.com We speak English, Spanish, Portuguese, Chinese, Russian. Our consulting rates are very competitive and we should be able to cover your domestic and Worldwide Corporate ERP consulting and support needs. We support all the versions of Microsoft Dynamics GP, Great Plains Dynamics, eEnterprise, Great Plains Accounting, SAP Business One
