Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Sub-Total field in Reporting Services report

Posted on 2010-08-17
Medium Priority
Last Modified: 2012-05-10

I am trying to figure out how I can exclude a value in the sub-totals.  The group has five fields like
A, b, c, d, e
I would like the sub-totals to sub-total a, b, c and d and exclude all of e - Can anybody tell me if this is possible?  Additionally, if it is not, I also considered using a stored procedure (SQL Server) but then debated if it would actually be useable - as in how would I tie those sub-total values, derived from an sp into the sub-total field and have it's value go in the correct columns...Anyways - if anybody can give their commenst - much appreciated..
Question by:Mosquitoe
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
LVL 27

Expert Comment

ID: 33460852

each field is a standalone field. So your  subtotals are made by you  per field. Subtotals are a,b,c,d
If you are having a problem of removing the e field sub total then just hide it.
Please state if you are using 2005 or 2008 SSRS and what table type you are using.

Assisted Solution

sureshbabukrish earned 750 total points
ID: 33472399
based upon what decision you want to exclude the value "e" while summing it up.

you can use IIF condition to the column and make when it is "e' values as 0 and then use the SUM function on  it

=SUM( IIF(Fields!colval = "e",0,Fields!colval))

Author Comment

ID: 33473745
I am using 2008 SSRS - It is a matrix.
planocz - The field I am sub-totalling on, I would like to have it sub-totalled, just have one of the values removed from the total.
sureshbabukrish - I am going to try this now, and see if that works...
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Author Comment

ID: 33473793
I should clarify where this condition will sit - as I am not a 100% sure where you are pointing me. In the matrix,  I have a group say TV I need to see the values for all of them so:  
TV is the group - the actual numeric values are a quantity field - The sub-total is placed on the TV group.  So is it in that expression that I place the IIF statement?  
a = 1
b = 3
c = 7
d = 4
e = 2

Expert Comment

ID: 33473811
yes, you need to place the SUM(IIF..) in the expression

Author Comment

ID: 33473981
If I place the expression in the group - it will not compile and tells me that a group expression for the matrix includes an aggregate function.  Aggregate functions cannot be used in group expressions.  If I go to put the IIF statement in the sub-total field itself, (here is where the text value resides for what the label should be ie/"Total:  " - However, in my case it is a database driven billingual field - so I actually have it associated with a field already, and it also comes from a separate db than the db that most of the rest of the report is built on.  If I attempt to put two expressions in here, that field simply has "ERROR" in it when the report is compiled. - If I try to leave the expression in teh sub-total field just alone, I get  "error" in that field when compiled, but the numeric values under each column still remain the same - Somehow I thinkthat I am putting this in the wrong place.  Are you intending that I place this expression in the actual numeric fields where the number of TV's is summed?  If I do that, then the value for e, becomes zero, and yes the sub-total works - But we need to see all the values, it is only in the sub-total that e should be removed...Maybe I am getting lost here

Accepted Solution

sureshbabukrish earned 750 total points
ID: 33475723
then you need to do this in the query itself instead of doing it in a matrix. can you show me your query so that i can suggest?

Author Comment

ID: 33475931
I did think of doing it right in the sp itself - here was my dilemna - If I add the sub-totals to the query, that is not an issue - adding it to the report becomes the issue.  I cannot get the computated field to display in the matrix in the appropriate location - the sub-total field only allows the text to be edited.  If I do not use that option and add a separate field to the report, it will never display under the fields - I can only add a new row group or column under the column groups....Unless I am missing something ...

Author Comment

ID: 33501197

Hello - so I have found a solution to this. sureshbabukrish, you were partly right in that the query could be used to supply the actual calculated value - but how to get it into the grid displayed correctly, you wuld have to use the InScope function along with the IIF clause in this manner to ensure that none of the calculated fields include the data, but that the field doesn't get removed completely from display:
=(IIF(InScope("matrix1_Release"), Fields!name.Value, IIF(InScope("matrix1_Prov"), "","")))

Author Closing Comment

ID: 33501245
The Inscope must be used in the field or it will remove the field you wish from the display completely and not just in the total fields

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question