• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3397
  • Last Modified:

Exclude hidden rows from totals

Good afternoon.  I have a report in SQL Reporting Services 2005 that contains numbers for a particular situation, broken into months.  So, there are columns for each month.  In some cases, the numbers from one month might offset the numbers from a previous month, leaving a YTD total of zero.  When this occurs, I suppress the row entirely using a Visibility, Hidden expression.  This is working fine.

However, the grand totals for each month (column) are still including these hidden rows.  I have not been able to figure out how to make sure this doesn't happen.  I did find through Google some advice to filter the table so it does not include the hidden rows, but the suggestion did not include any sample code.  

Can anyone provide a filter expression that will exclude hidden rows?  Or provide some other method to ensure the totals for each month do not include hidden rows?

Thank you!
1 Solution
lottidahAuthor Commented:
Is this a toughie?  I'm just hoping to set a filter or something that only allows my total row to include rows where the visibility is set to true.
Nico BontenbalCommented:
On your total row can't you use something like:
=sum(iif([criteria for being hidden is not met],Fields!Thingy.value,0))
If this doesn't help the information in this article might help you:
lottidahAuthor Commented:
I'm not really sure I could do that.  Hiding a row when the YTD amount is 0 is one thing, but trying to come up with a calculation that excludes certain values when the YTD amount is 0 is beyond me

Jan   Feb   Mar   YTD
1       0       -1       0
5       3        6      14
1       2        3       6

In the above example, the first row would be hidden because the YTD value is 0.  So the total of the Jan colum should be 6 and not 7.  And the total of the Mar column should be 9 and not 8.

I'm using RS 2005.
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Alpesh PatelAssistant ConsultantCommented:
In grand total, I will not exclude hidden rows. You have to manually calculate the Grand total with each row and check the same expression to hide the row.
Nico BontenbalCommented:
As the expression for the total field of the Jan column use:
=Sum(iif(Fields!YTD.value <> 0,Fields!Jan.Value,0))
instead of:
Assuming the YTD is a column in the Dataset. If it is calculated you can use:
=Sum(iif((Fields!Jan.value+Fields!Feb.value+Fields!Mar.value) <> 0,Fields!Jan.Value,0))
If this still doesn't solve your problem. Please provide some more information about what your Dataset looks like, and how you report is build.
lottidahAuthor Commented:
Thanks for the reply.  Unfortunately, this formula still winds up including the hidden rows.

I guess it can't be done.
Nico BontenbalCommented:
Strange. I've tested it in 2005 and it works fine. Please download the attached file and rename it to YTD.rdl. When you run this report you'll see that the total row only sums the rows where YTD<>0.
You'll need to change the data source before you can run this report. The query creates its own data, you don't need any specific tables in the database.

Alfred A.Commented:
How do you retrieve the data you are displaying in your report?  I assume you access it through a dataset.  If this is the case, then you can set a filter expression in the dataset before even displaying the data in a Tablix or Matrix.

In SSRS 2005, under the Dataset dialog box, select Filters Tab.

In the Expression area, input this:

Fields!JAN.Value + Fields!FEB.Value + Fields!MAR.Value

In the Operator area, try selecting "<>"

In the Value area, put the value 0.


After setting up the filter, try it out.  I hope this helps.
lottidahAuthor Commented:
thank you, the filter is what I was after
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now