Report Builder 3.0: #Error returns when multiple runningvalues are in an IIF statement RRS feed

  • Question

  • I am an amateur at report builder but this is what I'm trying to do.  I want to be able to select 2 years, return the amounts and sum them by a monthly basis, and find the percent difference between the sums.  The issue I'm having is with the percent differences. Basically, what I'm doing is doing an iif statement where it uses a running value for the amounts based on both years chosen by the user and divides them.  The formula does work but has one major issue.  Only one set of values will work.  Any other values will return #error for the entire row.  For Example:

    =IIF(Parameters!YOA_1.value = 2013 and Parameters!YOA_2.Value= 2014, RunningValue(Fields!ID2014.Value, Sum, "DataSet1") / RunningValue(Fields!ID2013.Value, Sum, "DataSet1") - 1, NA)

    Using this year combination, all data comes back correctly.  Then I try to add another year combination and this is the new expression:

    =IIF(Parameters!YOA_1.value = 2013 and Parameters!YOA_2.Value= 2014, RunningValue(Fields!ID2014.Value, Sum, "DataSet1") / RunningValue(Fields!ID2013.Value, Sum, "DataSet1") - 1, IIF(Parameters!YOA_1.Value=2012 and Parameters!YOA_2.Value=2013, RunningValue(Fields!ID2013.Value, Sum, "DataSet1") / RunningValue(Fields!ID2012.Value, Sum, "DataSet1") - 1, "NA"))

    With this new expression, if you enter 2013 and 2014, you get #error for the entire row.  But for some reason if you enter 2012 and 2013, all data comes back correctly.  Can anyone help me out with this? 

    • Edited by Andrew00000 Friday, November 21, 2014 8:22 PM
    Thursday, November 20, 2014 5:53 PM

All replies

  • Hi Andrew00000,

    Based on my understanding, there are three columns and two parameters in your report, when using first expression, all expected values can return. However, using second expression, only one set of values can work.

    As we tested in our environment, both of those two expressions can work fine in Report Builder 3.0. In your scenario, the issue may caused by selecting “Allow multiple values” option in parameter. When allowing multiple values, the parameter is an array, so operator “=” can’t be used for parameter equal to an integer. Since Report Builder 3.0 can’t display warning message, we recommend you save the report as .rdl file and open it in BIDS or SSDT, then run the report and check the warning message to find root cause. If possible, please provide the warning message and report design for our analysis. Regarding the test results of second expression in our environment, please refer to screenshots below:

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu

    Tuesday, November 25, 2014 8:29 AM