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

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
LVL 1
C MAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
0
C MAuthor 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,
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>>  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..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
C MAuthor 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!!
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome, glad to point you in the right direction..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server OS

From novice to tech pro — start learning today.