Go Premium for a chance to win a PS4. Enter to Win

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

Reporting Services - Summing Group Header Values

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
ryanvb83
Asked:
ryanvb83
1 Solution
 
reb73Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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