Putting the full account number in the row overrides the tree. Are you toast? No.
Most of the time, FRx is used with the main GL account segment specified in the row, and the remaining parts of the account structure are specified in the optional reporting tree. Often, however, there is a need to specify the full account structure in a row or two (I’m using a headcount example today), and this causes inaccuracies.
You should be very aware that anytime there is a full account number in the row and you’re using a tree, it overrides the tree, resulting in duplications. So if you’re using 4500-????-??? in the row, you get all the 4500′s for every department in the company. If 4500-????-??? totals $8,000 and there are 10 units in the tree, your summary total is going to be $80,000. You will get the full $8,000 in each reporting unit. It’s overstated 10 times. Not so good.
So what to do? Sometimes you just have to use the row without a tree.
Or you may have a situation in which you may be able to use the following technique. In this example we’re dividing the headcount in one department by the headcount for the whole company to determine a percentage (usually used for allocations).
Here’s the row. The way you set this up without duplication is to set up a second Link to GL column. Your detail units will use the original link in Column H (called GL1 in the tree below), and your top level summary unit will use the second link in Column I (GL2 in the tree below). The ‘XR’ in the Print Control column tells FRx to suppress the rollup to the summary level.
In the catalog, make sure you check the box ‘Use row formats and worksheet links from reporting tree’:
In the tree, your top level summary unit uses the second link (GL2). Your detail units use GL1. Column G, Acct Mask, is a bit unusual too, in that an account mask is specified for the summary unit. Any time you have a unit that’s pulling from the GL, it must have an account mask.
A bit more explanation on the row: Departmental Headcount account 9500 is going to rollup to the All Departments unit, just like normal. We don’t want Total Headcount to rollup, however, since it would overstate. It is NOT going to rollup due to the XR suppress rollup code. Instead, the summary unit gets its data for Total Headcount from the 9500-????-??? in the GL2 link.
If you have any situations like this, I’d suggest you experiment a bit to see what you can work out. This is admittedly not exactly elementary FRx. You can test with just a few reporting units, and when you think you have it, make sure your ‘All Departments’ unit ties back to current year income, then make sure your individual departments foot to ‘All Departments’. If there are too many to foot manually, export to Excel and tie out there, another good use of Excel with FRx. Good luck.
{ 2 comments… read them below or add one }
I’m trying use the full account code on a row, but I need to grab data across companies. FRx seems to only grab balances for the account codes I specify within the company currently being reported on and does not include balances for other companies on this row. We have the same account mask, etc across companies and I do not have any difficulties when using a tree with multiple companies. Any thoughts?
You’re headed the right way using the tree. It’s what you need to grab data across companies.