We help IT Professionals succeed at work.

SSRS - Hide table/column headers if there are no rows after applying the a hide row expression. Or another way perhaps

439 Views
Last Modified: 2018-09-25
In an SSRS report I want to hide the tablix/table when there are no rows appearing. Problem is there are rows, but I hide the rows when column 1 is the same as column2. So my problem is that I don't want the column headers / table to appear when no other row appears. Is this possible I think there are scope issues doing this.

At moment I have two datasets. One retrieves current values, the other dataset retrieves historical data from a specific point in time. So I am using the lookup value '=Lookup(Fields!LabelLookup.Value, Fields!LabelLookup.Value, Fields!Value.Value,"Date2")' to show in one cell what the value was at a specific date and in the other cell what is is now on the same row.  When the two values are the same I hide the row because there was no change. This works fine, but sometimes I am left with just the table header showing no rows. However, there might be lots of rows but are actually duplicate, as the value has not changed between the two dates.

Is there a way to check if there are no rows appearing in the table after the hide rows has been applied? I'm not sure it is possible? If not is there another way I might be able to do this?

Using sql server and SSRS instance 2014
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> Is there a way to check if there are no rows appearing in the table after the hide rows has been applied?

If you wish to not display rows if all the values in that row is NULL or 0, then its possible and you can use the below one..
https://www.crestwood.com/2017/01/27/hiding-blank-row-sql-server-reporting-services-ssrs-report/

>> So my problem is that I don't want the column headers / table to appear when no other row appears

If you don't want to display the tablix completely if no rows returned then this can help you out..
http://www.midnightdba.com/Jen/2012/01/ssrs-tip-hide-a-tablix-if-no-rows-returned/

if you don't want to display few columns if it doesn't have any values for all rows, then do this..
http://rahulmsdax.blogspot.com/2017/01/hide-column-if-all-rows-are-empty.html

Since your question was revolving around all scenarios, provided the complete set of options.. just try to use whatever you are trying to implement..
C M

Author

Commented:
Hi Raja,

Thanks very much for those links. Unfortunately my problem is slightly different. I have two datasets. One is for historical data, the other is currentdata.
I use the lookup feature to populate one of the cells e.g. Olddata. I then use the current data to populate the other cell CurrentData.
I then use the row hide using check if the cells in that row are the same or not. So if  ReportItems!OldData.value <>ReportItems!CurrentData.Value then show the row otherwise hide. So the data might have information or it might not. But I only want to hide the row if both are the same as the report is showing changes between two dates. If there was some way to compare if there was any rows after the I have applied the hide row if there were any rows?
Perhaps I am going about this wrong and I need a different approach to comparing two sets of trending information?

Kind Regards,
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
C M

Author

Commented:
Hey Raja,
Excellent and yes your right I should have thought of that before. I guess that requires a lot of rework but the solution is there! I will try it out, thanks very much for your help!!
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Welcome, glad to point you in the right direction..