Is Aggregation based on RowNumber possible?

Question

Is Aggregation based on RowNumber possible?

Charmer

SSChampion

Points: 14903
More actions
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

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

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

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

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)

Charmer SSChampion Points: 14903 More actions · Answer 2

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

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 3

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.

Charmer SSChampion Points: 14903 More actions · Answer 4

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

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 5

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.

Charmer SSChampion Points: 14903 More actions · Answer 6

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