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

C M
C M used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

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
Awarded 2009
Distinguished Expert 2018
Commented:
>>  I have two datasets. One is for historical data, the other is currentdata. I use the lookup feature to populate one of the cells

I would suggest to create a procedure and then merge both historical and current data inside it and then give a single result set to the SSRS dataset so that it would be easier for you to implement the row hide or column hide..
One advantage of this approach is all the lookup manipulations will be done at Database engine which is faster compared to lookup operations in SSRS.
Just think about it once and see whether you can implement this or not..
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
Awarded 2009
Distinguished Expert 2018

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial