Adding LookupSet expression on any column in SSRS adding duplicate row in the result

Question

I have this data in report.

Then I want to sum all the values from column D per value of column B (group by col B). I have this this expression.

Code.SumLookup(LookupSet(Fields!B.Value,Fields!B.Value,Fields!D.Value,"dsData"))

I have this custom function in report code to group values based on key and do sum.

Function SumLookup(ByVal items As Object()) As Decimal  
If items Is Nothing Then  
Return Nothing  
End If  
Dim suma As Decimal = New Decimal()  
Dim ct as Integer = New Integer()  
suma = 0  
ct = 0  
For Each item As Object In items  
suma += Convert.ToDecimal(item)  
ct += 1  
Next  
If (ct = 0) Then return 0 else return suma   
End Function

After adding this expression in column E, i am getting result as below.

In this though I am correctly getting sum in each row, row 3 is getting removed and row 2 is coming as duplicate. I am unable to understand this behavior.

I have rdlc file but same beaviour i see with SSRS.

#rdlc #ssrs

I see same behavior with using Join function which I used to debug. Join(LookupSet(Fields!B.Value,Fields!B.Value,Fields!D.Value,"dsData"),",")

I am expecting result as

I could simulate this in report builder using "ENTER DATA" data source in which I have added this static data.

Please help me to understand why this is happening and how can I achieve this expected result in rdl.

Answer 1

Although you sample data in the first image (please post data as text in future) does not match images further down, I have replicated the issue. I've actually seen this kind of thing on rare occasions where values are repeated from the row above under very specific circumstances, however, I've never seen a solution posted.

In your case though, you might not need to use the VB function at all. This assumes that the data is fairly simple and you can add a group (even though it's not actually displayed).

First I recreated your sample data (or a version that matched your expected outcome further down). This is the dataset query I used. I added more data to demonstrate this working across more than 1 group of records.

declare @t table (A int, B int, C int, D decimal(10,2))
insert into @t values 
(1234, 412, 1, 6523.58),
(1234, 412, 2, 6583.58),
(1234, 412, 3, 4038.64),
(1234, 999, 1, 3000),
(1234, 999, 2, 4000),
(1234, 999, 3, 5000)

SELECT * FROM @t

Next I added a table and dropped the 4 columns on it, finally I added a fifth column, that we will use for our expression.

Now we have our report with the default row group (Details). I right-clicked the Details rowgroup and choose "Add Group"==> "Parent Group", and selected field "B" to group by. I renamed the group to "myBGroup". When the group is added, a new column is inserted automatically to the left of the table, delete this column and when prompted choose "Delete Column Only".

Your report design should look something like this (I added the "E" manually to the header row)

Note we have the detail and group icons on the table row, we have the "Details" and "myBGroup" row groups in the row group pane and our addiiotnal column which is currently blank.

Now all we have to do is use the following expression in our last column.

=SUM(Fields!D.Value, "myBGroup")

This just sums the data within the context stated, which in the case is "myBGroup", the name of our row group.

The final output looks like this...