How do I mix functions in complex SSRS expressions?

Question

I'm writing a report against a data repository that has null values within some of the columns. The problem is building expressions is as temperamental as a hormonal old lady and doesn't like my mixing of functions.

Here's an expression I've written that does not work if the data in the field is null/nothing:

=IIF(
    IsNumeric(Fields!ADataField.Value),
    RunningValue(
        IIF(
            DatePart("q", Fields!CreatedOn.Value) = "2",
            Fields!ADataField.Value,
            0
        ),
    Sum,
    Nothing
    ),
    Sum(0)
)

(Pseudocode) "If the data is valid and if the data was created in the second quarter of the year, add it to the overall Sum, otherwise, add zero to the sum."

Looks pretty straight forward. And the individual pieces of the expression work by themselves. IE: IsNumeric(), DatePart(), etc. But when I put them all together, the expression throws an error.

I've attempted about every permutation of what's shown above, all to no avail. Null values in Fields!ADataField.Value cause errors.

The specific error generated:

"The Value expression for the textrun ‘’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data."

Thoughts?

I bet you have nonnumeric data in "AdataField" even for some cases where CreatedOn is in quarter 2. — Jamie F, Dec 11 '12 at 15:50
Can you give us some sample data that we can try with this? Make sure that the sample data gives you the error as well. — Neil, Dec 11 '12 at 15:56
Sample data would be nothing more than an integer field that has null values mixed in with valid integers vs. zeros. — TheHolyTerrah, Dec 11 '12 at 16:00
I have changed my answer. It appears to be working for my sample set. Take a look at it and let me know if it solves your problem. — Neil, Dec 11 '12 at 16:13
Yessir. I did right after you made the edit. :( Join our chat on the subject: http://chat.stackoverflow.com/rooms/20957/discussion-between-gshenanigan-and-boydski — TheHolyTerrah, Dec 11 '12 at 18:43

Neil · Answer 1 · 2012-12-11T16:13:07.453

1

Try using this:

=SUM(IIF(ISNothing(Fields!ADataField.Value), 0, 
(IIF(IsNumeric(Fields!ADataField.Value), IIF(DatePart("q", Fields!CreatedOn.Value) = "2", Fields!ADataField.Value, 0), 0)
)))

edited Dec 11 '12 at 16:13

answered Dec 11 '12 at 15:33

Neil

911
7
25

See comment from previous answer: I've attempted many versions of IsNothing() in various places in the expression. Thanks though! And please lemmie know if you come up with anything that works. – TheHolyTerrah Dec 11 '12 at 15:50

score 1 · Answer 2 · answered Dec 11 '12 at 15:35

1

You could handle the NULL value occurring using:

Iif(Fields!ADataField.Value Is Nothing, 0, Fields!ADataField.Value)

That way you can replace any Null values with something else (0 in my example above). To put this into the context of your expression, it should look something like this:

=Iif(Fields!ADataField.Value Is Nothing,0,
    RunningValue(
        IIF(
            DatePart("q", Fields!CreatedOn.Value) = "2",
            Fields!ADataField.Value,
            0
        ),
    Sum,
    Nothing
    )
)

answered Dec 11 '12 at 15:35

GShenanigan

5,409
5
38
48

Tried that already. Tried every use of IsNothing() and "x Is Nothing" I could come up with. Still errors. :( – TheHolyTerrah Dec 11 '12 at 15:48
Do you know for sure that it's the NULL value causing it, or could it be something else? I take it the error message just says that it couldn't evaluate the expression? – GShenanigan Dec 11 '12 at 15:51
I have indeed confirmed it is the null values. – TheHolyTerrah Dec 11 '12 at 15:59
Have you tried without the RunningValue function, as @Neil suggests in his edit? I would personally take that sort of approach normally, rather than using RunningValue (just preference). – GShenanigan Dec 11 '12 at 16:17
I started out using Sum() and went to RunningValue() in an effort to make it work. – TheHolyTerrah Dec 11 '12 at 16:33
Can you please add an example of how you're trying to use this expression in your report? I.e. in a table, what are the groupings etc? – GShenanigan Dec 11 '12 at 16:47
I've actually factored this whole thing down to a table with a single row and column (one cell) attempting to get a single quarter's worth of data to properly show. I've attempted with groups, without groups, with and without aggregates, etc. It's driving me nutz. The closest I've gotten anything to work works without aggregation or taking the quarter into account: =IIF(IsNumeric(Fields!ADataField.Value), Fields!ADataField.Value, 0) – TheHolyTerrah Dec 11 '12 at 17:07
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/20957/discussion-between-gshenanigan-and-boydski) – GShenanigan Dec 11 '12 at 17:21

Jamie F · Answer 3 · 2012-12-11T17:02:33.113

The VB in expressions in SSRS does not do short circuit evaluation. That is, all three parameters for an IIF statement are evaluated and if any of them throws an exceptions (#Error) then the entire expression throws an error.)

So no matter what the value for ADataField is, this part will be evaluated:

RunningValue(
        IIF(
            DatePart("q", Fields!CreatedOn.Value) = "2",
            Fields!ADataField.Value,
            0
        ),
    Sum,
    Nothing
    ),

This will happen even if the code has no way of returning the result such as when IsNumeric(Fields!ADataField.Value) is false.

So you are always going to be executing the RunningValue function, If there is ever non-numeric data in ADataField when the CreatedOn field is in Quarter 2, then this will all generate an error for every call.

Try this as an alternative:

=IIF(
    IsNumeric(Fields!ADataField.Value),
    RunningValue(
        IIF(
            DatePart("q", Fields!CreatedOn.Value) = "2"
              AND IsNumeric(Fields!ADataField.Value),
            Fields!ADataField.Value,
            0
        ),
    Sum,
    Nothing
    ),
    Sum(0)
)

Right. And I've kept that in mind as I move forward with various permutations of the expression. What I show above is only one version of many I've attempted. Still working it... — TheHolyTerrah, Dec 11 '12 at 16:01
I've added a proposed solution. It has a seemingly redundant clause in the inner IIF that should clean things up. — Jamie F, Dec 11 '12 at 17:03

score 0 · Accepted Answer · answered Dec 11 '12 at 18:57

Ok everyone. I figured out the solution. Thanks much for all the great suggestions. Most of them I had already tried before I decided to post here. So the sanity check was of great value!

For whatever illogical reason, what fixed this was wrapping the field in CInt() even after the field was confirmed already to have been numeric. It would not allow a Sum() to run against the field without it. Here's the code that finally took:

=Sum(
    IIF(
        IsNumeric(Fields!ADataField.Value) And
        DatePart("q", Fields!createdon.Value) = "1",
        CInt(Fields!ADataField.Value),
        0
    )
)

Any and all other permutations that we've all tried here, including those with RunningValue() all work after having wrapped the "true" portion of IIF() with CInt().

Just please don't axe me why this works and yet it doesn't without it. Anything but the most elementary expressions always seem to be extremely fickle.

Thanks again everyone. Answered question goes to GShenanigan for going the extra mile during chat. Wish I could give it to everyone though. You guys are awesome.

How do I mix functions in complex SSRS expressions?

4 Answers4