reporting-servicesssrs-tablixssrs-expression

SSRS Expression to Sum value IIF YearMonth = Parameter

I hope someone would be able to assist\help.

I have a Tablix that I'm trying to populate with three separate (summed) values (Current Month, Current Year and Previous Year) from one field based on a parameter. My parameter is set as yyyymm. My expression logic is as follows for each summed value:

Sum Current Month values

=SUM(Fields!Quantity.Value),IIF(Parameters!YearMonth.Value) = CDATE(Now()), "yyyyMM")

Sum Current Year values

=SUM(Fields!Quantity.Value),IIF(Parameters!YearMonth.Value) = CDATE(Now()), "yyyy")

Sum Previous Year values

=SUM(Fields!Quantity.Value),IIF(Parameters!YearMonth.Value) = CDATE(Now()), "yyyy")-1

I'm getting the following error when attempting the above expressions:

The Value expression for the textrun Quantity5.Paragraphs[0].TextRuns[0] contains an error: [BC30205] End of statement expected

Solution

  • As Harry pointed out, the IIF statement syntax is

    IIF([Expression to evalute], [Expression to return if true], [Expression to return if false])

    Also, I think you need to format the date you get back from now() so it matches the format of your parameter.

    So, taking your first expression it should be something like

    =SUM(
    IIF(Parameters!YearMonth.Value = FORMAT(Now(), "yyyyMM"), Fields!Quantity.Value, Nothing)
    )

    So starting at the inner expression, we compare the parameter to today's date formatted as yyyyMM. If the match the return the value of the Quantity field, if not return nothing. The outer SUM then just sums all these results.