locked
Filter Tablix based on conditions RRS feed

  • Question

  • Hi,

    I have created Tablix report in Report builder, now I want to filter the rows based on few column conditions.

    I want to filter the matrix based on the value in column H,I.

    Example:

    If no value in both H,then filter the rows marked in yellow(rows CCC,DDD,GGG,HHH).

    I tried few filters on tablix but with no positive results.

    Any help would be much appreciated.

    Thanks,

    Praveen


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Friday, July 1, 2016 8:48 AM

Answers

  • Hi Praveen,

    According to your description, you want to change the rows’ background color based on the column conditions. Right?
    In your scenario, were your column H and column I the fields in dataset query? If so, you can specify the Fill Properties in textbox properties to change the cell background color by using the expression like this:

    =IIF(IsNothing(Fields!Column_H.Value) and IsNothing(Fields!Column_I.Value), “Yellow”, “Transparent”)

    If the column H and column I were the value of one filed in dataset query. Then you should know that when you run the report with a matrix, the values are generated one by one. As when the column H gets a value, we don’t know if there is a value in column I or not. So we can’t filter rows based on the two column conditions at the same time as using the expression like

    IIF(Fields!Column.Value= “H” and Fields!Column.Value= “I” and IsNothing(Fields!Value.Value))

    . I think your requirement cannot be achieved.

    If you still have any questions, please feel free to ask.
    Thanks,
    Xi Jin.


    Monday, July 4, 2016 8:54 AM

All replies

  • Hi Praveen,

    You can put visibilty condition on the row based on the column value.

    let say ColumnH and ColumnI are the query dataset fields then your expression look like below for visibilty:-

    =iif(isnothing(Field!ColumnH.value) and isnothing(Field!ColumnI.value),True,False)


    Thanks

    Prasad

    Friday, July 1, 2016 9:36 AM
  • Hi Praveen,

    According to your description, you want to change the rows’ background color based on the column conditions. Right?
    In your scenario, were your column H and column I the fields in dataset query? If so, you can specify the Fill Properties in textbox properties to change the cell background color by using the expression like this:

    =IIF(IsNothing(Fields!Column_H.Value) and IsNothing(Fields!Column_I.Value), “Yellow”, “Transparent”)

    If the column H and column I were the value of one filed in dataset query. Then you should know that when you run the report with a matrix, the values are generated one by one. As when the column H gets a value, we don’t know if there is a value in column I or not. So we can’t filter rows based on the two column conditions at the same time as using the expression like

    IIF(Fields!Column.Value= “H” and Fields!Column.Value= “I” and IsNothing(Fields!Value.Value))

    . I think your requirement cannot be achieved.

    If you still have any questions, please feel free to ask.
    Thanks,
    Xi Jin.


    Monday, July 4, 2016 8:54 AM