Search code examples
reporting-servicesssrs-tablix

SSRS Overload resolution failed because no accessible 'IIf' accepts this number of arguments

I wrote a formula using the values of other textboxes in a textbox, but the system does not accept it. The formula is as below

=IIF(ReportItems!Textbox66.Value>=1,IIF(ReportItems!Textbox257.Value>=500,CInt((ReportItems!Textbox66.Value*100-100)/5),IIF(CInt((ReportItems!Textbox66.Value*100-100)/5)>=4,4,CInt((ReportItems!Textbox66.Value*100-100)/5))),IIF(ReportItems!Textbox257.Value<500,IIF(CInt((100-ReportItems!Textbox66.Value*100)/5)>-4,-4,IIF((100-ReportItems!Textbox66.Value*100)/5*(-1))),(CInt((100-ReportItems!Textbox66.Value*100)/5*(-1)))))
Solution

  • It looks like there's an IIF statement with only one of the three arguments.

    IIF((100-ReportItems!Textbox66.Value*100)/5*(-1))

    It should have an argument for the value when the IIF expression is True of False.

    IIF((100-ReportItems!Textbox66.Value * 100) / 5 * (-1), ?, ?)

    It's easier to find if you use returns and tabs to break up the expression. A few spaces don't hurt either.

    =IIF(ReportItems!Textbox66.Value >= 1,
    IIF(ReportItems!Textbox257.Value >= 500,
        CInt((ReportItems!Textbox66.Value * 100 - 100) / 5),
            IIF(CInt((ReportItems!Textbox66.Value * 100 - 100) / 5) >= 4,
                4,
                CInt((ReportItems!Textbox66.Value * 100 - 100) / 5)
                )
        ),
    IIF(ReportItems!Textbox257.Value<500,
        IIF(CInt((100 - ReportItems!Textbox66.Value * 100) / 5) > -4,
            -4,
            IIF((100 - ReportItems!Textbox66.Value * 100) / 5 * (-1), ?, ?)
            ),
        CInt((100 - ReportItems!Textbox66.Value * 100) / 5 * (-1))
        )
    )

    It might be a bit more tedious to use a SWITCH but it may be easier to read and make it work the way you want.

    =SWITCH(ReportItems!Textbox66.Value >= 1 AND ReportItems!Textbox257.Value >= 500, CInt((ReportItems!Textbox66.Value * 100 - 100) / 5),
        ReportItems!Textbox66.Value >= 1 AND CInt((ReportItems!Textbox66.Value * 100 - 100) / 5) >= 4, 4,
        ReportItems!Textbox66.Value >= 1, CInt((ReportItems!Textbox66.Value * 100 - 100) / 5), 
        ReportItems!Textbox257.Value < 500 AND CInt((100 - ReportItems!Textbox66.Value * 100) / 5) > -4,
            -4,  
        ReportItems!Textbox257.Value < 500 AND (100 - ReportItems!Textbox66.Value * 100) / 5 * (-1) = ?????, ?????,
        ReportItems!Textbox257.Value < 500, CInt((100 - ReportItems!Textbox66.Value * 100) / 5 * (-1))
)