IIF (lots of IIF)

Mrfra62

Hi everybody, I am new to this topic and the use of fastreport. We are using fastreport 4.13.7. I am trying to make a formula which works in Excel but in Fast report return error.
In Excel (2013) the formule would be :

(IF(SALDO=0;0;IF(AND(SALDO>0;SALDO<266,69);40;IF(AND(SALDO>=266,69;SALDO<=2500);15%*SALDO;IF(AND(SALDO>=2500,01;SALDO<=5000);(15%*2500)+(10%*(SALDO-2500));IF(AND(SALDO>=5000,01;SALDO<=10000);(15%*2500)+(10%*2500)+(5%*(SALDO-5000));IF(AND(SALDO>=10000,01;SALDO<=200000);(15%*2500)+(10%*2500)+(5%*5000)+(1%*(SALDO-10000));IF(AND(SALDO>=200000,01;SALDO<=1000000);(15%*2500)+(10%*2500)+(5%*5000)+(1%*190000)+(0,5%*(SALDO-200000));IF(SALDO>1000000;6775)))))))))

This formula works.
Now I tried to convert the formula to FastReport like this:

IIF(Saldo=0; 0,0; IIF(Saldo<=266,69; 40, IIF(Saldo<=2500; 0,15*Saldo; IIF(Saldo<=5000; 375 + (0,10*(Saldo-2500)); IIF(Saldo<=10000; 625 + (0,05*(Saldo-5000)); IIF(Saldo<=200000; 875 + (0,01*(Saldo-10000)); IIF(Saldo<=1000000; 975 + (0,005 *(Saldo-200000)); 6775))))))

When making the report i get the following error:

gordk

the first error is the wrong type of brace
the iif in a memo should be enclosed in [ ] braces
ie
[IIF(expression to evaluate,true value,false value)]

i suggest you try using frs builtin expression builder to help you form your expression

technisoft

I have no faith in the memo's IFF() expression calculator, nor its builder.
It may work for simple statements but for such a complex formulation I would never consider it putting there.

Move the code into the script and handle it there. This will also allow you to structure the code in a readable manner and debug it efficiently.

I think that using a decimal comma instead of a decimal point is another source of error.

Petter S.

Hi.

The message says: " ')' expected".
As far as I can count you have 15 left braces, and just 14 right braces. So I would guess that one more ')' should do the trick.

Mrfra62

@gordk
did try using the expression builder, never made the formula to work
@technisoft
don't know how to use code or script or to handle it. even don't know where to find that tool in the report builder.
@Petter S.
you're right, it was one bracket left. now I dont get error message. but formula doesn't give right outcome. if you would put the excel formula in a excel sheet and use as Saldo= 900, then excel returns the right value of 135 but the fast report expression returns 40
suppose saldo= 182.83, excel returns 40 and fast report returns 775
excel returns the right value.....
whats wrong with my formula ?
why arent the values the same ?

Petter S.

Hi.

A couple of things I didn't notice at first:
- In my FR I must use ',' between statements, not ';' (in your exemple you have one ',' in the second IIF...), and '.' as decimal point. Maybe this can be set up somewhere - i don't know...
- if 'Saldo' is av variable, then the syntax should be '<Saldo>'
- if 'Saldo' comes from a database/query the syntax should be <MyDb."Saldo">

I've tested this one:

[IIF(<Saldo>=0, 0.0, IIF(<Saldo><=266.69, 40, IIF(<Saldo><=2500, 0.15*<Saldo>, IIF(<Saldo><=5000, 375 + (0.10*(<Saldo>-2500)), IIF(<Saldo><=10000, 625 + (0.05*(<Saldo>-5000)), IIF(<Saldo><=200000, 875 + (0.01*(<Saldo>-10000)), IIF(<Saldo><=1000000, 975 + (0.005 *(<Saldo>-200000)), 6775)))))))]

where <Saldo> is a variable, and I seems to get the right output (900 -> 135, 182.83 -> 40)

Mrfra62

Thanks for your reply. Saldo is indeed a variable. So I put it between brackets like this: <Saldo>. This returned consequently the value: 40. The I realized that I had to calculate this formula over the sum of saldo so I put SUM(Saldo) between brackets like: <SUM(Saldo)>.
The fomula would the be:
[IIF(<SUM(Saldo)>=0, 0.0, IIF(<SUM(Saldo)><=266.69, 40, IIF(<SUM(Saldo)><=2500, 0.15*<SUM(Saldo)>, IIF(<SUM(Saldo)><=5000, 375 + (0.10*(<SUM(Saldo)>-2500)), IIF(<SUM(Saldo)><=10000, 625 + (0.05*(<SUM(Saldo)>-5000)), IIF(<SUM(Saldo)><=200000, 875 + (0.01*(<SUM(Saldo)>-10000)), IIF(<SUM(Saldo)><=1000000, 975 + (0.005 *(<SUM(Saldo)>-200000)), 6775)))))))]
How ever this doesnt return the right valua.
Then I looked at numberformats. Using "Number / 1,234.00 / %2.2n / , " the formating of the outcome is right and when i use $1,234.00 instead of 1,234.00 the outcome adds the euro sign (??????¬). But sadly the valua of the outcome isnt right.
I dont get it at all. Use the same formula you get the desired outcome, and me .... I get the wrong outcome. Frustrating.
Maybe it is something in the (regional) settings. However, I use the fast report in Dutch user interface and when I press F1 ... up comes help text in (US) English.
I tied to use the variant [IIF((SUM(Saldo))=0, 0.0, IIF((SUM(Saldo))<=266.69, 40, IIF((SUM(Saldo))<=2500, 0.15*(SUM(Saldo)), IIF((SUM(Saldo))<=5000, 375 + (0.10*((SUM(Saldo))-2500)), IIF((SUM(Saldo))<=10000, 625 + (0.05*((SUM(Saldo))-5000)), IIF((SUM(Saldo))<=200000, 875 + (0.01*((SUM(Saldo))-10000)), IIF((SUM(Saldo))<=1000000, 975 + (0.005 *((SUM(Saldo))-200000)), 6775)))))))] instead of <> i use ()...this formula return consequently the value 40.
Using the variant [IIF([SUM(Saldo)]=0, 0.0, IIF([SUM(Saldo)]<=266.69, 40, IIF([SUM(Saldo)]<=2500, 0.15*[SUM(Saldo)], IIF([SUM(Saldo)]<=5000, 375 + (0.10*([SUM(Saldo)]-2500)), IIF([SUM(Saldo)]<=10000, 625 + (0.05*([SUM(Saldo)]-5000)), IIF([SUM(Saldo)]<=200000, 875 + (0.01*([SUM(Saldo)]-10000)), IIF([SUM(Saldo)]<=1000000, 975 + (0.005 *([SUM(Saldo)]-200000)), 6775)))))))] instead of <> i use []... i get an error: Invalid variant operation

I really dont get: why the value outcome is consequently 40 while I use the formula like you suggested.

Could it maybe be that saldo is part of Masterdata 1 and Sum(Saldo) is in the Group footer 1 ? While the fomula is part of Group header 1

Maybe you have some suggestions or tips ?

Mrfra62

edit to my last reply:

I figured it out.
Formula works just fine.
But after I placed it in the same part of the report, the outcome was correct.
So I placed the formula in the group footer one where the Sum(Saldo) variable was aswell.
Then it worked.

Thanks for putting me on the right track.