Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1179
  • Last Modified:

Sub-Total field in Reporting Services report

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
Mosquitoe
Asked:
Mosquitoe
  • 6
  • 3
2 Solutions
 
planoczCommented:

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
 
sureshbabukrishCommented:
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
 
MosquitoeAuthor Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
MosquitoeAuthor Commented:
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
 
sureshbabukrishCommented:
yes, you need to place the SUM(IIF..) in the expression
0
 
MosquitoeAuthor Commented:
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
 
sureshbabukrishCommented:
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
 
MosquitoeAuthor Commented:
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
 
MosquitoeAuthor Commented:

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
 
MosquitoeAuthor Commented:
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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