Answered by:
Filter Tablix based on conditions

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 PageFriday, 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.- Edited by Xi Jin Monday, July 4, 2016 8:55 AM
- Proposed as answer by Xi Jin Friday, July 15, 2016 10:19 AM
- Marked as answer by Qiuyun YuMicrosoft contingent staff Saturday, July 16, 2016 9:04 AM
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.- Edited by Xi Jin Monday, July 4, 2016 8:55 AM
- Proposed as answer by Xi Jin Friday, July 15, 2016 10:19 AM
- Marked as answer by Qiuyun YuMicrosoft contingent staff Saturday, July 16, 2016 9:04 AM
Monday, July 4, 2016 8:54 AM