See 12 of the top FRx tips, trick, techniques and shortcuts
I’ve been consulting with FRx since 1995, and these are a few of the top tips and tricks I’ve run across over the years.
1. Set up a link to Excel to pull statistical data into FRx financials. How? The simplest way is to doubleclick column H ‘Link to GL’ in the row, change the link to ‘GL+Worksheet’, and specify the Excel filename. But be aware there’s another way, one that’s used when the units in your tree all need to read a separate file. Use the ‘separate worksheet link’ method in this case. My favorite way to do any of this, though, is to stick the statistical data in the GL. Saves lots of headaches! There’s more information in my post Link to Excel to Calculate Statistics.
2. Generate several reports with one click using chaining, being careful of the 4 inherent traps: watch your report date, making sure it’s the same for all reports. Watch the detail level: if it’s financial and account and you’re sending everything straight to the printer, you’re likely to get reams of paper you don’t want. Watch the output type (printer, drilldown viewer, excel, etc) and make sure it’s consistent for each report. Finally, for any reports with trees, set the tree options (Catalog> Report Options> Tree Options) so that the reports process without human intervention. Or for a better way, check out the Report Manager, an optional module in 6.7, which allows the creation of report books containing Excel, Word, Crystal, and FRx reports. And here’s a link to a chaining alternative: use Report Launcher instead of chaining.
3. Electronic Distribution.Email top level reports with detail available in drilldown: Set the Detail Level in the Catalog to Financial & Account. Get the report perfected, and only then go to Output Options> Email Options and enable email, set your recipients, and generate. How to avoid a ‘gotcha’: be sure to uncheck this for future use, or you’ll send all your recipients unintended copies. Your recipients must have Drilldown Viewer licenses, and when they open the file, they’ll be able to drilldown and see the detail behind a total.
4. Create a cash flow statement.This one is always hard for those new to FRx, but it’s actually not too bad (when you do them day in day out). There is a catalog in FW (the demo company) called Cashflow_FASB that you can use as an example, but if you use it be sure to note that its column placement is set to ‘Calculate Columns First’. (For what it’s worth, I use a much simpler method, but at least this example exists.) In every case you should start with a simple balance sheet change cashflow, get it right, and only then move on to pulling in note proceeds and payments and the like from Excel. And some companies STOP at the balance sheet change cashflow, export to Excel, and edit from there to finalize.
5. Use drilldown capability to analyze GL transactions. Your column layout will need both a CUR and a YTD column. Set the detail level in the catalog to Financial & Transaction. Generate the report to the DDV and drill down. And be sure not to send the entire report to the printer! Here’s a post with more information: Get More Information From FRx Using Transaction Detail Drilldown.
6. Set up consolidations (even multicurrency or different GLs). The reporting tree is the driving force behind the consolidation. Once the companies are set up in FRx, they will be selectable in the tree. Make your catalog point to the consolidating tree, and you’re essentially done.
7. Create budget variance statements by cost center. The main trick here is to use the ‘XCR’ print control in the variance columns in order to get the favorable/unfavorable variance sign correct on both the revenue and expense sections. The second trick is to subtract the Actual column from the Budget column in the Variance columns. Last but not least, in the row format, you should make sure there’s a ‘C’ in the Normal Balance column for any TOT or CAL row that uses a row that has had its sign flipped with a ‘C’. Add a cost center reporting tree and you should be good to go.
8. Plan and design the best way to use FRx to begin any reports that must be completed in Excel.You can really give yourself a headstart by starting in FRx, even if you have to finish in Excel. Also, when in Excel, it’s a good idea to separate your report from its data. That way you can copy your data in each month but still have your report retain its formatting.
9. Analyze existing reports to make them run faster: unless it’s not possible due to unusual design considerations, the natural account should be in the row and the tree should specify the departments or cost centers. The report processes much faster this way, not to mention the streamlined maintenance.
10. Use the View Subtotals feature in the Drilldown Viewer to analyze different segments of the account structure. This is one of my alltime favorite FRx features. Here’s a post on Using the FRx View Subtotals Feature to tie out.
11. Generate a report to a PDF and send it to your own SharePoint site. There’s more information on generating a PDF from the drilldown viewer in my post Creating PDFs from FRx Reports.
12. My Favorite FRx Shortcuts. I LOVE shortcuts and timesavers, so Tip 12 is actually a link to 12 of my favorite FRx shortcuts!
Lagniappe (Cajun for ‘a little something extra’):
Use Excel to automatically graph FRx reports. There’s more information in my post Executive Reporting: Using Excel Charts with FRx. Enjoy!
(Note: I’m updating this post today, July 12, 2010. I’ve added a few links to posts and replaced one tip with another, better one.)
My FRx tells me that I cannot add any more columns. Is there a way to override this or is it really limited to 225 columns?
I thought the limitation was more like 256, but if that’s what it’s telling you I would believe it. You may be able to continue the report in a 2nd catalog: clone the 1st, then use the new catalog with another column and chain the two. I have a post to do sometime soon, courtesy of Rick Tenorio, that explains the process in more detail. Jan
Thank you so much! I look forward to that post! Please let me know when you do it!
Alli
I’m trying to create an Operating statement budget variance (favourable/unfavourable), but can’t seem to to get the ‘XCR’ print controls in the variance columns to work properly in the column format. I have both revenue and expenses in the row format…is there something I need to do in either the row or column format to signal when to switch the signs?
Oops…saw the answer on one of your previous posts from Jan 2007. Problem solved. Great site by the way – very useful tips.
In FRx, is there a way to replace a zero amount with a “-“? I only see option to display a 0 or “”.
Thanks!
Yes, in fact I have a post on how to do just that. Do a search (at the top of the page) for ‘hyphens’ and you will find it. Jan
I look forward for more tips and tricks
about your 1 click report generate
I am using batch to do that.
For more information, please look here. It is very useful.
http://support.microsoft.com/kb/964873
I hope you can do an article on rolling qtr report b/c I need more help with that.
Reece, thanks for your comment and I really liked the link. VERY interesting. Nice to see people using FRx in unexpected ways. Jan
Thanks. This helps a lot. Doing research on how to copy canned report settings from the demo company FW to my company. Any ideas?
Thanks Marie! Looking at the demo reports is a great way to learn. You can pull individual building blocks by using File>Save As, but if you want to pull an entire report over along with the report catalog, it’s easier to Export it from the Company>Specification Sets window, then import it into your company spec set in the same window.
Is there a way to filter an account row and make it work with a reporting tree? Our account structure has an acct-ofc-dept-profit center. I am filtering some of the rows in the row format to specific profit centers (ie. 510000-????-????-5210) but because the row formatting is overriding the tree when I try to run the reports by office with an office tree I’m getting duplicated values.
Any tips or tricks for doing this? Or can’t be done?
Thanks in advance!
Hi Anita…I had a question about this just the other day so I should probably post on this sometime soon. The full account in the row will override the reporting tree as you’ve already discovered. The solution is to use column D in the row format to restrict an individual row to a certain reporting unit (or rollup of reporting units). Jan
Thanks for the quick reply.
Are you saying to use Column D in addition to the account filter? Or use the tree feature in column D to get to the specific data?
I didn’t pursue a tree for the row specific info because the Profit Center is subsidiary to the office, so if I create an OFC and PC tree then I couldn’t figure out how to get to my profit center specific information for the row – it would be by office and each office would have each profit center and I couldn’t represent on a single line.
If build a PC / Ofc tree then I can get the overall PC segment for the row, but then don’t have the ability to select the Ofc specific unit for the office report.
example:
Birmingham
PC5000
PC5001
PC5002
Atlanta
PC5000
PC5001
PC5002
vs. PC5000
Atlanta
Birmingham
Dallas
PC5001
etc…
My rows need to work for both the Summary/Consolidated Report and a per Office report.
Salaries – PC5000
Salaries – PC5001
Salaries – PC5002
Only certain rows need to be limited to a specific profit center, other rows are representing all of the data.
Perhaps I’m just still missing something on this. Definitely need to step away from it for a little while; I’ve been going round in circles too long! Thanks
Hi Jan,
I am trying to export an FRX TB report at Financial & Transaction level to excel. For some reason it is getting stuck on Page 2 (which is the transaction level.) Do you know why this is so? Thanks in advance for your help!
Hi Josephine…there are some issues exporting to excel with Service Packs 10 and 11, so that could be it. You might try generating and exporting just the financial level first, then move onto the transaction level. See if that helps any, although it’s not a good longterm solution. Jan
Jan,
I created a Statement of Cash Flows, but all of a sudden it just went out of balance with the GL. It was workig fine. I have checked the row format, the column as well and see nothing missing. Where do I begin to troubleshoot?
Thanks
Paula
There’s lots that can go wrong with a cash flow, but for any report out of balance, I always begin troubleshooting with exception reporting in the catalog on the advanced tab. JJan
Is it possible to print lists of all my row, column, and tree formats, as I can print a list of my catalogs with the Catalog Summary Listing? I hate to have to scroll down my lists in a tiny window, doing screen shots and then pasting them together. I have FRx release 6.7.5014.
Not exactly, although the File>Print Catalog option in the catalog screen has an option to Print Detail which includes the row format for each catalog. Another option is hacking into the access database, but barring that, I maybe have a tool you can use for documentation. I’ll look and email it. Jan
I’m searching for a manual that will literally walk you through setting up a report in FRX. Is there any type of “Dummies” book for FRX out there?
Hi David…there isn’t a dummies book but I wrote a manual that does exactly what you’re looking for. It’s FRx in 8 Hours Introduction (there’s also an intermediate). Here’s the overview link: https://www.frxbuzz.com/frx-training-manuals/
Good luck! Jan
Any way to put a logo in FRX reports?
Thanks. Jacob
Not exactly. A definite no in FRx, but if you’ve gotta have it you can export to excel and add a logo and manage printing there.
Hi Chuck…there’s an optional FRx add-in called Report Server, and it will schedule reports for you. Given FRx’s status, whether you can still get your hands on it is another story. :)
Hello,
Is it possible to export multiple catalogs/reports into separate worksheets in one MS Excel workbook/file?
My TB, B/S, I/S and Expense Schedule are all separate catalogs, which I want to output to separate worksheets in one MS Excel workbook/file.
Thanks!
No…each catalog, when exported, creates its own excel workbook. Content can’t be added to an existing workbook…the only option you have is to replace the file. So manually combining remains the solution. Good luck…Jan
Thanks for the feed back Jan.
I was sent a link from another forum that allows the combining multiple spreadsheets into one.
See link below.
http://www.mrexcel.com/forum/showthread.php?t=77475
Excellent! This will work…you still have to output your FRx catalogs to separate workbooks though, and then use this to combine outside of FRx.
FRx has the option to print the details of Rows, Columns and Trees. However I am getting an error whenever I try to do this as a user. sa can output this information. Any ideas ? Error: 0. Occurred in: PrintCommandDialog()
Hi Jan,
Where can I find the solution to Justin’s problem dated 2/23/09?
“I’m trying to create an Operating statement budget variance (favourable/unfavourable), but can’t seem to to get the ‘XCR’ print controls in the variance columns to work properly in the column format. I have both revenue and expenses in the row format…is there something I need to do in either the row or column format to signal when to switch the signs?”
Thank you.
Paolo
btw – I am asking my supervisors to have you come in for some one-on-one trainings.
Hi Paolo…the post Justin was referring to is Color Coded Budget Variances using FRx and Excel.
Would love to come help one on one. I just looked at your website and have done a good bit of work in your industry and have lots of happy clients in NY :)
Cheers—Jan
Currently having a problem when utilizing both the Row Rounding Adjustments and calculating % variances in my columnar layout. When I do this, I get a warning that a certain value is far exceeding the limit for the warning. However, I don’t see this value in the report. Have you seen this issue before? We’re using version 6.7.2013 with Epicor.
Take a look in the row format under Edit>Rounding Adjustments and you’ll see the row number where the rounding adjustment is ‘plugged’, as well as the rounding limit. Good luck…Jan
First, I appreciate the Q&A provided by this website.
Second, I have created a trend P&L that displays all months up to the month entered. Above the Revenue section I want to enter a row labeled “Billable Days” for each month and have it display a pre-determined number. What is the best method to do this?
Thx…Paul
First, thanks for the compliment! Second, you can either book ‘days’ in your GL and use a row modifier in the row format to change the book code, or link to excel. Jan
I have an issue FRX 6.7 is duplicate value for some accounts , but only for one month? I try all the Microsoft KB that was published but the error persist. Thanks
Hi Alfredo…suggest doing a search on this site for ‘duplicate values’. There are 2 results that may be of help. Also make sure your row doesn’t contain a full account structure; that will override the tree and give duplicate values. Good luck! Jan
Hello! I stumbled across this site through google and it appears you really know your stuff! I was wondering, do you know how to hard code an amount below a column of GL links? I am trying to take my net income and then subtract a hard coded number at the bottom to tie my FS to the audited ones. Any ideas would be a great help! Thanks!
Hi Jen…thanks for the compliment! Yes, no sweat on the hard coded amount. This works for either FRx or MR. You put a CAL in column C format code, then just enter your number in column D. Usually a number in column D will have an @ sign preceding it; that means it references a row code in column A. Any number without an @ sign is treated as, well, a number. Enjoy! Jan
Thank you so much! Any idea how to make it show only under the CY and not the PY in a comparative statement? Frx is not my specialty, clearly!
Yes, you can use the row’s column G to restrict where a row prints, but the downside is that then the row really can’t be used with any other column layout. (In MR, I believe it’s column H and it’s called Column Restriction.)
Have you done (or know if it is possible) to create a cashflow statement that is bassed on a set of predefined terms? For example, if we know accounts receivable terms are 30 days, we want to age that by 30 days, expenses are 7 days, and therefore aging it by 7 days.
Thanks!
Hi Joe…I guess theoretically it might be possible, but you’d probably rip all your hair out trying to get it. As would I. For stuff like this, I usually start with a cashflow that uses just the balance sheet accounts, export it to Excel, and then monkey with it in Excel. Jan
I am getting a message, “Runtime error 8-Subscript out of range” when trying to export an income statement into excel. Has anyone encountered this or know how to fix it?
That’s sort of a catch-all error…could be lots of things. I usually start with googling it. Good luck…Jan
Great site! I recently linked a FRx report to an excel spreadsheet to pull in a value that, for the rest of the FRx report, shows a % of Rev calculation in the next column over. I do not wish for this particular row to be effected by the calc, almost like including a headcount figure. Any easy way to disregard?
Hi Tom…yes indeed. In the row format, column G Column is used to restrict what prints. (In Management Reporter, it’s column H Column Restriction.) Give it the letters for the columns that you DO want to print, for instance ‘A to G, I,J,K’. Take a look at Help for more syntax. But here’s the downside: once you restrict columns in the row, your row format can now only be used with that ONE column layout. So instead of reusing one row with many columns, you’re limited. Furthermore, you need to look at what reports the row is used in to determine the impact of this change BEFORE you make the change. You might decide it’s not worth the trouble, or you might decide you want to make a copy to use for the restricted columns. Nothing’s ever simple, is it! Jan