SQL Server Reporting Services 2005 – How to Put a Conditional Total in a Header Row

reportingreporting-servicessqlsql-servertsql

Let's say I have a table like this:

Customer,Invoice Type,Balance
A,Good,50
A,Good,10
A,Bad,20
B,Good,20

And I want to make a report like this:

Customer,Invoice Type,Balance,Total Adjusted Balance
A            40    
   Good
        50
        10    
   Bad
        20 
B            20    
   Good
        20

Where total adjusted balance is the sum of the good invoice balances minus the sum of the bad for a customer.

Is adjusted balance something I use an expression in the layout editor to calculate? Or is there a better way?

(If I need to use an expression in the layout editor, how do I do it?)

Best Solution

You just need to SUM up the fields in the header.

Reporting Services is very aware of context or "Scoping".

So if you have different groupings i.e. Country, State, Town

By refering to the value you wish to sum for each of these (e.g. SalesAmt), it will only sum for those values within that particular group.

http://msdn.microsoft.com/en-us/library/bb630415.aspx