Using IF THEN Calculations in FRx

Here’s another not-so-obvious trick with calculations in FRx. This can be used for various things, but I’ll use the example of multiplying by a currency rate.

Use IF @UNIT() calculations in the row format to define the rate for each unit, then just use a CALC column to multiply by your rate row. This is a bit harder to explain, but you basically end up with something like this in the row format:

MYRATE My exchange rate CAL 1.5678 “0.000000″ NP
NO_RATE No conversion CAL 1 NP
11 Company1 CAL IF @UNIT(COMPANY1) THEN @MYRATE ELSE @12 NP
12 Company2 CAL IF @UNIT(COMPANY2) THEN @MYRATE ELSE @13 NP
13 Company3 CAL IF @UNIT(COMPANY3) THEN @MYRATE ELSE @14 NP
14 Company4 CAL IF @UNIT(COMPANY4) THEN @MYRATE ELSE @15 NP
15 Company5 CAL IF @UNIT(COMPANY5) THEN @NO_RATE ELSE @16 NP
16 Company6 CAL IF @UNIT(COMPANY6) THEN @NO_RATE NP

You then have one GL column and one CALC column in the column layout. Your CALC column would use a formula of B*B11. When you generate the report, FRx will start at row 11 and then fall through each CAL row until it hits the right one.

You can combine this with XR and other functionality to do some pretty cool stuff. There are probably some good, creative uses of this without @UNIT(), too.

I don’t get any of this please help!

Is there a way to get my report to only display lines that meet a certain IF criteria. For example I am trying to display only variances over $5000. I’ve used one column to calculate the absolute value for each variance, but I only want to see the rows that exceed 5000. Thank you in advance for any help.

Does anyone know if you can do an: IF/THEN/IF/ THEN/ ELSE?

Hey thanks Jeff—I may have a use for that tomorrow!

I also do this in a row to make an ‘out of balance’ row calc to -0- so it doesn’t print due to rounding:
900 Out of balance TOT 830-800 NP
930 Out of balance CAL IF @900 < -4 or @900 >4 THEN @900 ELSE 0

Not exactly the same thing as a nested if/then but it works great in this instance. Jan

I am trying to use the IF/THEN statement to create a p&L that shows the revenue and salaries for one department containg professional staff separately at the top of the report. Below that information is another salary row for departments for non-professional staff. I am using the @unit statement in the row format and if the unit contains is equal to the professional staff department then FRX branches to a row that contains the accounts in the LINK to GL column, including the department segment as DEP-????-ACCT-????-????. The Else statement is equal to “0″. That is not working because the summary p&L does not include the rollup. If I include an else statement that branches to list the natural accounts only (as ACCT????) minus DEP-????-ACCT-????-???? I get duplicate values. How can I fix that so only one department and the summary rollup contains the rows for the professional staff department and the non-professional staff departments show the correct amounts? I played around with the tree and row format and I can’t get that work either because the same natural account relating to non-professional staff also are used for professional staff and we need to report by department on the reporting tree. Please help! Thanks.

I have been trying all day to get the ABS function to work in an IF CALC column. Here are the statements I have tried.

IF ABS(G-H)>2500 THEN G-H This is how it is in the book, but doesn’t work
IF (ABS(G-H))>2500 THEN G-H
IF ABS(G-H) > 2500 THEN G-H
IF ABS (G-H)>2500 THEN G-H

None work. What is the correct syntax, spacing, round or square brackets to get this working? If I can get the ABS to work, then hopefully, I will be able to build upon the variances to include % as well (Jeff’s 3/2009 posting)

Can this calculation be used on rows that are linked to the General Ledger?

Or do I need to insert another row referencing the GL balance to use the IF THEN calculation and mark the row linked to the General Ledger not to print?

Thanks,
Lindsay

I might sound to be out of the topic, is there anywhere on the web one can get free *.pdf guide of Frx Report Designer with formulas and guides on how to do some of the reports. I need to take the certification exam but I need like a strong material that i can use. Thank you

Jan,

It’s me again. Love the reference guides but I am having a bit of a problem with using the if then else calculationf for my intercompany accounts. I have several intercompany accounts in each of my companies. I pulled the balances of each in a separate section in my row definitions and now I am trying to use the if then cal to pull the correct balances in the receivable/payable sections on my balance sheet. I get it to work for one company but can’t figure out how to add multiple if then statements on the same row. Is this possible? Or do I need to have separate rows that do not print or show on the balance sheet that sum up to the proper line?

Great thread! Here’s another scenario I haven’t seen discussed. What about displaying or suppressing a row based on the date of a column. For example, a rolling P&L that uses a non-financial account value, but that value didn’t exist until a particular month. I didn’t see any discussion where the true/false statement tested on all columns in the Column Layout. Thanks for any feedback!

Been reading up on if then else statements and I have a question. I have a column that is a variance against budget and another for last year. Let’s say A is actual, B is budget and C is variance. For D I want the percentage variance so I’ve used if B>0 then C/B else 0. Works great, problem is formatting in the rows is overriding the display zeros as blanks on the print out. Can I fix this or can I point the else statement to display N/A in text?