Creating a Subtotal of select Groups

Hi,

I am working on a new reporting system using reporting services, but I cannot figure out how to create a footer row which will only subtotal select group totals. If anyone has a method to do this please help!

Nathan

If you have a matrix report right click on the group and select the option subtotal.
If you have a tabular report right click on the left side of the table and select table footer. Then in each field you want to summarize put = SUM(Fields!FieldName.Value)
That's all|||

I've been able to do that for individual groups, but what I want to do is make footer subtotal of a select set of groups. So say I have data grouped by Credit card type. I have a group for MC, and another for Visa, and another for American express.

I want a footer total of just the MC and Visa groups, excluding the total for American express.

|||You could use an expression similar to this in the footer (it will add 0 instead of the actual amount if the card was Amex):
=Sum(iif(Fields!CardType.Value = "Amex", 0, Fields!TransactionAmount.Value))

--Robert|||Alright!

Thank you Robert. you made my day |||

When running this selective sum, i get a scope error. I have tried giving it a group name and a dataset. What am i doing wrong? here is my code:

=Sum(iff(Fields!CardType.Value = "Visa/Mc" OR Fields!cardType.Value = "Diner" OR Fields!cardType.Value = "JCB", Fields!amount.Value, 0))
This is my error:

"The value expression for the textbox ‘textbox9’ refers to the field ‘CardType’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Whats wrong?

|||I have the same problem. I don't want to sum a select number of groups, but all groups within the report. The principle is the same as the above, and I get the same scope error.
In my case I have a bunch of items grouped by customer. Each customer has a subtotal, and I want to have a grand total of all the customers.
Any ideas/workarounds?|||Note: Field names are case-sensitive. In your expression it seems like you have upper-case and lower-case "CardType" fields.
Also, are the cardType field and the amount field in the same dataset?

-- Robert|||If you want to get the grand total, you just need to specify either the data region name (i.e. table, list, or matrix report item name) or the data set name.
E.g.
=Sum(Fields!Amount.Value, "DataSet1")

-- Robert|||Thanks, Robert! I knew it had to be something simple. |||Hi, somehow related with the topic:
Is posible to have something like: the sum of the ValueField from all the rows of DataSet2 that have CompareFiled equal with the current value of ComparedWithField from DataSet1?
In other words: in the expression of the SUM function can be used more then one scope?
=Sum(iff( DS2!Fields!CardType.Value = Fields!CT.Value, DS2!Fields!amount.Value, 0))