How do I exclude data using the IIF statement in report builder

[dela]

I have data in my dataset that I want to exclude in the average calculation
for the final report (those with a key.value of 999). Here is how my
statement is written but it is not returning any value. What am I doing
wrong?

=iif((Fields!MeasureID.Value = 1 or Fields!MeasureID.Value = 16) and NOT
Fields!Key.Value = 999, round(Avg(Fields!CY_Rate.Value),0),
iif((Fields!MeasureID.Value = 19 or Fields!MeasureID.Value = 20
or Fields!MeasureID.Value = 21 or Fields!MeasureID.Value = 22
or Fields!MeasureID.Value = 23 or Fields!MeasureID.Value = 24) and NOT
Fields!Key.Value = 999, round(Avg(Fields!CY_Rate.Value),0) & "%",nothing))

[Uri Dimant]

dela
I think you need to write custom function to get the code back
http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-reporting-services-part-4/ -
--Reporting Services

"dela" <de...@discussions.microsoft.com> wrote in message
news:FDA1A48B-020A-406D...@microsoft.com...

[Mark_Seven]

It might be easier to add a couple columns (COL_1, COL_2) to your
dataset. I would use the CASE statement to derive each column. You
would place all the logic from your IF statement into the CASE
statement.
If true, then COL_1 would return cy_rate.value, else it would return
0.... also, if TRUE, then COL_2 would return 1, else it would return
0.... Then in your report, SUM(COL_1)/SUM(COL_2) should be the average
you are looking for... just add a little logic to avoid dividing by
zero...

HTH,
Mark