IIF (lots of IIF)

edited January 2014 in FastReport 4.0
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:

Comments

  • gordkgordk St.Catherines On. Canada.
    edited 6:16PM
    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
  • edited January 2014
    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.
  • edited 6:16PM
    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.
  • edited 6:16PM
    @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 ?
  • edited 6:16PM
    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)
  • edited February 2014
    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 ?
  • edited 6:16PM
    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.

    >

Leave a Comment

Rich Text Editor. To edit a paragraph's style, hit tab to get to the paragraph menu. From there you will be able to pick one style. Nothing defaults to paragraph. An inline formatting menu will show up when you select text. Hit tab to get into that menu. Some elements, such as rich link embeds, images, loading indicators, and error messages may get inserted into the editor. You may navigate to these using the arrow keys inside of the editor and delete them with the delete or backspace key.