none
Skip duplicate rows in subtotals

    Question

  • Hi All,

    I have a situation using SSRS 2008R2, where my detail rows have various values that need to be summed, however do to joins in the source query, some of the columns contain a mixture of the same values (that should not be summed in a subtotal), for example:

    Group1            Detailcol1         Detailcol2

    A                     1000               E

    B                      500                F

    B                      500                G

    C                      1000              H

    Total                3000

    When I sum (DetailCol1) I get 3000 as shown, however what I need is to be able to sum all the values except where Group1 is duplicate, so my value would be 2500.  The Previous function doesn't seem to work as it complains about scope and doesn't allow me to actually use dynamic scoping.

    Any suggestion on how I can do something like this?

    Thanks


    Sawyer

    Monday, October 14, 2013 8:07 PM

Answers

  • I would suggest to follow steps below:

    1. Create separate dataset for Total. Let's call it DS_Total.

    2. DS_Total dataset should only contain Group1 and Detailcol1 and to avoid redundant values it SHOULD NOT have Detailcol2 in Total DataSet. Make sure your query doesn't return duplicate values.

    3. Simply use expression where you require total as = Sum(Fields!Detailcol1, "DS_Total")

    • Marked as answer by DD Sawyer Tuesday, October 15, 2013 4:36 PM
    Tuesday, October 15, 2013 5:26 AM
  •  I'm actually trying to add the values in DetailCol1 not Detail Col2. So in your example, where you have [Sum(DetailCol1)] it should have the sum of the columns except where Group1 is duplicated.  Hopefully this is clearer.

    Hi Sawyer,

    I created the subreport for displaying DetailCol2 which is the most detail level in your sample data. Group B is repeated twice because this column has two unique values for Group B. So the subreport just displays the detail col2 and the main report's dataset will avoid detailcol2 in the resultset. Here is the snapshot of the subreport example I pasted above.



    If you do not want to go with subreport then the second option in my post above will work without having additional datset.

    --sIbu


    • Edited by cruellogic Tuesday, October 15, 2013 4:15 PM
    • Marked as answer by DD Sawyer Tuesday, October 15, 2013 4:38 PM
    Tuesday, October 15, 2013 3:51 PM

All replies

  • You can use a subreport just for DetailCol2 column and embed the subreport in the DeatilCol2 colum of your tablix in main report as shown below. 

    So you main report dataset will be a something like this

    SELECT Group1, 
    DetailCol1 
    FROM dbo.SSRSGroupTest WITH(NOLOCK)
    GROUP BY Group1, DetailCol1 

    And in your subreport you have to define a hidden parameter for group1 and connect it to your subreport dataset query like below. 

    SELECT Group1, 
    DetailCol1, 
    DetailCol2 
    FROM dbo.SSRSGroupTest WITH(NOLOCK)
    WHERE Group1 = @Group1

    And then in the main report connect the subreport parameter to the Group1 field by right clicking the subreport properties.

    OR

    You can have just one report and modify your dataset query something like below and then use the DetailCol1ForSum column for the Total expression.

    SELECT Group1, 
    DetailCol1, 
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY Group1 ORDER BY DetailCol1) = 1 THEN DetailCol1 ELSE 0 END AS DetailCol1ForSum ,
    DetailCol2 
    FROM dbo.SSRSGroupTest WITH(NOLOCK)
    

    ------------------------

    Please mark as answered if a post solves your problem


    --sIbu

    Monday, October 14, 2013 10:10 PM
  • Hi Cruellogic,

    Thanks for the response.  I'm actually trying to add the values in DetailCol1 not Detail Col2. So in your example, where you have [Sum(DetailCol1)] it should have the sum of the columns except where Group1 is duplicated.  Hopefully this is clearer.

    Thanks


    Sawyer

    Tuesday, October 15, 2013 3:25 AM
  • I would suggest to follow steps below:

    1. Create separate dataset for Total. Let's call it DS_Total.

    2. DS_Total dataset should only contain Group1 and Detailcol1 and to avoid redundant values it SHOULD NOT have Detailcol2 in Total DataSet. Make sure your query doesn't return duplicate values.

    3. Simply use expression where you require total as = Sum(Fields!Detailcol1, "DS_Total")

    • Marked as answer by DD Sawyer Tuesday, October 15, 2013 4:36 PM
    Tuesday, October 15, 2013 5:26 AM
  • Something like

    =SUM(IIF(RowNumber("Group1")=1,Fields!Detailcol1.value,0))

    Tuesday, October 15, 2013 5:30 AM
  • Hi Visakh16,

    This solution didn't work for me.  It throws an error "The Value expression ... contains an aggregate function (or Runningvalue or RowNumber functions) in the argument to another aggregate function (or RunningValue)..."


    Sawyer

    Tuesday, October 15, 2013 12:43 PM
  • Hi Samiullah,

    Your solution didn't quite work as is, but I think I can make it work with a little tweak.  I'll let you know what I come up with.


    Sawyer

    Tuesday, October 15, 2013 1:25 PM
  •  I'm actually trying to add the values in DetailCol1 not Detail Col2. So in your example, where you have [Sum(DetailCol1)] it should have the sum of the columns except where Group1 is duplicated.  Hopefully this is clearer.

    Hi Sawyer,

    I created the subreport for displaying DetailCol2 which is the most detail level in your sample data. Group B is repeated twice because this column has two unique values for Group B. So the subreport just displays the detail col2 and the main report's dataset will avoid detailcol2 in the resultset. Here is the snapshot of the subreport example I pasted above.



    If you do not want to go with subreport then the second option in my post above will work without having additional datset.

    --sIbu


    • Edited by cruellogic Tuesday, October 15, 2013 4:15 PM
    • Marked as answer by DD Sawyer Tuesday, October 15, 2013 4:38 PM
    Tuesday, October 15, 2013 3:51 PM
  • Oh, I see.  I didn't see it that way so Yes, your way may have worked.  I didn't get a chance to try it though, because I went with one of the other choices above.  I'll mark this as a correct answer along with the choice I used above, in case someone else would like to give it a try.

    Sawyer

    • Marked as answer by DD Sawyer Tuesday, October 15, 2013 4:33 PM
    • Unmarked as answer by DD Sawyer Tuesday, October 15, 2013 4:38 PM
    Tuesday, October 15, 2013 4:33 PM
  • This worked for me, I just had to do a lookup to make sure it was working against the correct data in the scope... Here's what I did:

    LookUp(Fields!Group1.Value,Fields!Group1.Value,Fields!DetailCol1.Value,

    "DS_Total")


    Sawyer

    Tuesday, October 15, 2013 4:36 PM