SSRS Nested IIF statements in expression throwing error-CodePudding

I keep getting an error [, is invalid. InvalidSyntax] when deploying this report in Visual Studio. I can't find the incorrect [ , ], also not sure if the bracets are positioned correctly. I am pretty new to SSRS, if someone could help me out here it would be very much appreciated.

Thank you in advanced.

Short description of what I would like to achieve.

If the condition below is met

=iif((First(Fields!ShowCut.Value, "SalesDataSet") = "No",

Then run this IIF statement below

iif((Fields!CutSize1.Value = "C1" or Fields!CutSize1.Value = "C2", Space(0), Iif(Fields!SalesUnitTxt.Value<>Fields!PriceUnitId.Value,FormatNumberCulture(CStr(Fields!PriceQty.Value), Parameters!AX_RenderingCulture.Value) vbCRLF FormatNumberCulture(CStr(Fields!Qty.Value), Parameters!AX_RenderingCulture.Value), FormatNumberCulture(CStr(Fields!PriceQty.Value), Parameters!AX_RenderingCulture.Value)),

Else run this IIF statement below

iif((Fields!CutSize.Value = "C2",FormatNumberCulture(CStr(Fields!PriceQty.Value),Parameters!AX_RenderingCulture.Value), Iif(Fields!SalesUnitTxt.Value<>Fields!PriceUnitId.Value,FormatNumberCulture(CStr(Fields!PriceQty.Value), Parameters!AX_RenderingCulture.Value) vbCRLF FormatNumberCulture(CStr(Fields!Qty.Value), Parameters!AX_RenderingCulture.Value), FormatNumberCulture(CStr(Fields!PriceQty.Value), Parameters!AX_RenderingCulture.Value)) )

CodePudding user response：

After some trail and error I found the solution already.

=iif(First(Fields!ShowCut.Value, "SalesDataSet") = "No",

iif(Fields!CutSize1.Value = "C1" or Fields!CutSize1.Value = "C2", Space(0), Iif(Fields!SalesUnitTxt.Value<>Fields!PriceUnitId.Value,FormatNumberCulture(CStr(Fields!PriceQty.Value), Parameters!AX_RenderingCulture.Value) vbCRLF FormatNumberCulture(CStr(Fields!Qty.Value), Parameters!AX_RenderingCulture.Value), FormatNumberCulture(CStr(Fields!PriceQty.Value), Parameters!AX_RenderingCulture.Value))),

To many brackets where placed and also mispositioned.

CodePudding user response：

You still have a set of unnecessary parenthesis and the formatting doesn't help you figure out what it's doing. Adding a new line for each condition of the IIF and indenting the conditions can make it easier to troubleshoot.

=IIF(First(Fields!ShowCut.Value, "SalesDataSet") = "No",
    IIF(Fields!CutSize1.Value = "C1" or Fields!CutSize1.Value = "C2",  
        Space(0), 
        IIF(Fields!SalesUnitTxt.Value<>Fields!PriceUnitId.Value,
            FormatNumberCulture(CStr(Fields!PriceQty.Value), Parameters!AX_RenderingCulture.Value)   vbCRLF   FormatNumberCulture(CStr(Fields!Qty.Value), Parameters!AX_RenderingCulture.Value),
            FormatNumberCulture(CStr(Fields!PriceQty.Value), Parameters!AX_RenderingCulture.Value)
            )
        ),
    IIF(Fields!CutSize.Value = "C2", 
        FormatNumberCulture(CStr(Fields!PriceQty.Value),Parameters!AX_RenderingCulture.Value), 
        IIF(Fields!SalesUnitTxt.Value<>Fields!PriceUnitId.Value,
            FormatNumberCulture(CStr(Fields!PriceQty.Value), Parameters!AX_RenderingCulture.Value)   vbCRLF   FormatNumberCulture(CStr(Fields!Qty.Value), Parameters!AX_RenderingCulture.Value), FormatNumberCulture(CStr(Fields!PriceQty.Value), Parameters!AX_RenderingCulture.Value)
            )
        )
    )