Dont's show rows in SSRS

1
votes

I'm trying to achieve my report displaying a "No Data Available" message if no results are returned in my query.

I am trying to achieve this via an expression against the Row Visibility.

So I have a Tablix that looks like this -

enter image description here

If there is data available then I want the third, fourth and fifth line to show. If no data exists then I want the first two rows to display.....

In the Row Visibility for the first two rows I have the following - 

=iif(CountRows("RentTransactions") = 0, true, false)

In the Row Visibility for the remaining three rows I have the following - 

=iif(CountRows("RentTransactions") > 0, true, false)

I have a filter on the Tablix that just limits it to "AccountType" = Water.

When I run the report between 01/06/2016 and 30/06/2016 - I know there are not transaction - so would expect my report to return the first two rows.... It doesn't it returns the bottom ones , with no data in it??

What am I doing wrong?

The DataSet is definitely called RentTransactions

enter image description here

1
reporting-servicesexpression

1 Answers

0
votes

There are a few issues going on here.

  • CountRows with the dataset name will always return the total number of rows in the entire dataset.
  • Row Visibility will make the entire row blank, but it will still take up space. This would look bad if there are alternating blank rows.

What you're really trying to do is control what is displayed in each cell. So in each cell you'll want to have an expression that checks whether or not to display a value. For example, for the Description field it would look something like this:

=IIf(Count(Fields!Transaction_Type.Value) > 0, Fields!Description.Value, "")

This expression will work by returning a count of 0 for NULL Transaction Types. You can customize this if needed.

Also make sure that the query is returning rows for dates with no transactions. Otherwise there's no raw data for the report to do anything with in the first place.