Need to calculate an individual cell in FRx? You can. The technique is called column placement.
I’ve been focused on the future of FRx for so long that it’s been a while since I posted a How To. By special request from the Wednesday presentation at Convergence (sorry again for the dreadful visual capabilities of the room we were in!), here’s how to do column placement.
Quite often in FRx you simply need to put the value in one column into a different column. This is easy in excel with by just entering an ‘=’ with the cell reference. It’s not quite as easy in FRx but it’s still fairly straightforward.
It’s easier if you think of the intersection of each row and column as having a cell reference, just as in Excel. So the reference for FRx column B and row code 1410 would be simply B1410.
But let’s look at an example. This is from the cashflow example in the FRxDemo spec set. In it, Column E calculates the YTD change in all accounts (because that’s what you need for cashflow). Yet for Cash at the Beginning of Period, we don’t want the change, we want the actual Beginning Balance. The YTD Beginning Balance is contained in column C. So we’ll use column placement to set the value in column E equal to the value in column C:
Row 1360 contains the link to the GL.
Row 1390 is the column placement row:
- Use a CAL format code.
- FRx column B is set equal to its own value for row 1360, in this case the beginning balance (due to the row modifier /BB) for GL accounts 1100 to 1110 +1600. The formula is B=B1360.
- You’d get the same value in column B for row 1360 if it weren’t a nonprint.
- FRx columns C and D are nonprinting so we don’t need to assign values in this particular case.
- FRx column E is set equal to column C’s beginning balance for the GL accounts in row 1360. The formula is E=C1360, meaning put column C’s balance into column E.
One last item: for column placement to work, you must set the calculation priority in the catalog to Calculate Columns First. What this means is that columns are calculated first, then the rows are calculated, so row calculations are dominant.
BTW I don’t do cashflows like this, but this was the simplest example I could dig up quickly.
This is a simple example but it can get complex quickly. This last screenshot shows a LOT going on in this column placement and it’s waaay too complex to boot (there are several ways to simplify this), but it does show that calculations can be done with column placement. Don’t try this at home!
Don’t forget the catalog setting: Report Options tab, Advanced, Calculation Priority: Calculate Columns First.
{ 7 comments… read them below or add one }
Hi Jan,
Good post. I just wanted to clarify one thing …
You only need to change the calculation priority to columns first if there’s an intersection of row and column calculations. In the example above, if you were setting column D=C1360, you would not need to change the calculation priority because there’s only one calculation. However, because you’re setting column E’s value and column E is a CALC column, FRx needs to know which of the conflicting calculations should take priority (i.e., which should happen first). It’s a little strange, but that’s the way it works.
Hope that helps.
Jeff
Excellent point, thanks Jeff! You’re exactly right (of course!): the calculation priority is only necessary in reports where a CAL in the row intersects with a CALC in the column.
FRx has to know which calculation should prevail, and defaults to the CALC in the column (meaning calculation priority is set to ‘calculate rows first’).
Makes sense that if D=C1360 was the only calculation in this example, you wouldn’t have to change the default priority. Thanks for pointing that out…Jan
I’ve used the formula in FRx but have yet to find a solution in Management Reporter that will return the same results. Seems MS forgot this one.
Jan,
I am trying to display revenue and expense columns by program in FRx. so our row description will have our programs and I would like to insert 2 seperate GL columns one for the revenue accounts and the other for the Expense accounts.
Ex=g
Program A Revenue A Expense A Profit A
Program B Revenue B Expense B Profit B
Program C Revenue C Expense C Profit C
How do we get this to come out correctly onthe catalog. The key is to have a revenue account and expense in one line (row).
Thanks!
The short answer is to use the Account Filter cell in the columns. The best answer depends on your account structure: I would probably simplify and use just the programs in the row, but that’s an advanced design that would likely require a bit of consulting to set up correctly. Use the contact form if you want to go that route. I consult with end users and consultants alike! Cheers…Jan
Jan -
This works great but one thing that has always stumped me is getting a GL column to calculate when the column is set to NP. For example, when creating a trailing twelve month calculation (revenue or EBITDA) you have columns for the past 23 months and create calc columns to add each month trailing twelve numbers. The calc columns can have NP and it hides the column and calculate fine as long as the first 11 months don’t have NP on the print control. Show it shows all 23 months and the TTM calc is perfect for the last twelve months. As soon as you add NP to the first 11 months it ignores them in the CALC columns. Have you found a way around this besides showing all columns and dumping to excel to hide the first 11 months?
Hi John…this is by design and works brilliantly for a forecast where depending on the report generation period, you get either an actual or budget column. You can just specify B to X and it picks up just the printing columns. To include a NP column in a calculation, you have to specify it by name instead of including it in a range. For instance, b+c+d+e+f will include all those columns regardless of whether they print or not.
BTW, I do trailing 12 month calculations in the ROW, using row modifiers. Way easier than trying to get it in the column, I think. Cheers! Jan
{ 1 trackback }