Great Plains Maintenance and Recovery Procedures

The Great Plains system is designed to ensure maximum accuracy and integrity of your accounting data. Occasionally, however, your data tables may become damaged. Hardware failures, power surges, and other problems can damage or destroy data.
While damage occurs infrequently, the factors that cause it are difficult to predict or control, and it's necessary to take measures to protect your data. Regularly back up your accounting data and perform table maintenance to minimize risk of data loss from table damage.Only SA or DYNSA can open the Backup Company window to make backups. The backup procedure must be run on the server.
Read the Complete article here GP Maintenance

AA Transaction Error – Primary Key Voilation

There are quite a few errors comes in AA which are not highlighted in the knowledge base articles.
Transaction dimensions can't able to be saved. System had thrown you the following message

• 'Primary key violation error on table AAG00600. Cannot insert duplicate records on table' along with that message there was another saying
• 'The stored proc aagCreateTree returned the following results. DBMS: 2627, Great Plains: 0'.

Resolution
This problem is due to the aaTreeID which is the primary key violation of AAG00600. In order to rectify it follow the process below.
Run all the below steps in SQL Server Query Analyser
Step 1:- select * from dynamics..sy01500 (used to know the company id)
Step2:- select * from dynamics..aag00102 where aatableid = 600 ('600' is the source aa table ID for which the corresponding aaRowID mentioned is the last rowid inserted in AAG00600 for the corresponding is company ID)
Step3:- select * from two..aag00600 (look for the aaTree ID were the last record inserted is holding the same ID as that of the aaRowID in aag00102 table)

If the rowid and the tree id is not same for the corresponding company then you will be getting the above message which will not allow you to save the new transaction dimension for that company. So use the following code to make the rowid as same as that of the aatree id in AAG00600.

Step4:- update dynamics..aag00102 set aarowid = 14 where dex_row_id = 4

Find Missing / Deleted Journal Entry in Microsoft GP

When your company auditor ask for all the missing or deleted Journal entry, it's not a big task now you can get it in minutes by running the script below in your SQL query analyser.
If Object_Id('tempdb..#tempJV') is Not Null
Drop table #tempJV
create table #tempJV
(JVno int,MJV int identity(1,1))
insert into #tempJV (JVno)
(select distinct JRNENTRY from GL20000 )
select wg.MJV as id_is_missing
from #tempJV wg left join #tempJV ti on wg.MJV = ti.JVno
where ti.JVno is null order by wg.MJV

Keep visiting this site for more Dynamics Tips... J

Dynamics Management Reporter

Management Reporter is a specialized financial reporting solution that allows users to create powerful reports in minutes. This application allows executives, managers, and front-line employees to gain access to the information they need when they need it and where they need it. There are 3 basic building blocks in Management Reporter.

The first building block is called a row definition. A row definition defines the descriptive lines (for example, sales or sales returns) on a report. It also lists the account codes or dimensions that contain the values for each line item and includes row formatting and calculations.1

The second building block is called a column definition. 1 A column definition defines the time period to use when extracting data from the financial data source. It also includes column formatting and calculations.

The third building block is called a reporting tree definition. A reporting tree definition is similar to an organizational chart. It contains individual reporting units that represent each box in the chart. These units can be either individual departments from the financial data or higher-level units that summarize data from other reporting units.

Together these three building blocks give users the flexibility to define a virtually unlimited number of reports to meet their business needs. The report definition process is designed in such a way that the average end user can get access to the critical business information they need quickly and easily.

Another feature built into Management Reporter that simplifies the report creation process is the Report Wizard. The Report Wizard gives you the choice between creating one of five different financial reports including a balance sheet, three types of income statements and a trial balance.

Resetting GP 2010 System Password

Forgot your GP System Password? GP Administrator relax, you can now reset System password with the below trick, but be careful using this trick
Run the below script in SQL Server Management studio
USE DYNAMICS
GO
Update SY02400 Set Password = 0X00202020202020202020202020202020
Keep watching this blog for more tricks on GP…….. J

Enhanced Email Option in Microsoft GP2010

Sending Document to Customer's or Vendor's is made easy in Microsoft GP2010 with addition of Email options to the Transaction level. You can select to embed documents, attach documents, or both. If you select to send documents as attachments, you can specify which type of file formats you want to attach the documents as. The options you select in this window are available when setting up or modifying e-mail options for documents, customer records, and vendor records.

If you selected to use an option, such as the DOCX file format, and then decide not to use the option, you must update the customer and vendor records that were set up to use the DOCX file format. An addition feature include the what format you want to send the document to Customer or Vendor and an option of creating a template which can be attached to Customer/Vendor.

Download complete article Download Here

Improved Home Page Metrics for Dynamics GP2010

The metrics, charts, and KPI's that are available on the Microsoft Dynamics GP Home Pages now leverage SQL Server Reporting Services (SSRS) reports.  This enables greater flexibility for our customers in regards to the metrics that are displayed and how they can be accessed which are easily build on SSRS.

• Metrics built using Office Web Components are still available to the users
• Customers can choose SQL Server Reporting Services as their metric source
• Over 100 fully customizable metrics that can appear on the Microsoft Dynamics GP Home Page
• Companies can easily create their own metrics they want displayed on the Home Page
• Metrics are part of the SQL Server Reporting Services Deployment Wizard
• Metrics may be deployed to SQL Reporting Services in either Native or SharePoint mode
• Reporting Services metrics will appear in the SQL Reporting Services Report List; making them available to add as links on the Home Page
• All metrics are available to use in Business Portal as well.
• Users can display multiple metrics on the Home Page at one time, or display just one at a time with the option to scroll through the metrics

How to Setup the Reports?SSRS reports setup is just a screen away provided you have installed SQL Server Reporting Services (SSRS) if not please follow the instruction provided with SQL server installation document. Assuming you have installed SQL Server Reporting Services (SSRS) follow the steps below.

1. Install SRSReports which are under GP2010 installation CD/DVD as additional products.
2. Configure SSRS reports for GP open Programs >> Microsoft Dynamics >>Business Intelligence >>Microsoft SQL Server Reporting Service Wizard.
3. Follow the instruction on the wizard and provide the reportserver path and select the company you want to install SSRS Reports for GP.
4. 
   
   Once done login to GP and navigate to Tools>>Setup>>System>>Reporting Tools setup.  
5. 
   
   There is an important thing that needs to be looked into while configuring the reporting tools for GP, while entering the Report Server URL make sure you enter the URL properly
   • If SQL Server 2005 Reporting Service is installed then the path should be http://localhost/ReportServer/reportingservice.asmx
   • if SQL Server 2008 Reporting Service is installed then the path should be http://localhost/ReportServer/reportingservice2005.asmx
6. Under the Charts and KPI's Section mark only the Option Enable SQL Server Reporting Service Home Page Metrics
   Note: Do not mark Charts and KPI's have been deployed to a sharepoint Server, if you mark this option then you will not be able to view the charts and KPI's under the Home page Metrics.
7. Click OK and close the Reporting Tools Setup Window
8. That's All folks.

Adding Custom Charts or KPI's to Home PageMicrosoft Dynamics GP2010 has made easy to add custom reports which is another powerful and important feature that has made GP2010 customer/user build their own analysis. Follow the below steps to add Custom Reports or KPI's to GP Home Page.

To ADD Reports / Charts Build your custom Report or Charts in SSRS.

1. Deploy the custom report under the Reporting Services for this open SSRS Report Manager and Click on the Company folder
2. Identify your custom report to make sure it is added under proper module.
3. Click on the module you identified and click Charts And KPI's Folder.
4. Deploy your reports under this Folder and give the Proper name that can be viewed under Home Page Metrics Selection in GP2010.

 Hope this helps to add your Custom Charts and KPI's to GP2010 Home Page, for any queries and new Charts to be build contact venuasg@gmail.com

If Check Voided by mistake in Microsoft Dynamics GP

You issue a check to a supplier and then someone erroneously voids that check. How do you recover from this without having to contact the vendor, issue a new check, and stop payment on the old one?
Follow the 7 Steps below

1. First Navigate to Cards>>Financial>>Checkbook
2. Select the checkbook from which the check was issued. If the Duplicate Check Numbers checkbox isn't checked, check it.
3. Click Save. Leave the Checkbook Maintenance window open. We'll be coming back to it.
4. Navigate to Transactions>>Purchasing>>Manual Checks and create a manual check to replace the check (in the system) that was voided. Change the Document Number to the check that was inadvertently voided. Post the transaction.
5. You now have a replacement check in the system for the one that was voided that can be cleared in Bank Rec when the supplier deposits the original check.
6. Document the heck out of this because you'll now have two checks in the system with the same check number, one voided and one not. And sure as can be, this check will be selected during audit.
7. Go back to the Checkbook Maintenance window and uncheck Duplicate Check Numbers.

Deleting an Item in Microsoft Dynamics GP

Before you can delete an item from the item master; there are a number of conditions that must exist.  First make sure that:

1. There should be no quantities of the item are on hand at any site.
2. There should be no allocated quantities of the item at any site.
3. The item is no longer assigned to any site.
4. There are no un-posted transactions for the item exist (work transactions in IV and other modules that update IV like POP, SOP).
5. The item is not a component in any bills of materials in Dynamics GP. Refer to the 'Bill of Materials' documentation for more information.
6. No inventory history exists for the item.

If any one of the above condition fails you will not be able to delete the item.

Microsoft Dynamics AX Architecture

Understanding the internal architecture of Microsoft Dynamics AX can help you make decisions when planning and developing a Microsoft Dynamics AX system. Here are some pointers on DAX architecture primarily for DAX architects & solution developers.
System Architecture

This diagram provides a high-level overview of a Microsoft Dynamics AX system with all components installed, and describes how communications flow among components.
 
 
 
 
 
 
Application Object Server (AOS) Architecture

 
This diagram describes the functionality within the AOS Windows service, and describes how communications flow within it.
 
 
 
 
 
 



Application File Server Architecture

This diagram below describes the application file server.






Client Kernel Architecture

This diagram below describes the functionality within the client kernel, and describes how communications flow within it.





Business Connector Architecture

The differences between the client kernel as it runs on a standard client and a Business Connector client are:
 - The Session Manager in the client kernel manages only a single instance--in the Business Connector kernel, it manages multiple instances.
- The client kernel includes forms security, while the Business Connector kernel does not.
This diagram describes the architecture of the Business Connector version of the client kernel, and describes how communications flow within it.

Application integration framework Architecture

This diagram below describes the functionality within the application integration framework, and describes how communications flow within it.







Enterprise Portal Architecture

This diagram below provides a logical overview of a Microsoft Dynamics AX system with an Enterprise Portal server, and describes how communications flow within it.
 
 
 
 
 
Reporting Server Architecture

This diagram below provides a logical overview of a Microsoft Dynamics AX system with a reporting server, and describes how communications flow within it.

Layers in Microsoft Dynamics AX 2009

Layers are a hierarchy of levels in the application source code that enable you to make modifications and additions without interfering with the application objects in the next lower level. When you make an object modification on one level, the modification overshadows the object on a lower level. You could, for example, decide to add e-mail information to a standard form. The addition would be saved on our level only. The revised form replaces the standard form. However, you can always return to the original form at the next lower level by removing the new form.
Three customer groups have an interest in adding and modifying application objects:

• Application developers who create the standard application.
• Business partners.
• End users of Microsoft Dynamics AX.

Here are descriptions of the application object layers in Microsoft Dynamics AX:
USR - User - Individual companies or companies within an enterprise can use this layer to make customizations unique to customer installations.
CUS - Customer - Companies and business partners can modify their installations and add the generic company-specific modifications to this layer.
VAR - Value-Added Reseller - Business partners use this layer, which has no business restrictions, to add any development done for their customers.
BUS - Business solution - Business partners develop and distribute vertical and horizontal solutions to other partners and customers.
SL1-SL3 - Certified solutions - Partners certified under the Microsoft Dynamics Industry Solution (MDIS) program distribute their solutions in the SL layers.
HFX - Hotfix - The Dynamics AX team delivers critical hotfixes using the HFX layer.
GLS - Global solution - The Dynamics AX Global Development and Localization team provides a set of GLS layers that contain country-specific functionality for regions in which Dynamics AX is released.
SYS - System - This is the lowest model element layer and the location of the standard Dynamics AX application. Only Microsoft has access to the element definitions at this layer.
Objects defined at higher levels of the stack override objects defined at lower levels of the stack. That means USR Layer Object will cast a shadow on the same object in, for example, the VAR Layer. Objects are stored in a separate file (*.aod) on each layer whenever they are saved from the MorphX development environment client. Element definitions are read from these files and dynamically composed by the Dynamics AX runtime. Object instances are created on either the server or the client based on the model element definition.

SQL Query to get Field names as Records

    At times you may need to have the SQL table column names in runtime for your various reasons, below is the simple query to get the columns as records for any given table. Copy the below code and execute in your SQL Query window.
select column_name,* from information_schema.columns  
where table_name = 'Your Table Name'
order by ordinal_position

For More tips on SQL query... keep comeback to this blog

Financial Reporting Standards Compliance with Microsoft GP 2010

I found a very interesting article by – Steve Kane

With the release of Microsoft Dynamics GP 2010, many organizations are finding new ways to improve their business operations.  One of these ways is the ability to prepare standard reports to comply with US GAAP guidelines and make the adjustments necessary to prepare financial reporting compliant with International Financial Reporting Standards (IFRS).  The newly released Microsoft Dynamics GP version uses three separate reporting ledgers – BASE, IFRS and LOCAL – to simplify the reporting process.

As more and more US-based companies operate abroad and have to adopt IFRS standard reporting, the need has increased for multiple standards reporting. To provide the flexibility that accounting departments need, Microsoft Dynamics GP 2010 offers three separate reporting ledgers including:

• BASE (commonly used as the US GAAP ledger)
• IFRS
• LOCAL (for organizations that apply non-GAAP-non-IFRS local accounting rules)

Eliminate Work- around for IFRS Reporting

Before the release of Dynamics GP 2010, manual adjustments were required to present financial information compliant with IFRS.  The recommended solution was a challenging work-around:  create a second GP company to shadow the first, make IFRS adjustments in the second company and, finally, create FRx reports to consolidate the two.

My immediate reaction is…

                WORK-AROUND = PAIN + AGONY + SUFFERING = ERRORS = TROUBLE

We can prove this mathematically…and we have all felt the pain.

IFRS in Dynamics GP 2010

With Dynamics GP 2010, the solution is simple and easy. Regular transactions – Sales, Purchasing, Payroll, Inventory, Financial  – post to the BASE Ledger. The IFRS ledger is adjusted with closing entries and then FRx financial reports can be used to create the reports.

There is an outstanding determination being made for a patch to FRx that would segregate reporting among the ledgers.  Until that patch is released, you should consider segregating IFRS accounts to a unique GL Account segment so that the adjustments can be isolated or separated.

Enhanced Budgeting Feature in Microsoft Dynamics GP 2010

Have you had time to check out the great new budgeting features in Microsoft Dynamics GP 2010? 

I have been browsing the new release of GP 2010 and found some interesting new features that GP 2010 has introduced on of them is the new budgeting feature in Microsoft Dynamics GP 2010 will be a time saver in the accounting department.  Many customers have different departments within their organization that create their respective budgets. Then someone has to combine all of those budgets into one. With the new feature, they can just import multiple budgets to combine them. 

Combine multiple General Ledger budgets into a single master budget 

You can combine multiple General Ledger budgets into a single master budget, one budget at a time. The master budget can be completely new and have its own budget ID, or it can be one of the original budgets. This feature is helpful when multiple departments in your organization create their own budgets, which must then be consolidated. The new "Combine Budgets" window simplifies this process. 

More to come in my next write-up… come back again….

Dynamics AX / NAV / GP / SL

AX (formerly Axapta)

This is properly Microsoft's best product toward the midsize to enterprise level company. It offers many of the enterprise level features, known from bigger packages. It also has strong manufacturing and distribution features. A good solution for a streamlined/centralized smaller enterprise active in many countries. The con about this package is that the projects (just as with other bigger products) often are getting rather heavy and expensive.

NAV (formerly Navision)

The strength of NAV is its usability and rapid development environment. Microsoft primary target this product toward the midsize segment with 20 to 200 users, but I know of many companies with many more users (up to 1000 users). It's also a very good product for international corporations with many subsidiaries in many countries, due to the rapid development and implementations. Good for practically all types of businesses. The cons of this product is properly that it's base application is not as strong as AX and it's SQL implementation is not as good as it could be (but it gets better for each release).

GP (formerly Great Plains)

Another product with nice look and great features with loads of scope on customization, as it used to be one of Navision's biggest competitors on the US market and basically targets the same businesses (midmarket). And even today, you don't find it in many other countries than USA. It's being told that it's general ledger module is one of the best. The con of this product is primary that you don't find it strong in some modules.

SL (formerly Solomon)

Personally I heard about this product many years ago as an financial application, but I wasn't really too impressed. Today this package has been redesigned and is being focused toward smaller and midsize companies in the project management, service and distribution industry. Like GP the cons is that you primary find this product in the USA and week in some modules.

FRx Tips & Tricks – Part 3

FRX DRILLDOWN VIEWER
1. When printing from the DrillDown Viewer, you can select a Print Range of Current Selection' to print only what is currently viewable on screen. This allows you to print the drill down detail for one specific line item.
2. From the FRx DrillDown Viewer, you can export to a spreadsheet by selecting File > Export > Worksheet file.
3. In the DrillDown Viewer, highlight a column by clicking on the column header. Right mouse click and you can hide a column. You can save the file with the changes, so the next time you open this file, the columns you selected will still be hidden.
4. Use the ability to hide columns in the DrillDown Viewer to create a vertical page break for reports you want to print. Hide a section of columns and print the report. Then unhide those columns and hide another section and again print the report.

CATALOGS
1. You can have FRx Financial Reporting generate a report that shows any accounts that are missing from the account range on the row format, as well as any that are duplicated. In the catalog, go to the Report Options tab, Advanced tab, and check the exception report boxes. Generate the report. In the FRx DrillDown Viewer, select from the menu, View > Exception report. An exception report can be exported to Excel, where it can then be sorted. This is helpful when there are many duplicate or missing accounts.
2. To run multiple reports at once, you can chain them together by using the "Chain to Catalog" field on the Output Options tab. Open the first catalog item that you want to generate and chain it to the second catalog item. Then open the second catalog item and chain it to the third, and so on. When you select to generate the first catalog item, or any items in the chain, you will be prompted on whether or not you want to generate the chained reports. If so, select yes and all subsequent chained reports will generate.
3. If you have a report with a reporting tree and you always generate reports for all the branches of the tree, select "Include all Units" from the Tree Options tab under the Reports Options tab. This will save you the keystrokes to select the units every time a report is generated.
4. Use a @CAT code in the header or footer to print the catalog ID on the report. This way you will always know which catalog was used to generate each report.
5. Use the @CATDESC code in the report header to pull in the Catalog description onto the report header. This will save you from typing an additional description for the report. Likewise, when cloning the catalog, the report header description will update automatically to the new catalog description that you type.
6. Create a catalog called "Template" (or any other name you like). Set all the options that are standard for your reports, such as headers, footers, rounding, page options, default date, etc. Whenever you need to create a new catalog, open the "Template" and clone it. This will save you from having to select all of your standard options each time you need a new catalog.
7. Cloning a catalog ID is a better option than clicking on File > Save As. When selecting to clone, the output file name will be updated with the new catalog id. When using the Save As function, item will have the same output file name as the original catalog item. You will then need to manually update the output file name.
8. Whenever you have a report with both Calc type columns and CAL rows, you may encounter a "calculation collision". Where a calculated row and a calculated column intersect, you need to determine which calculation will be printed on the report – the row calculation or the column calculation. If your report has the wrong result, you can change the calculation priority in the catalog. This option is on the Report Options tab, Advanced sub-tab.
9. After making changes to a Report Catalog, save the catalog before generating. When changes are made to a catalog, the catalog should be saved before the Generate button is clicked. If the new changes are not saved, FRx Financial Reporting must create a new record to store these changes before passing the report to the FRx Reporting Engine. These records will accumulate over time, resulting in larger data file and possible corruption if the records are damaged in some way.
10. Using the OLAP output option for FRx Financial Reporting and Excel 2000 you can view cube files in an Excel spreadsheet as a pivot table. Simply choose the output as a Microsoft Local Cube and generate the file. Then launch Excel. Select Data > Import External Data and New database query. Create an OLAP data source and browse to find the .cub file that you created in FRx Financial Reporting. Excel then takes over and you can select the dimensions you wish to display. Graphing and data interrogation then becomes a breeze. The OLAP output option was a new feature in Version 6.0.
GENERAL
1. If you have added a new budget, but do not see it in the dropdown for book codes, you may need to rebuild your index. Likewise if you have added a new account, but the account is not included on your report, again the solution is likely to be rebuilding the index. The index is a file that ends with the extension *.G32. To rebuild the index, go to the catalog screen, choose from the menu, Admin > Build Local Index.
2. If you get an error message that starts with "Check OFSI connection…", check the information in the Company > Information screen. Be sure to check the System Specific Information tab.
3. KEEP REGULAR BACKUPS!! This is the most important step a company must follow for a software program that stores data, including FRx Financial Reporting. Backups should be run nightly if FRx Financial Reporting is used on a day-to-day basis; otherwise backups should be run at least once a week. If frequent backups are not kept, reports and other FRx Financial Reporting data may be lost due to any number of circumstances. While you can attempt to repair a corrupted file, there are no guarantees that repairs will be successful. A frequent backup of the entire Sysdata directory is recommended.
4. Compact the FRx System Database and FRx Specification Sets regularly. This should be done weekly or bi-weekly depending on the usage of FRx Financial Reporting. The FRx System Database contains Companies, Registration Information, FRx Security Information, Users, font styles and other information pertaining to the operation of FRx Financial Reporting. Specification Sets contain Row Formats, Column Layouts, Reporting Trees, and Catalogs, all of which make up the reports that are designed by FRx Financial Reporting users. When one of these databases is compacted, a new file is created with the same structure. User deleted records and other unnecessary information is removed and the remaining information is moved into the new file. When compacted regularly, these files will remain smaller in size and less likely to cause errors and/or lost data.

To compact these databases:

• Have all other users exit out of FRx Financial Reporting – the user performing these steps must have exclusive access to the FRx Financial Reporting files.
• Select from the menu File > Compact FRx Database > FRx System Database. If all other users are out of the program, this should compact successfully.
• Select from the menu File > Compact FRx Database > Current Spec Set.
• If more than one specification set is being used in FRx Financial Reporting, repeat the previous step for each company that uses a different spec set.

FRx Tips & Tricks – Part 2

ROWS
1. Use wild cards and ranges as much as possible in a row. This way, if a new account is added to the GL it will automatically be included in the row format.
2. If you have any pre-existing reports in Excel, you can copy and paste descriptions from Excel into a row format.
3. You can use the Copy / Paste function to / from Excel to utilize the spell check functionality in Excel. Just copy the Description column out of the row format and paste it into Excel. Then run spell check on it.
4. You can Copy / Paste account codes to / from Excel. If you want to combine several natural account codes with several department codes, you can use the concatenate feature in Excel, then copy and paste the results back into the row format.
5. FRx Financial Reporting has a Find and Replace feature. To use it, go to the menu option Edit > Find (keyboard shortcut Ctrl+F), or Edit>Replace (keyboard shortcut Ctrl+H).
6. In the row format, add rows to your report that contain check figures. This will help insure that you have included all the GL accounts in your row format. For example, if the range of account numbers on your income statement is 4000 to 9999, add a single non-printing row at the end of the report that includes that range. Then, have a row below it that calculates the difference between the net income you calculated and the net income of the check row. If there is a difference, the row will print the out of balance amount.
7. You can manually type format codes and column types once you are familiar with them so you do not have to zoom as much.
8. You can put a Remark in your row format. Use the REM format code. Then, in the description column leave a note about the design of the report. For example, when the row format was last updated and why.
9. To format a report with a solid line running across it, select LNE from the dropdown in the Format column. You can select from different line types by putting a TYPE code in the description column. TYPE=1 results in a thin line, TYPE=2 is a thick line, TYPE=3 is a dotted line, TYPE=4 results in two lines, thick followed by thin, and TYPE=5 results in two lines, thin followed by thick. You can apply any font you like to the line to change its color and thickness.
10. To format a report with a box around one or more sections, insert a BXB format code on the row where you want the box to start and a BXC format code on the row where you want the box to be completed.
11. Keyboard shortcuts include pressing F1 to bring up the help screen, F2 to edit and F3 to zoom.
12. To put an IF THEN statement in a row, use the CAL format code in column C and the IF THEN statement in column D.
13. If you run out of row codes between two rows, you can renumber the rows by going to the menu item Edit > Renumber rows. If you use this feature, FRx Financial Reporting will automatically update all the formulas you have input into column D of the row format.
14. To apply a font to multiple rows all at once, hold the CTRL key down and click in the grey section to the left of the rows you want to highlight. Next, with the rows highlighted, select the font you wish to use from the Font Style dropdown window.
15. In Version 6.0 or higher of FRx Financial Reporting, you can use a modifier to have an entire row of data from the budget, effectively overriding the book code selected in the column. To take advantage of this feature, click on the dropdown in column H, click on the dropdown for Row Modifiers, click on the dropdown for book code and select the book code you desire.
16. To correct rounding differences in a balance sheet, go to the menu item Edit >Rounding Adjustments in the row format. Fill in the Total Asset Row, the Total Liability & Equity Row and the Row where you want to place the rounding adjustment. Note: The rounding adjustment row must be a GL account row. You can also place a limit on the total rounding adjustment. If the adjustment exceeds the limit, you will receive an error message. This insures that you don't plug the amount of a GL account number missing from the report. |
17. If you have a row that is only valid for certain columns, put the applicable column letter(s) in column G of the row format. The column letters should be separated by commas. Any columns that are not listed in column G will not show a value in the final report.
18. There are two page break options in the format code column. They are PB and PL. As a rule of thumb, you should use a PI for all reports. PB is used only in a report that contains both a balance sheet and an income statement. This tells FRx that the following page is a balance sheet, or a supporting schedule to the balance sheet. However, the PB code is commonly mistaken to mean "Page Break" instead of its true meaning "Page Balance Sheet." Since a balance sheet does not include income and expense accounts, when this code is used to create page breaks, income and expense accounts will not pull into the reports or the column headings for these entries. So it may appear that FRx Financial Reporting is not functioning properly.

COLUMNS
1. To put an IF THEN statement in a column, use the CALC column Type and put the IF THEN statement in the calc formula row.
2. When you enter a value in the "Extra Spaces Before Col" row, these will be in addition the spaces between the columns as defined in the catalog on the Report Options > Formatting Tab.
3. To create a quarterly report, hard code the range of periods in the period code row of the column format.
4. To dynamically suppress columns that contain periods that are later than the report date, select the P<=B print control.
5. You can center your descriptions in a column. To accomplish this, choose Center from the dropdown in the Justification row.
6. If you want to use a format mask in the row, but can't remember the syntax, you can select the appropriate format mask from the dropdown in the column and Copy / Paste to the row. In the row format, remember to put the special format mask in double quotes.
7. You can show favorable and unfavorable variances as positive and negative numbers respectively. To do this, in your variance column, subtract budget –actual. Then, add the XCR Print Control to the variance column. This print control will reverse the sign on any row in the row format that has a 'C' in the Normal Balance Column. To work correctly, you must place a 'C' on all income related rows including totals, such as Total Revenue, Gross Margin and Net Income.
8. Runtime Error 5: Invalid Procedure Call or Argument. This error message can be received after generating a report with DDV output, right before the FRx DrillDown Viewer opens.This is often caused by Column Headers that spread beyond the last column with information entered or Column headers have the spread set backwards (i.e. – D TO C) or column headers that spread into/across NP columns. To fix this problem, examine all column headers for correct spread. Be sure that none spread backwards or into NP columns. As a last resort, remove all column headers and start over paying close attention to the spreads that are setup for each header.

TREES
1. Any long description entered into column D can be pulled into the report header with a @TITLE code. You can also pull these descriptions into a row format by putting a @UNIT code into column B of the row format.
2. To avoid error messages, be sure to always have the number of ampersands in the account mask of the tree equal to the number of account code characters you have entered into column H of the row.
3. You can have FRx Financial Reporting build a tree for you based on the account segments you have created in the GL. To accomplish this, use the menu item Edit > Add Reporting Units from Chart of Accounts. This can save you from having to type in all the descriptions and the hierarchies will be created for you.
4. When you click on a branch of the tree to drag and drop it, the cursor turns into a dark file folder. The file folder will have an arrow attached to it. A bent arrow means the unit will be dropped as a child and a straight arrow means it will be dropped as an equal to the unit it is dropped on.
5. A unit of the tree must either have children or have an account mask. If it doesn't, the unit will contain no data.
6. When linking to data from an external spreadsheet using the separate method, the columns T, I, and W must be completed and the box in the catalog "use row formats and worksheet links from the reporting tree" must be checked.

 

FRx Tips & Tricks – Part 1

1. Adding Rows from Chart of Accounts:
You can have FRx Financial Reporting build a row format for you by using the menu item Edit > Add Rows from Chart of Accounts. This can save you from having to type all the row descriptions and account numbers.
    Tips:

• Descriptions come first from Account Segment Set-up, and then from Account Maintenance
• This does not do an insert. Adding Rows from Chart of Accounts will write over any existing rows in the row format.

2. Using Account Sets in the Row Format:
You can create Account Sets to group GL account numbers together for line items that you will use on multiple Row formats. Changes to the Account Set update all Row formats that use the Account Set. This will save you time when changes are needed.

Tips:

• Updating an Account Set updates all Row formats in the same .F32 file that use that Account Set. This means that if you have multiple specifications sets in the same .F32 file, updates to the account set will affect all specification sets.
• You can create an Account Set on the fly from the GL Account Links window in the Row.

3. DrillDown Options:
You can use features in FRx to customize the information that you see when you generate reports with account or transaction level detail.

Tips/Tricks:

• Use the Subtotal feature to summarize by Natural Account or any other account segment.

  This can be done on-the-fly in the DrillDown Viewer from the View menu, or it can be pre-set at the Catalog. From the DrillDown Viewer, Select the menu View>Subtotals. From the pop-up window, select the segment on which you want to subtotal. Optionally, you can add an account filter. To pre-set the subtotals from the Catalog of reports, go to the Report Option tab, and the Acct/Tran Detail sub-tab. On the left hand side is the 'Subtotal on segment' checkbox to turn on subtotals.

• Use a XD print control in the Row to stop users from drilling down on specific rows, such as payroll information.

  You may want to generate a report with account or transaction detail, but suppress the drill down to detail for one or more line items. This can keep users from seeing sensitive payroll data, and will also save processing time if detail is not needed for several lines. To suppress the detail on a line, select the XD code from the dropdown in the Print Control column of the row format.

• Add a column to include the Employee/Vendor/Customer Name or Employee/Vendor/ Customer ID in a transaction Drill Down.

  You can obtain details about individual transactions, such as vendor name, source code, etc. as part of a transaction level drill down. In order to drill down the transaction level, the column layout in the report must contain a GL-CUR column to include current period data and be sure to select Transaction level detail in the catalog. To see Employee, Vendor, or Customer names, include a column with the selection of ORMSTRNM. To see the Employee, Vendor, or Customer ID, include a column with the selection of ORMSTRID. Tip: Place the ORMSTRNM or ORMSTRID column to the far right so that the financial level of the report doesn't have a blank column where the transaction detail would print.

• Select 'Transactions in GL' to open Dynamics GP when drilling down to transaction detail.

  If the user in the DrillDown Viewer is a GP user, this allows the users to drill into GP for transaction level detail. Once in GP, the user can then continue drilling down to the actual source document. Tip: You must be logged into GP prior to selecting to drill down to 'Transactions in GL'.

4. Effective Dates for Rows, Columns and Trees in Report Catalog:
You can use this feature to control the selection of the column layout for the proper year's budget. This allows you to keep a single catalog item that will work across multiple years and pull the proper budget information from GP.

Tips:

• Set the effective date window to use Dates and not Periods.
• Set the effective date of the column as the first day of the fiscal year.

5. E-mail Distribution and Tree Security:
You can enable email distribution in the Report Catalog from the Output Options tab, E-mail Options sub-tab. Reports can be email in any of the output file formats, including Excel. When Enable E-mail is selected, the report will be emailed immediately after it is generated. Using tree level security allows you to automate distribution of your reports to various users where only the users specified in the tree receive their assigned levels of the report. You can select one or more e-mail addresses per branch in Column P of the tree. You need to check Enable E-mail and select Use Tree in the Output > E-mail Options in your catalog as well. When you generate the report, each branch will be e-mailed to the appropriate recipient(s) you designate in the tree.

Tips:

• Clone each catalog item that you want to e-mail to create a separate version with e-mail enabled. If email is enabled, when you generate the report, it will automatically send when it is finished. Keeping two versions will help stop accidental email distributions.
• You can select users from FRx Security set up or directly from your email system.
• Turn Off 'Replace File Without Warning' on the Output options tab to keep from forgetting to rename the file that you are sending.

6. Report Distribution Options:
You can automate the distribute or your FRx Financial Reports via several FRx components.

• Report Launcher – Users can be provided with a Report Launcher whereby the user can generate reports on demand that have been created in the Report Designer.
  o Report Launchers can generate ("launch") and view reports
  o Select from menu of existing reports to generate
  o Determine output options and report dates
  o Includes a DrillDown Viewer
• DrillDown Viewers – Users can be provided with DrillDown Viewers for report viewing only. Users with a DrillDown Viewer can receive reports via email or open reports from a network location and utilize the drill down capacities published with the report.
  o Allows users the ability to view FRx reports
  o Streamlines report distribution through email
  o Users can answer their own questions with drill down to transaction detail
  o Loaded locally on laptop or desktop
• FRx WebPort – This provides for publishing reports to an intranet site for on-line report viewing with drill down.
  o Access FRx and non-FRx reports via the Web
  o Allows users to drill down to transaction detail
  o Automates and streamlines report distribution
  o No FRx client software required locally
• FRx Report Server – Allows reports to be scheduled for automated generation. Can be used in conjunction with FRx WebPort to generate updated versions of reports to the WebPorts.
  o Allows users to schedule reports to generate automatically
  o Off-load processing from local desktop to a separate server
  o Automate and streamline report distribution
• FRx Report Manager – Provides the ability to create report books that include both FRx and Non-FRx reports such as Word or Excel.
  o Combine FRx and non-FRx files into a comprehensive report book
  o Generate report books – on the fly or scheduled
  o Distribute via email, hardcopy or Web

Microsoft FRx 6.7 Compatibility with 64-bit version of Windows

Microsoft FRx 6.7 is not supported on a 64-bit version of Windows.

Some parts of FRx 6.7 may work on 64-bit Windows because of the 32-bit compatibility
layer (WoW64). However, this environment is not tested or supported.

FRx 6.7 service pack 11 or later versions is compatible with Windows 7 and Windows
Server 2008, but only with the 32-bit versions.

My recommendation is to install FRx 6.7 on a 32-bit version of Windows (client and server).

However FRx 6.7 can connect to the general ledger database that resides on a 64-bit SQL Server.

 

More Updates in my next post…… Keep blogging J