Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reporting Services - Summing Group Header Values

Posted on 2009-03-31
4
Medium Priority
?
640 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

721 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