Post reply

Is Aggregation based on RowNumber possible?

  • June 30, 2016 at 12:54 am

    #319602

    Hi Friends,

    I am trying to SUM a field which has duplicates but want to add only the unique value at the bottom.

    I tried googling and found this link

    http://bidn.com/Blogs/userid/10/ssrs-use-row_number-to-resolve-datasets-with-different-levels-of-aggregation

    Can it be possible to do this in SSRS instead of T-SQL?

    Just curious...

    Thanks,
    Charmer

  • June 30, 2016 at 4:04 pm

    #1887980

    Good strategy using RowNumber to filter unique values. Try the below SSRS expression (SUM the field only if the RowNumber field has a 1):

    =SUM(iif(Fields!rownum.Value = 1, Fields!ProductCost.Value, Nothing)

  • June 30, 2016 at 8:58 pm

    #1887991

    But don't have a row number field coming from the dataset. That's the problem..even if I create a custom row number in tablix, I can't use that in the aggregate expression, right? This is where I got stuck.

    Thanks,
    Charmer

  • June 30, 2016 at 11:14 pm

    #1887996

    It should be possible to sum unique values using custom code.

    I've uploaded an example report file here. Here's how to recreate:

    Add a table with a detail row and a table footer row.

    In the detail row, add a column containing the below, where addProductCost is the name of a VB function (we'll add that in a moment), and ProductCost is your field name.

    =code.addProductCost(Fields!ProductCost.Value)

    In the table footer, add a column containing

    =code.getSumOfUniqueProductCosts()

    Right-click on an empty area of the report (not the report body), select Report Properites, Code, then paste the below and click OK.

    Public Dim productCostCollection As New Microsoft.VisualBasic.Collection()

    Public Function getSumOfUniqueProductCosts() As Long

    Dim uniqueProductCostCollection As New Microsoft.VisualBasic.Collection()

    Dim uniqueSum As Long

    Dim element As Long

    For Each element In productCostCollection

    If Not uniqueProductCostCollection.Contains(CStr(element)) then

    uniqueProductCostCollection.Add(CStr(element),CStr(element) )

    uniqueSum= uniqueSum + element

    End If

    Next element

    Return uniqueSum

    End Function

    Public Function addProductCost(ByVal productCost As Long) As Long

    productCostCollection.Add(productCost)

    Return productCost

    End Function

    Left-click on an empty area of the report (not the report body), and in the Properties sidebar, change the InteractiveSize to 0.

    This shows that it's possible to sum on unique values using SSRS without having a supporting RowNumber column in the underlying dataset, but it's not nice, and I think the inferred assumption that no two summed values will be exactly the same is dangerous. An improvement would be to modify the above example to correctly store and check for the unique key in the collection.

  • July 1, 2016 at 12:37 am

    #1888000

    Hi Andrew,

    Thank you so much for sharing your ideas. Unfortunately, I am not able to download the RDL. Could you please attach in here? I have visual studio 2008 version. Could you please attach the same rdl version in case your sample rdl is in higher version?

    If possible, can you provide me sample data to test?

    Sorry for the inconvenience

    Thanks,
    Charmer

  • July 1, 2016 at 2:12 am

    #1888010

    Neat - I didn't notice there's file attachment on the forums. See attached - I've included an XML data source in the report with a small sample of data and rebuilt it for SSRS 2008.

  • July 1, 2016 at 3:47 am

    #1888029

    My bad luck. I don't know whether the problem is with the attachment or with my machine...I am not able to download it. It says access denied. :ermm:

    Thanks,
    Charmer

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply