Reporting Services - exclude value

# Reporting Services - exclude value

• Tuesday, June 05, 2012 6:15 AM

Hello,

How to exclude value, I have four parameters - urgency 1,2,3,4 and I must calculate avg  urgency 1,2,3.

I created query,

=avg( avg ( (
IIf(Fields!urgency.Value=1,1,
iif(Fields!urgency.Value=2,1,
Iif(Fields!urgency.Value=3,1,0))))) xor
Iif(Fields!urgency.Value=4, 1,0))

but still, RS calculate urgency 4

• Edited by Tuesday, June 05, 2012 6:15 AM
•

### All Replies

• Tuesday, June 05, 2012 7:34 AM
Moderator

Hi Lukas317

Can you please give some more details what you are trying to achieve. Why you have put two avg function. What is the objective of this expression then someone might be able to help you out

If you have any question please let me know.

Many Thanks

Syed Qazafi Anjum

• Tuesday, June 05, 2012 8:51 AM

HI,

I put 2 avg function, because I want average for "urgency 1,2,3", and ignored in the counting of "urgency 4"

I tried in one function avg, but I gets the same Result.

• Tuesday, June 05, 2012 9:16 AM
Moderator

Hi There

=iif(Fields!Urgency.Value=4,nothing,avg(iif(Fields!Urgency.Value=1,1,iif(Fields!Urgency.Value=2,1,iif(Fields!Urgency.Value=3,1,nothing))),"DataSet1"))

`=iif(Fields!Urgency.Value=4,nothing,avg(iif(Fields!Urgency.Value=1,1,iif(Fields!Urgency.Value=2,1,iif(Fields!Urgency.Value=3,1,nothing))),"DataSet1"))`

Please put the dataset name if you would like to use dataset otherwise you can use like this

=iif(Fields!Urgency.Value=4,nothing,avg(iif(Fields!Urgency.Value=1,1,iif(Fields!Urgency.Value=2,1,iif(Fields!Urgency.Value=3,1,nothing)))))

`=iif(Fields!Urgency.Value=4,nothing,avg(iif(Fields!Urgency.Value=1,1,iif(Fields!Urgency.Value=2,1,iif(Fields!Urgency.Value=3,1,nothing)))))`

If you have any question please let me know.

Many Thanks

Syed Qazafi Anjum

• Tuesday, June 05, 2012 10:40 AM

Ok,

it works, but when I add value "ticket" i see "blank fields".
I want have query,  if  "urgency 4" do not add value to the report

When I used filter it works, but doesn't want to use the filters in the properties

• Tuesday, June 05, 2012 10:58 AM
Moderator

Hi There

Can you please let me know what is "ticket"

Are you saying that you do not want to display the values to the report if the Fields!Urgency.Value=4

If that is the case then you can put filter on your report to exclude the values Fields!Urgency.Value=4

You can put tablix filter expression like this

Fields!Urgency.Value<>4

If you have any question please let me know.

Many Thanks

Syed Qazafi Anjum

• Tuesday, June 05, 2012 11:52 AM

"ticket" is another value, but in matrix tabel, this value is helping me see what is wrong.

"Are you saying that you do not want to display the values to the report if the Fields!Urgency.Value=4"

Yes

"If that is the case then you can put filter on your report to exclude the values Fields!Urgency.Value=4

You can put tablix filter expression like this "

Yes, but I doesn't want to used the filter tablix, becouse I have second query in the same table, when I used/need "urgency 4 ".

I want created query, which exclude the values Fields!Urgency.Value=4

• Edited by Tuesday, June 05, 2012 11:53 AM
•
• Wednesday, June 06, 2012 9:35 AM

Hello,

Someone have any idea?

• Edited by Wednesday, June 06, 2012 9:35 AM
•