locked
Unable to use Previous in a Sum aggregate function RRS feed

  • 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



    • Edited by GaryMha Friday, October 21, 2016 5:21 PM
    Friday, October 21, 2016 4:50 PM

Answers

  • 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

    • Proposed as answer by Xi Jin Monday, October 24, 2016 8:02 AM
    • Marked as answer by GaryMha Monday, October 24, 2016 4:42 PM
    Friday, October 21, 2016 5:46 PM

All replies

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

    A Fan of SSIS, SSRS and SSAS

    Friday, October 21, 2016 5:01 PM
  • 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.



    • Edited by GaryMha Friday, October 21, 2016 5:12 PM
    Friday, October 21, 2016 5:07 PM
  • So you want to display all data and then added the calculated data at the bottom?

    A Fan of SSIS, SSRS and SSAS

    Friday, October 21, 2016 5:18 PM
  • Yes, I want to display each row and then a subtotal for the offer. 
    Friday, October 21, 2016 5:20 PM
  • 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

    • Proposed as answer by Xi Jin Monday, October 24, 2016 8:02 AM
    • Marked as answer by GaryMha Monday, October 24, 2016 4:42 PM
    Friday, October 21, 2016 5:46 PM