CODEWITHSUNDEEP
reporting-servicesssrs-2008
user1546143
user1546143

Reputation: 143

SSRS - I want to add a filtered expression for a calculation

--

I would like to add and expression that calculates a value based on a certain value. I have a dataset with the name DSSPend that has to columns one is the Area and the other spend. Now I would like to calculate the spend based on certain area values. I tried something like this but does not seem to work

=Iif((Fields!Area.Value, "DSSSpend") IN ('New York','Miami','Texas') = SUM(Fields!Spend.Value, "DSSSpend"), 0)

Upvotes: 0

Views: 56

Answers (1)

mmarie
mmarie

Reputation: 5638

=sum(iif((Fields!Area.Value = "New York" or Fields!Area.Value = "Miami" or Fields!Area.Value = "Texas"), 
CDec(Fields!Spend.Value), CDec(0)))

There is no IN operator in SSRS expressions. You can achieve the same goal by using OR.

Also, your SUM aggregation needs to go outside the IIF. Your expression would evaluate the first record only rather than going through each record in the dataset and then aggregating. The expression I have written above goes through the entire dataset, summing either the value in the Spend field or 0, depending on the value of Area.

Update: If you were getting an error before, it was probably because it wasn't seeing your amounts as numbers. By converting them explicitly, you should get around this issue.

Upvotes: 2

Related Questions