CODEWITHSUNDEEP
reporting-servicesreportbuilder3.0
Graeme
Graeme

Reputation: 2687

How to sum a column that is based on an expression in SSRS ReportBuilder table

If have a column where the value is based on another field, so the expression is 

=iif(Fields!TaskType.Value = "Type1", Fields!Amount.Value, 0)

And I'm trying to get the sum of this in the group totals using the folooing exporession, but it gives #Error (with or without the group name as scope):

=sum(iif(Fields!TaskType.Value="Type1", Fields!Amount.Value, 0), "GrpProjectNumber")

This looks fine to me. What am I doing wrong? Thanks in advance

Upvotes: 0

Views: 19541

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

I've constructed a simple DataSet to try and mimic yours:

select Amount = cast(100.0 as money), TaskType = 'Type1'
  union all select Amount = cast(100.0 as money), TaskType = 'Type1'
  union all select Amount = cast(100.0 as money), TaskType = 'Type2'

I replicated your error with your expression, but the following works for me:

=Sum(IIf(Fields!TaskType.Value="Type1", Fields!Amount.Value, CDec(0)), "GrpProjectNumber")

So it seems like you were on the right path; just needed to apply the CDec cast to the 0 constant in the expression.

Upvotes: 3

Related Questions