Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3040
  • 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!
0
lottidah
Asked:
lottidah
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.
0
 
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:
http://blogs.msdn.com/b/robertbruckner/archive/2008/07/20/using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx
0
 
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.
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
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.
0
 
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:
=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.
0
 
lottidahAuthor Commented:
Thanks for the reply.  Unfortunately, this formula still winds up including the hidden rows.

I guess it can't be done.
0
 
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.

YTD.xml
0
 
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.

http://msdn.microsoft.com/en-us/library/ms183549%28v=SQL.90%29.aspx

After setting up the filter, try it out.  I hope this helps.
0
 
lottidahAuthor Commented:
thank you, the filter is what I was after
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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