Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Reporting Services - exclude value

Answered Reporting Services - exclude value

  • Tuesday, June 05, 2012 6:15 AM
     
     
    Sign In to Vote
    0

    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 lukas317 Tuesday, June 05, 2012 6:15 AM
    •  
     

All Replies

  • Tuesday, June 05, 2012 7:34 AM
    Moderator
     
     
    Sign In to Vote
    0
     

    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

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

     

     
  • Tuesday, June 05, 2012 8:51 AM
     
     
    Sign In to Vote
    0

    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
     
     Answered Has Code
    Sign In to Vote
    0

    Hi There

    please try this one

    =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

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.


     
  • Tuesday, June 05, 2012 10:40 AM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0
     

    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

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

     
  • Tuesday, June 05, 2012 11:52 AM
     
     
    Sign In to Vote
    0

    "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 lukas317 Tuesday, June 05, 2012 11:53 AM
    •  
     
  • Wednesday, June 06, 2012 9:35 AM
     
     
    Sign In to Vote
    0

    Hello,

    Someone have any idea?


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