Solved

Sub-Total field in Reporting Services report

Posted on 2010-08-17
10
1,173 Views
Last Modified: 2012-05-10
Hello,

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..
0
Comment
Question by:Mosquitoe
  • 6
  • 3
10 Comments
 
LVL 27

Expert Comment

by:planocz
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.
0
 
LVL 9

Assisted Solution

by:sureshbabukrish
sureshbabukrish earned 250 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))
0
 

Author Comment

by:Mosquitoe
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...
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Mosquitoe
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
0
 
LVL 9

Expert Comment

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

Author Comment

by:Mosquitoe
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
0
 
LVL 9

Accepted Solution

by:
sureshbabukrish earned 250 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?
0
 

Author Comment

by:Mosquitoe
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 ...
0
 

Author Comment

by:Mosquitoe
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"), "","")))
0
 

Author Closing Comment

by:Mosquitoe
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
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
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…

831 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