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

Prev: Using arrows as layout element
Next: Cover page problem

From: dela on 11 Dec 2009 12:20

---

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))

From: Uri Dimant on 14 Dec 2009 04:29

---

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" <dela(a)discussions.microsoft.com> wrote in message
news:FDA1A48B-020A-406D-A756-BF6248F207E4(a)microsoft.com...
>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))

From: Mark_Seven on 15 Dec 2009 15:43

---

On Dec 11, 11:20 am, dela <d...(a)discussions.microsoft.com> wrote:
> 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))

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

 | 
Pages: 1
Prev: Using arrows as layout element
Next: Cover page problem