• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

Reporting Services - Summing Group Header Values


I have a report that is laid out like this:

Group Header     Client
     Group Header     Account
          Detail                   Transaction

First, I have deleted the detail row as I have no interest in viewing individual transactions.  I have decided to hide Account groups that do not have a total transation amount of 20,000.  I also show that sum amount in the Account Group Header.  Now, I'm trying to sum all Account groups within the Client group.  My problem is that I don't want all transaction to be included within the Client sum.  I only want the transactions that were part of the Account group sums.  I only want to sum the Account group sums.  I tried referencing the Account sum cell using ReportItems but it appears that I can only do that in a page header/footer.  Is there any way to accomplish what I'm trying to do?
1 Solution
If you don't need the detail and want to hide account groups with transactions totalling 20,000 or less, wouldn't it be easier to change your backend query to return only the required Account Groups with transactions amounting to over 20,000?

A simple group by/having clause would give you only the required records which will  negate the requirement of having to do a conditional sum within the report -

SELECT Client, Account, SUM(TransAmount)
FROM TableName
GROUP BY Client, Account
HAVING SUM(TransAmount) > 20000

This will also make the report and the backend query execute faster as unnecessary data is not brought into the report and then excluded..

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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