lottidah
asked on
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!
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!
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
=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
ASKER
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.
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.
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.
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
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.
=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
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.
ASKER
Thanks for the reply. Unfortunately, this formula still winds up including the hidden rows.
I guess it can't be done.
I guess it can't be done.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you, the filter is what I was after
ASKER