I am using SSRS 2016 / Report Builder and I am trying to get a nested IIF expression to SUM number of minutes spent in one particular Room (P241). There are some other conditions to deal with as well, which complicate matters.

I am able to successfully sum total minutes for ALL Rooms using the following expression, but I can't seem to come up with an expression to separate out the rooms:

Expression That Calculates Total Minutes Spent in ALL Rooms (works)

=Sum(IIF(IsNothing(Fields!ServiceEndDate.Value),      
        IIF((Parameters!EffectiveDateTime.Value <= Fields!EffectiveDateTime.Value), 
              DATEDIFF("n", Fields!EffectiveDateTime.Value, DATEADD("d", 1, Parameters!EffectiveDateTime2.Value)), 
              DATEDIFF("n", Parameters!EffectiveDateTime.Value, DATEADD("d", 1, Parameters!EffectiveDateTime2.Value))), 
        IIF((Parameters!EffectiveDateTime.Value <= Fields!EffectiveDateTime.Value), 
              DATEDIFF("n", Fields!EffectiveDateTime.Value, Fields!ServiceEndDate.Value), 
              DATEDIFF("n", Parameters!EffectiveDateTime.Value, Fields!ServiceEndDate.Value))))

Data Table

Useful info: I have two parameters @EffectiveDateTime and @EffectiveDateTime2 --> the user running the report uses these to specify a beginning and end dates from which to calculate the number of minutes.

Attempt #1 Based on the working expression that totals minutes for all rooms. I added 'AND Fields!Room.Value = "P241"' into the IIF conditions. Result: ended up with total minutes of ALL ROOMS. I concluded (right or wrong) that the issue might be because there was no way to account for rooms that were NOT P241

Expression that calculates total minutes for ALL ROOMS

=Sum(
    IIF(IsNothing(Fields!ServiceEndDate.Value),
        IIF((Parameters!EffectiveDateTime.Value <= Fields!EffectiveDateTime.Value AND Fields!Room.Value = "P241"), 
            DATEDIFF("n", Fields!EffectiveDateTime.Value, DATEADD("d", 1, Parameters!EffectiveDateTime2.Value)), 
            DATEDIFF("n", Parameters!EffectiveDateTime.Value, DATEADD("d", 1, Parameters!EffectiveDateTime2.Value))),
        IIF((Parameters!EffectiveDateTime.Value <= Fields!EffectiveDateTime.Value AND Fields!Room.Value = "P241"), 
            DATEDIFF("n", Fields!EffectiveDateTime.Value, Fields!ServiceEndDate.Value),
            DATEDIFF("n", Parameters!EffectiveDateTime.Value, Fields!ServiceEndDate.Value))))

Attempt #2 My second attempt was based on the idea that I needed an "else" option for the room selection to work. RESULT: I triple-checked commas and brackets, but kept failing with ERROR: "The Value expression for the textrun ‘Textbox275.Paragraphs[0].TextRuns[0]’ contains an error: [BC30516] Overload resolution failed because no accessible 'IIf' accepts this number of arguments." The expression below has "" as the else part, but I also tried 0 and Nothing after scouring forums for suggestions

=IIF(Fields!Room.Value = "P241"),
    SUM(
        IIF((IsNothing(Fields!ServiceEndDate.Value)),
            IIF((Parameters!EffectiveDateTime.Value <= Fields!EffectiveDateTime.Value),
                DATEDIFF("n", Fields!EffectiveDateTime.Value,DATEADD("d",1,Parameters!EffectiveDateTime2.Value)),
                DATEDIFF("n",Parameters!EffectiveDateTime.Value,DATEADD("d",1,Parameters!EffectiveDateTime2.Value))),
            IIF((Parameters!EffectiveDateTime.Value <= Fields!EffectiveDateTime.Value),
                DATEDIFF("n",Fields!EffectiveDateTime.Value,Fields!ServiceEndDate.Value),
                DATEDIFF("n",Parameters!EffectiveDateTime.Value,Fields!ServiceEndDate.Value)))
            ),""

Attempt 3 Tried rearranging IIF/SUM and it really hated that. Result: ERROR:The Value expression for the textrun ‘Textbox275.Paragraphs[0].TextRuns[0]’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.

=SUM(
    IIF(Fields!Room.Value = "P241"),
        IIF((IsNothing(Fields!ServiceEndDate.Value)),
            IIF((Parameters!EffectiveDateTime.Value <= Fields!EffectiveDateTime.Value),
                DATEDIFF("n", Fields!EffectiveDateTime.Value,DATEADD("d",1,Parameters!EffectiveDateTime2.Value)),
                DATEDIFF("n",Parameters!EffectiveDateTime.Value,DATEADD("d",1,Parameters!EffectiveDateTime2.Value))),
            IIF((Parameters!EffectiveDateTime.Value <= Fields!EffectiveDateTime.Value),
                DATEDIFF("n",Fields!EffectiveDateTime.Value,Fields!ServiceEndDate.Value),
                DATEDIFF("n",Parameters!EffectiveDateTime.Value,Fields!ServiceEndDate.Value)))
            ),0)

CodePudding user response：

I believe #3 is the correct approach if you only want to sum values for Room.Value = "P241". Your problem is that your parenthesis are misplaced, starting with the one immediately after your room test.

Below is a slightly reformatted version with adjusted parenthesis placement.

=SUM(
    IIF(Fields!Room.Value = "P241",
        IIF((IsNothing(Fields!ServiceEndDate.Value)),
            IIF((Parameters!EffectiveDateTime.Value <= Fields!EffectiveDateTime.Value),
                DATEDIFF("n", Fields!EffectiveDateTime.Value,DATEADD("d",1,Parameters!EffectiveDateTime2.Value)),
                DATEDIFF("n",Parameters!EffectiveDateTime.Value,DATEADD("d",1,Parameters!EffectiveDateTime2.Value))
            ), -- IIF #3
            IIF((Parameters!EffectiveDateTime.Value <= Fields!EffectiveDateTime.Value),
                DATEDIFF("n",Fields!EffectiveDateTime.Value,Fields!ServiceEndDate.Value),
                DATEDIFF("n",Parameters!EffectiveDateTime.Value,Fields!ServiceEndDate.Value)
            ) -- IIF #4
        ), -- IIF #2
        0
    ) -- IIF #1
) -- Sum

In short, I believe you want to change:

=SUM(<original calculation>)

to:

=SUM(IIF(Fields!Room.Value = "P241", <original calculation>, 0))

I suggest that you paste this into an editor (such as Notepad ) that supports highlighting of matching parenthesis. That will help you review the placement.