CODEWITHSUNDEEP
reporting-services
DJGray
DJGray

Reputation: 512

SSRS 2005 Deal with Null return value - 0 rather than #Error

As an SSRS 2005 neophyte, I'm unclear how to accomplish this, and have been unsuccessful incorporating the suggestions of other answers offered here into my specific situation.

Scenario: I'm calculating the average age of employees by department, gender, etc. In some cases there is nothing to calculate, and in such cases I would prefer to return a zero. In its current form, my expression returns #Error.

The expression:

=Int(Avg(IIF(Fields!CustGender.Value = "F", CDec(Fields!Age.Value),Nothing),"ReportDataset"))

What is the proper syntax/logic to get that expression to return 0 of there are no Female employees in that department? If I change that "Nothing" to a zero, won't that throw off the Avg function's calculation?

Upvotes: 0

Views: 693

Answers (1)

Jamie F
Jamie F

Reputation: 23789

I haven't tried this, but I think if you manually calculate the average it should work. Sum / Count:

=Int( SUM(
    IIF(
      Fields!CustGender.Value = "F",
      CDec(Fields!Age.Value),
      0
    ),"ReportDataset")
    / IIF(COUNT(
    IIF(
      Fields!CustGender.Value = "F",
      1,
      0
    ),"ReportDataset") = 0, 1,
    COUNT(
    IIF(
      Fields!CustGender.Value = "F",
      1,
      0
    ),"ReportDataset"))
    )

Of course this will throw an error if there are zero "F" records.

Upvotes: 1

Related Questions