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

Question

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?)

Answer 1

I've never used SSRS so I don't know if there's any predefined way to calculate it. You can do it using the following SQL:

SELECT
  Customer,
  [Invoice Type],
  Balance,
  SUM(CASE WHEN [Invoice Type] = 'Good' THEN Balance ELSE -Balance END) OVER (PARTITION BY Customer) AS [Total Adjusted Balance]
FROM table1;

Answer 2

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