How do I get around the Sum(First(...)) not allowed limitation is SSRS2005

Question

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 problem stems from the fact that this is actually two reports merged into one (as I see it). A Production Report and a sales report.
The report tried to address these criteria

• 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.

Answer 1

I had a similar issue and ended up using ROW_NUMBER in my query to provide a integer for the row value and then using SUM(IIF(myRowNumber = 1, myValue, 0)).

I'll edit this when I get to work and provide more data, but thought this might be enough to get you started. I'm curious about Adolf's solution too.

Answer 2

Pooh! Where's my peg?!

Have you thought about using windowing/ranking functions in the SQL for this?

This allows you to aggregate data without losing detail

e.g. Imagine for a range of values, you want the Min and Max returning, but you also wish to return the initial data (no summary of data).

Group Value Min Max
A      3    2    9
A      7    2    9
A      9    2    9
A      2    2    9
B      5    5    7
B      7    5    7
C etc..

Syntax looks odd but its just

AggregateFunctionYouWant  OVER (WhatYouWantItGroupedBy, WhatYouWantItOrderedBy) as AggVal

Windowing

Ranking

Answer 3

you're dataset is a little weird but i think i understand where you're going.

try making the dataset return in this order: Type, Product, SAS, SAE, Customer, Ordered Qty

what i would do is create a report with a table control. i would set up the type, product, and customer as three separate groups. i would put the sas and sae data on the same group as the product, and the quantity on the customer group. this should resemble what i believe you are trying to go for. your sas and sae should be in a first()