Solved

Reporting Services - Summing Group Header Values

Posted on 2009-03-31
4
637 Views
Last Modified: 2012-05-06
Hello,

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?
0
Comment
Question by:ryanvb83
4 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 500 total points
ID: 24044966
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..

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 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