Use the FRx Email option to automatically send reports generated to Excel

In the past, I've rarely seen a use for the FRx output Email Option. If you email a report generated to the FRx Drilldown Viewer, the email will have the report in a Drilldown Viewer file attached or it will have a link to the Drilldown Viewer file. Unfortunately, a report recipient would need to have FRx Drilldown Viewer installed and more importantly, a Drilldown Viewer user license, to open the file and view the report. A Drilldown Viewer license is fairly pricey, so many companies do not have Drilldown user licenses for their report recipients. Instead, the reports are generated to Excel files and then those files are emailed to the recipients.

Recently, I realized that you can use the Email Output Option to automatically send out a report in an Excel file. For example, if you email to the Board of Directors the Balance Sheet, P&L, and Statement of Cash Flows in Excel files each month, you could set the Output Option on each of the reports to automatically email them to each of the Board Members. The only time this won't work is if you are using a reporting tree and only certain units of the tree are to go to only certain people. There is currently no way to do this. (If anyone has figured out a way to do this, please let me know!)

If you routinely generate FRx reports to Excel files and then email those files out, using the Email Out Option will save you some time.

Tips for Footers on FRx Reports

There may be times when the default placement of footers is not acceptable. For example, if a report is multiple pages, the footer is printer immediately under the last line on the page. This presentation can look sloppy and confusing. Another example is when a report is very short and the footer is way down near the bottom of the page. Some users may want the footer information to print higher, more closely to the report.

To fix the problem of the footer printing too close to the last line of the report, you can add the @BLANK code to one or two of the first lines in the footer. Then enter the actual footer codes and/or text on the next lines of the footer. That way, there will always be a line or two between the report data and the footer data.

To fix the problem of the footer printing too low/too far away from the report, you can increase the bottom margin (on the report catalog under Report Options, Page Setup) which will cause the footer to print much higher on the page. I found that 9.0 was the largest bottom footer FRx would except but that was sufficient for a report that had only 6 lines of data.

Row Format Codes to Create Lines and Boxes on FRx Reports

When you are building an FRx report, don't forget that often the layout/presentation of the report can be very effective in making the report easier to read/understand. One of the layout features you might want to consider is the LNE format code. This feature is helpful for separating sections of a report. If you put this format code on a row in your row format, a thin line will be printed across the page. In addition to a thin line, you have other options if you add text in the Description field on the LNE row as follows:
TYPE=2 will print a bold line across the page
TYPE=3 will print a dotted line across the page
TYPE=4 will print two lines across the page, the top one thick and bottom one thin
TYPE=5 will print two lines across the page, the top one thin and bottom one thick

You can also place a box around a row or rows by using the BXB (box begin) and BXC (box complete) format codes. For example, if you wanted to put a box around the Net Income amount, you would put BXB in the format code of the row above the Net Income row and put BXC in the format code of the line below the net income row. If you don't want the box to be around all the columns, enter the desired columns in Column G. If you want the box to be drawn in something other than a thin line, use one of the TYPE= descriptions noted above on the BXB and BXC rows. In this example, a box with a bold outline will be around columns B and C on the Net Income row.

FRx Top Technical Support Issues

FRx has been around forever and is the most often recommended tool to do financial statements for the Dynamic GP and SL. The life span is coming to an end in the distant future but will still be around for many years. Over the past several years I've made a most common technical support list for FRx. FRx buzz has a top 12 tips list you can check out as well.

Here is my top trouble shooting list in no particular order:

1. Can't launch FRx from GP (Reports>>financial>>FRx) - Tech doc 850786 - The path to FRx is stored in the FRXDYN.INI file in the Great Plains directory on the workstation. If the path was entered incorrectly rename the FRXDYN.INI file, launch FRx again from Great Plains, and choose the correct path to the FRX32.exe file on your particular workstation.



2. Missing accounts in BS or P & L (or financials don't tie) - FRx does not automatically update accounts added in GP. Run exception report in FRx then add accounts that are missing or duplicate.



3. How to create a budget variance report- See Tech doc 862445. Also shows you how do do the budget variance for certain rows. See previous post on how to get budget into GP step 6.



4. Locked rows, columns or trees- Compact current spec set and system database - Tools>>compact FRx database. This solves lots of issues and is one of the first things you can do for maintenance purposes.



5. Invalid work drive error - Tech doc 865772 for citrix, 864623 non citrix - For a temporary fix you can delete the Optional Work Drive path found at Admin>>processing options.



6. Can't see any accounts after page break - Use PI for a page break instead of PB. See tech doc 859445.



7. FRx won't take new year date period or Change default Base Period to C when opening a catalog in FRx- Tech doc 862372 - Delete .G32 files in Sysdata folder. I do this almost everytime I start troubleshooting as it solves a lot of issues. Automated solution for 10.0 is found here. 9.0 users can find it here. Heck, since I'm so nice here's 7.5 and 8.0.



8. How do you share FRx reports with multiple users- Tech doc # 854726- Basically export out all reports in Company>>spec set>>highlight all catalogs, rows, columns and trees and save to a TDB file (just in case). Copy Sysdata folder that you want to make shared and paste in a network location. Point all workstations to this folder in Admin>>organization>>sysdata. If you can't change path there see step 14. Make sure this is being backed up with your nightly backup routine.



9. Error 91 - Sucky error to get. As you see it could be one of 16 issues from tech doc 874276. It's often a permission issue or DLL issue. (DLL automated solution here.)
   


10. Error 8900- tech doc 912977 - Also have seen a company ID on a tree not be a valid company in GP cause this error.



11. Where are the FRx service packs? See MBS link


12. Can't launch FRx from GP - The launch file for FRx is FRx32.exe. The default location is C:\Program Files\FRx Software\FRx 6.7\FRx32.exe. You can put that path in the location in Reports>>Finanicial>>FRx. If that launches something other than FRx report designer you can change the path in the FRXDYN.INI file in the GP folder on the workstation.


13. OFSI errors - Tech doc 858323 - Similar to 91 and 8900 errors.


14. Can't change sysdata folder in Company>>organization>>sysdata - Update path manually in the FRx32.CFG file located in C:\Program Files\FRx Software\FRx 6.7\FRx32.CFG. Make sure there is a \ at the end of the path or it will error out.


15. Can't log into FRx. Usually the company>>information setup is incorrect. See tech doc 865836. Sometimes it's an ID-10-T error. FRx uses the same SQL user as GP. Make sure the login ID is the same as GP not the network user ID that defaults in FRx. Must be exactly the same as GP. (eg. User ID - ted can not be TED.)

Any other common issues I'm neglecting?

No beginning balances in FRx balance sheet

I should have included this in my last post about the most common technical support questions. Usually around this time of year I get a rash of calls about how Dynamics is broken. Seems like all the balance sheet accounts don't have beginning balances even though their P and L have correct numbers.

Dynamics has been this way since the beginning of time. You will not have beginning balances until you close out the previous year (P and L accounts start with $0 each year so they are not effected). I know what you're thinking. "I'm not ready to close out the year yet". I usually have 2 responses to that objection.

1. You can still post entries to the GL to the most recent historical year. So even if you close out the year you can still make your audit adjustments etc. If you do choose this option be aware that the entry will freak you out. It will look like it posted the entry twice. The first is the original entry, the second is the system automatically adjusting the beginning balances for the open year. The second entry is always on the last day of the fiscal year (say 12/31/20xx). If you made your entry in the historical year on 12/31/20xx it will look like it's duplicating. No worries. That's what the system is supposed to do.


2. Do some fancy formatting in FRx. I would recommend a column layout similar to the below screen shot (click on picture for a closer look). The first 2 GL columns would be non printing columns. The first GL column give you YTD numbers for the 12th period of the previous year. The second column gives you YTD numbers for the current year. Column D adds both together.

You would probably want to save this as a seperate column layout so you can adjust back the original layout after you close out the year.

Tricky way to allow you to get a BS and procrastinate your year end close.