Tuesday, September 21, 2010

How to: Hide a SSRS report item like tablix, table, charts etc. if no data is found.

In your SQL Server Reporting Server (SSRS) reports you can add multiple report items. In some situations it can happen that no data is available for one of the data sets which are linked to these report items. Displaying empty report items can be confusing for the user. It is possible to display report items only if data is available to display. This can be done by defining an expression for the visibility of the report item. The result of this expression is the trigger to show or hide the report item. To configure the show or hide of a report item, you need to do the following:
  • Open the properties of the report item
  • Select visibility
  • Set when the report is initially run: Show or hide based on an expression.
  • Press the fx button to define the expression
  • Next example is an expression which will not display the tablix if the columns Hostname of dataset 'IndexImprovements' returns 0 records.

    =IIf(Count(Fields!hostname.Value, "IndexImprovements")=0,True,False)
    Enjoy building your reports.

    Picture credit: André Speek

No comments: