Avatar of C M
C M
 asked on

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
Microsoft Server OSMicrosoft SQL ServerSSRS

Avatar of undefined
Last Comment
Raja Jegan R

8/22/2022 - Mon
Raja Jegan R

>> 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

ASKER
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,
ASKER CERTIFIED SOLUTION
Raja Jegan R

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
C M

ASKER
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!!
Your help has saved me hundreds of hours of internet surfing.
fblack61
Raja Jegan R

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