Solved

Sub-Total field in Reporting Services report

Posted on 2010-08-17
10
1,174 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where to obtain Oracle Data Provider for .NET 3.5 7 54
Using MS Code on my Mac 6 68
Pivot not using aggregate yield error 3 61
Windows ICD FFU Issue 4 60
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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