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..
MosquitoeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.