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

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.

From stackoverflow

• Write a subquery.

  Ideally SSRS needs a Sum(Distinct())

  Re-write your query to do this correctly.

  I suspect your problem is that you're written a query that gets you the wrong results, or you have poorly designed tables. Without knowing more about what you're trying to do, I can't tell you how to fix it, but it has a bad "smell".

• 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

  Nathan Fisher : Yes I aggree, it does smell... I will looking to the windowing and ranking. The main problem is that it is really two seperate reports merged into one. A production report (stock at start, production, sales, stock at end) and a sales report(who those sales were to). Unfortunalty that is how management wanted the report.

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

  Paul G : Ok, I included a column like: SELECT ROW_NUMBER() OVER (Partition BY [keyFieldForRecGroup] ORDER BY [keyFieldForRecGroup] ) AS myRowNumber This causes the row number to be reset on each new occurrence of keyFieldForRecGroup. Then you can use the SUM + IIF combination to get the desired result.

  Paul G : Should have used your field names: SELECT ROW_NUMBER() OVER (Partition BY [Type] ORDER BY [Type] ) AS myRowNumber SUM(IIF(myRowNumber = 1, [Ordered Qty], 0)

• 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()

  DForck42 : to me it seems that your data should be in a bit of a hierarchial structure, going from type to product to customer. having the dataset return in a similar structure allows (atleast to me) a better idea to plan out the report.

  Nathan Fisher : You are correct that the data should be a hierarchical structure, but how do you do that when the data-set in reporting server seems to only allow a flat structure. perhaps I have missed something obvious.

  DForck42 : try following this blog and see if it helps you to understand http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/creating-a-basic-drilldown-report-in-ssr-2005

  Nathan Fisher : Thanks for the blog. I think that this will certainly help for future reports. I will have to see how this style of report will translate to my requirements for this current scenario. I will have to do a little more playing around with the data and the report.