How do I mix functions in complex SSRS expressions?
- by Boydski
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?
