The problem that I have is SQL Server Reporting Services does not like Sum(First()) notation. It will only allow either Sum() or First().

**The Context**  

I am creating a reconciliation report. ie. what sock we had a the start of a period, what was ordered and what stock we had at the end.

Dataset returns something like

```

Type,Product,Customer,Stock at Start(SAS), Ordered Qty, Stock At End (SAE)
Export,1,1,100,5,90
Export,1,2,100,5,90
Domestic,2,1,200,10,150
Domestic,2,2,200,20,150
Domestic,2,3,200,30,150

```

I group by Type, then Product and list the customers that bought that product.
I want to display the total for SAS, Ordered Qty, and SAE but if I do a Sum on the SAS or SAE I get a value of 200 and 600 for Product 1 and 2 respectively when it should have been 100 and 200 respectively.

I thought that i could do a Sum(First()) But SSRS complains that I can not have an aggregate within an aggregate. 

Ideally SSRS needs a Sum(Distinct())

**Solutions So Far**  

1. Don't show the Stock at Start and Stock At End as part of the totals.  

2. Write some code directly in the report to do the calc. tried this one - didn't work as I expected. 
3. Write an assembly to do the calculation. (Have not tried this one)

**Edit - Problem clarification**   

* the market that we sold it to (export, domestic)
* how much did we have in stock,
* how much was produced,
* how much was sold,
* who did we sell it to,
* how much do we have left over.

The complicating factor is the who did we sell it to. with out that, it would have been relativly easy. But including it means that the other top line figures (stock at start and stock at end) have nothing to do with the what is sold, other than the particular product.