We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Exclude hidden rows from totals

lottidah
lottidah asked
on
Medium Priority
4,087 Views
Last Modified: 2012-05-11
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!
Comment
Watch Question

Author

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.
CERTIFIED EXPERT

Commented:
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:
http://blogs.msdn.com/b/robertbruckner/archive/2008/07/20/using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx

Author

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.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Commented:
As the expression for the total field of the Jan column use:
=Sum(iif(Fields!YTD.value <> 0,Fields!Jan.Value,0))
instead of:
=Sum(Fields!Jan.Value)
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.

Author

Commented:
Thanks for the reply.  Unfortunately, this formula still winds up including the hidden rows.

I guess it can't be done.
CERTIFIED EXPERT

Commented:
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.

YTD.xml
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
thank you, the filter is what I was after
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.