Dynamics GP FRx Financial Reporting Notes
For current version of Microsoft Dynamics GP Great Plains 10.0 and 11.0 (expected to be released in May 2010), as well for all the historical versions of GP and Great Plains Dynamics and eEnterprise/Dynamics/CS+ (including versions for Pervasive SQL and Ctree): 9.0, 8.0, 7.5, 7.0, 6.0, 5.5, 5.0, 4.0 and earlier, FRx Reporting tool is the instrument of choice for Financial Reports design: Balance Sheet, Profit & Loss Statement, Statement of Cash Flow and consolidated versions of these reports. Theoretically you could design the same reports in Crystal or Microsoft SQL Server Reporting Services (SSRS), Excel, Microsoft Access Reporting, but this would take thousand percent more time, comparing to FRx. The secret of FRx is simple - it has special connector to various Corporate ERP applications General Ledgers: Great Plains, Axapta, Navision, Solomon and others. This article is written for technical consultants and IT professionals in your company and we are addressing FRx questions, which might be too technical:
1. Current version of FRx is 6.7. For Dynamics GP 10.0 Service Pack 9 (or more recent) is required for Dynamics GP 9.0 Frx 7.6 SP 5 is recommended. FRx 6.5 is for Dynamics GP 7.0 and 7.5. For earlier versions of Great Plains Dynamics FRx version typically should coincide with GP version
2. FRx anatomy. In FRx Reporting you have several simple building blocks: Row Format, Column Layout, Reporting Tree, and Catalog (there are several additional substructures: Account Set, Specification Set, but these are outside of the scope of todays article). FRx Specification Set file is in essence Microsoft Access password protected database (we expect a lot of vapor release emotions at this point, as MS Access is something that many IT professionals consider as computer technology legacy). We had customers, who needed to export FRx reports structures to SQL Server and we helped them to crack down FRx Default Specification Set MS Access Database
3. Lets begin FRx report building. Required parts of FRx report are Row Format and Column Layout. If you are building FRx Profit and Loss Statement report with details by each month of the current or historical year - this should be incorporated in 12 months Column Layout. In Row Format you are deciding on the report rows. Simplest option is to add rows from existing accounts, but more sophisticated approach is to create accounts sets (for sections of Current Assets, Long Term Assets, Deferred Revenues, Current Liabilities, Customer Deposits, etc). Account Set allows you to drill down to real accounts and potentially posted and even unposted transactions (here I am really talking on behalf of Microsoft Dynamics GP Great Plains)
4. Consolidated Reporting. It requires FRx reporting tree. Normally, you create similar natural accounts (main segment in Dynamics GP GL terminology) with similar or almost similar (or recognized as similar) names
5. FRx reports tuning: missing or duplicate accounts report. Often, as Dynamics GP consultant you face the situation, when you are given Excel Chart of Accounts and in building your FRx Balance Sheet you discover that Excel Chart of Accounts is not 100% accurate and now you have to add accounts to various sections of Balance Sheet (we are not talking about P&L, as BS has one line, where you virtually add all P&L as Current Year Earnings)
6. FRx Balance Sheet and P&L tuning recommended procedure. The best way is to build FRx report directly adding accounts from GL of the default company. If you are building FRx consolidated report, please consider simple technique of closing Report Catalog and switching default company
7. Beyond FRx. Managerial Reporting (often referred as industry reporting), typically doesnt look at General Ledger details and rather to specific module details and summaries. Dynamics GP Managerial Reporting could be done in GP Report Writer, Crystal Reports, MS Excel, SSRS, direct SQL Select Statement
8. Crystal Reports Alternative. Please, see above - we already mentioned that FRx is preferred in Financial Reporting (BS, PL, CF and consolidated versions). CR is rather form managerial reporting (Bill of Lading, Invoice form with Graphical Logo, Purchase Order Form plus Requisition form)
9. FRx Future. We heard signals from Microsoft Business Solutions that FRx will be replaced in few years with the Dynamics Financial reporting tool, built on the base of Microsoft SQL Server Reporting Services (SSRS). In any case, MBS should provide robust FRx to SSRS migration tool or option
10. 12. FRx for Dynamics GP consolidated reporting, when overseas your subsidiary deploys non Microsoft Dynamics Corporate ERP, such as SAP Business One. Please, review the option to consolidate Dynamics GP company with MS Excel Worksheet (export from SAP Business One Trial Balance report to Excel)
11. We support all the versions of FRx, if you need help, feel free to call us 1-866-528-0577, or email us at help@albaspectrum.com
12. Bonus topic, popular question about FRx in earlier January 2010 (and we assume for the following years). You are getting error message "Changed Default Base Period to C", and then there is no way for you to print FRx Balance Sheet, P&L or another report for January 2010. This issue is linked to the fact, that you didnt yet setup 2010 Financial Year (Fiscal Periods for the year 2010 in Microsoft Dynamics GP). To fix this issue, please follow the next paragraph recommendations
13. Tools->Setup->Company->Fiscal Period, then in the window in Year editable field type 2010and click on Calculate button above
Then log off FRx, login back and Rebuild GL Indexes (typically automatic process when you login FRx). If you have multiple companies, simple method to rebuild GL indexes is
A. In FRx->Company->Information, then change company and click on Set as Default (on the left bottom corner you should see status line, that it is rebuilding GL indexes)
B. Repeat step 1 for all the companies
C. Change default company to original one
At this point FRx should work normally
Andrew Karasev, Alba Spectrum http://www.albaspectrum.com help@albaspectrum.com 1-866-528-0577 - Microsoft Business Solutions Great Plains MS CRM Partner, serving corporate customers in the following industries: Aerospace & Defense, Medical & Healthcare, Distribution & Logistics, Hospitality, Banking & Finance, Wholesale & Retail, Chemicals, Oil & Gas, Placement & Recruiting, Advertising & Publishing, Textile, Pharmaceutical, Not-Profit, Beverages, Conglomerates, Apparels, Durables, Manufacturing and having locations in multiple states and internationally. We are serving USA Nationwide: CA, IL, NY, FL, AZ, CO, TX, WI, WA, MI, MA, MO, LA, NM, MN, Europe: Germany, France, Belgium, Poland, Russia, Middle East (Egypt, Saudi Arabia, OAE, Bahrain), Asia: China, Australia, New Zealand, Oceania, South & Central America: Mexico, Peru, Brazil, Venezuela, Colombia, Ecuador, Chile, Paraguay, Uruguay, Argentina, Dominican Republic, Puerto Rico. Please visit our information portal Pegas Planet: http://www.pegasplanet.com