FRX
FRx 6.7 SP11 Released
Some of the main benefits of SP11 are as follows
- Windows 2008 and Windows 7 will be supported now.
- FRx WebPort will be supported with Windows 2008 and Windows 7 and Windows Vista will still not be supported.
- Office 2010 will not be certified in this cycle. However, there was some limited testing and it does work. 64-bit version of Windows will still not be supported, Customers will have to use Virtual Machines. A new feature in Windows 7 allows users to download Windows XP Mode. This is a new feature in Windows 7, available in Windows 7 Professional, Ultimate, and Enterprise, provides a licensed copy of Windows XP with Service Pack 3 in Virtual Hard Drive (VHD) format. When installed with the proper integration components, it allows you to run Windows XP in its own virtual machine, separate from the host Windows 7 installation. The location for the download and additional information about XP Mode is available at this URL: http://www.microsoft.com/windows/virtual-pc/default.aspx.
- The previous issues with percentage and dollar formatting using CS are resolved in this service pack, along with other fixes in the service pack listed in the Release notes.
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
Listing of Microsoft Great Plains tables used by Microsoft FRx
FINANCE tables:
1. GL00100 Account Master
2. GL10110 Account Current Summary Master
3. GL10000 Transaction Work
4. GL10001 Transaction Amounts Work
5. GL00200 Budget Master
6. GL00201 Budget Summary Master
7. GL30000 Account Transaction History
8. GL40200 Segment Description Master
9. GL20000 Year-To-Date Transaction Open
10. GL30001 Account Summary History
11. DTA00100 Analysis Group Master
12. DTA00200 Analysis Code Master
13. MC40000 Multicurrency Setup
COMPANY tables:
1. SY40100 Period Setup
2. SY40101 Period Header
3. SY00300 Account Format Setup
SYSTEM tables:
1. SY00302 Account Definition
2. SY003001 Account Definition Header Dynamics
Tables that are directly read when generating FRx Financial reports are as follows:
1. DTA10200 Transaction Analysis Codes
2. GL10110 Account Current Summary Master
3. GL10111 Account Summary History
4. GL20000 Year-To-Date Transaction Open
5. GL30000 Account Transaction History
6. MC00201 Multicurrency Summary Master
7. MC30001 Mulitcurrency Account Summary Master
Dynamics Tips 