Unable to use Previous in a Sum aggregate function

Question

I have a report that produces totals based on a offer. Within each offer a single user may have multiple rows. An example is below

Offer  User  Amount  Total

1        1       10         10

1        2         5         10

1        2         5         10

1        3       10         10

In the above example the sum for Total would be 40.00. What I want to do is to have the total for user 2, which has 2 rows, to only be added once so the total would be 30.

This is what I tried to do...

Sum(IIf(Fields!User.Value = Previous(Fields!User.Value), 0, Fields!Amount.Value))

But I receive an error that says "Previous functions cannot be specified as nested aggregates".

Does anyone know of another way to accomplish this? I've tried using variables but there are too many restrictions on those as well.

Thanks

Answer 1

I would like to use a sql to do the calculation.

---

A Fan of SSIS, SSRS and SSAS

Answer 2

Thanks for the reply.

I'm not sure how I would do that. I need all of the data to display on the report so I can't merge rows.

Answer 3

So you want to display all data and then added the calculated data at the bottom?

---

A Fan of SSIS, SSRS and SSAS

Answer 4

Yes, I want to display each row and then a subtotal for the offer. 

Answer 5

Sub-Total:

DECLARE @YourTable TABLE (
	[Offer] int,
	[User] int,
	[Amount] int,
	[Total] int
)

INSERT INTO @YourTable ([Offer], [User], [Amount], [Total])
VALUES (1, 1, 10, 10), (1, 2, 5, 10), (1, 2, 5, 10), (1, 3, 10, 10);

SELECT t.[Offer], 0 AS [User], 0 AS [Amount], SUM(t.[Total]) AS [Total]
FROM
(
	SELECT [Offer], [User], [Amount], [Total], ROW_NUMBER() OVER (PARTITION BY [Offer], [User] ORDER BY [User]) AS Ranked
	FROM @YourTable
) AS t
WHERE t.Ranked = 1
GROUP BY [Offer]

---

A Fan of SSIS, SSRS and SSAS