How do I mix functions in complex SSRS expressions?

Posted by Boydski on Stack Overflow See other posts from Stack Overflow or by Boydski
Published on 2012-12-11T15:08:41Z Indexed on 2012/12/11 17:03 UTC
Read the original article Hit count: 129

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.

Thoughts?

© Stack Overflow or respective owner

Related posts about reporting-services

Related posts about ssrs-2008