locked
Hide Rows in Report which < 0 RRS feed

  • Question

  • Hi

    I have an expression which is in a table in SSRS, and I if the sum is < 0 I want to hide the row completely. 

    This is the expression in the column =SUM(iif(Fields!Number_Free.Value < 0,0,Fields!Number_Free.Value))

    How do I do I hide the row if it is less than 0?

    I have tired selecting the row, right clicking on it, selecting Row Visibility and show/ hide based on an expression:

    =Fields!Number_Free.Value <= 0 

    but this expression doesnt work.

    Thanks,

    Thursday, May 31, 2012 1:38 PM

Answers

  • reporter,

    As Manoj mentioned, you can hide row but row visibility or you can go for group visibility from group properties. 

    I think the expression may not be showing correctly due to the groupings I have.

    for this you can use scope operator for your aggregate function as show below

    =IIF(SUM(Fields!ID.Value,"YourGroupName")<=1,True,False)

    Let me know if you have any queries.



    Thanks,
    Sandip Shinde(Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip)

    • Marked as answer by Mike Yin Thursday, June 7, 2012 3:49 PM
    Monday, June 4, 2012 10:35 AM

All replies

  • try in this way:

    =IIF(Fields!Number_Free.Value <= 0,true,false)

    Thanks,
    Saikat

    Thursday, May 31, 2012 1:43 PM
  • reporter,

    You are on right track but missing one thing in your expression. Try following steps

    • Right Click on your Row which you want to hide if sum of your value is less than 0
    • Click on Row Visibility >> It will open Row Visibility dialog box
    • Click on Show or hide based on an expression option button
    • Click on Expression button("Fx") & type in following expression
    =IIF(SUM(Fields!Number_Free.Value)=0,True,False)
    Let me know if you have any question.


    Thanks,
    Sandip Shinde(Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip)

    Thursday, May 31, 2012 1:44 PM
  • Hi,

    Thanks for this. Unfortunately it doesnt work in the Row Visibility area.

    If this expression in my table is equal or less than 0 I would like to hide the row: =SUM(iif(Fields!Number_Free.Value < 0,0,Fields!Number_Free.Value))  e.g. if the result of the expression is 0 I just want the row hidden from the tablix.

    Does it matter that the Fields!Number_Free.Value  is an SSRS calculated field in the dataset?

    thanks,

    Thursday, May 31, 2012 1:57 PM
  • reporter,

    If aggregate functions does not work in your Row Visibility this means you are trying to hide rows from details part of your table/tablix.

    Your table always has row details & row headers if you want to hide row detail then try following expression

     
    =IIF(Fields!Number_Free.Value<=0,True,False)

    If your row is part of Row Group/Row Header then try following expression

    =IIF(SUM(Fields!Number_Free.Value)<=0,True,False)

    Let me know if you still face any issues.


    Thanks,
    Sandip Shinde(Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip)

    Thursday, May 31, 2012 2:02 PM
  • Hi

    No its still not working.  Do you think it is to do with my groupings? I have two groups Date and Type.  I am highlighting the row with the groups to add the visibility.

    Thanks

    Thursday, May 31, 2012 3:24 PM
  • Hi reporter,

    If you want to hide the whole row, add the visibility expression here

    else you want to hide only Type group values, then you can do that inside the row groups.



    Regards,
    Manoj
    *Happy to help
    http://experiencingmsbi.blogspot.com/
    Experiencing Microsoft BI

    Thursday, May 31, 2012 4:23 PM
  • Hello, yes i was right clicking there and selecting Row Visibility, and adding in the expression.  I think the expression may not be showing correctly due to the groupings I have.
    Thursday, May 31, 2012 4:38 PM
  • reporter,

    As Manoj mentioned, you can hide row but row visibility or you can go for group visibility from group properties. 

    I think the expression may not be showing correctly due to the groupings I have.

    for this you can use scope operator for your aggregate function as show below

    =IIF(SUM(Fields!ID.Value,"YourGroupName")<=1,True,False)

    Let me know if you have any queries.



    Thanks,
    Sandip Shinde(Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip)

    • Marked as answer by Mike Yin Thursday, June 7, 2012 3:49 PM
    Monday, June 4, 2012 10:35 AM