UnreachableCode
UnreachableCode

Reputation: 1791

How can I hide an rdlc report Tablix based on the contents of cells in a column, in Sql Server Report Services?

I have the following table in a report:

enter image description here

I need to hide the table (or even better, add some message that this has happened) for when all of the value cells pictured have no value. So in the picture example, the table would not be hidden, but if the 5 values were missing, it would be.

The problem is there's no real way to reference cells, or even columns in SSRS as far as I can tell via Google. Have I missed something?

Upvotes: 1

Views: 5237

Answers (1)

R. Richards
R. Richards

Reputation: 25151

My solution to this type of thing is to check the row count of the underlying data set for the tablix. If it is not zero, then show the tablix. This is what the expression would look something like in the Hidden property for the tablix.

=IIf(CountRows("DataSet1") <> 0, False, True)

Add a label that says something like "No data to report" to the report that only shows when the dataset is empty. Expression here:

=IIf(CountRows("DataSet1") = 0, False, True)

This assumes that no records will be returned in the dataset when there are no values for all selected columns. This also assumes that the name of your dataset is DataSet1.

EDIT:

Another possible approach is to check all of the dataset values that fill the cells, if they are all empty, then hide the tablix. Something like this could be used for the Hidden property:

=IIf(Fields!field_name1.Value <> "" OR Fields!field_name2.Value <> "" (etc.), False, True)

One other approach is to check for values in the SQL for the dataset. If all of the values are empty return an extra column, maybe named hidden, that is false. True if values are present. Not the prettiest approach, and it may mean a significant change to your SQL, but it could work.

With this last one, instead of returning true or false, you could return a 1 or 0. Then, in the RDL for the Hidden property, sum that column and if it is = 0, hide the tablix.

=IIf(Sum(Fields!hidden.Value, "DataSet1") <> 0, False, True)

Upvotes: 3

Related Questions