SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Aggregate in lookup


Aggregate in lookup

Author
Message
SceloM
SceloM
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 190
Hi All

I am using SSRS 2008 R2 and struggling with aggregation inside the lookup function.

I found the below code which perfectly:

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



and in my tablix I have :
=Code.SumLookup(Lookupset(Fields!CLUSTER.Value, Fields!CLUSTER.Value, Fields!Q1_Dismissals.Value, "Dismissals"))



which returns the correct result but I now want the sum of all the results.
My result look like this:
Cluster Dismissals
A 3
B 5
C 1

I would like to have results which look like the below but the total line is coming out wrong (I just get the 3).
Can anyone see what I doing wrong and advise if it is possible to get the total?
Do I need to change the custom code itself or add a new custom code?

Cluster Dismissals
A 3
B 5
C 1
Total 9


I appreciate your assistance.
SceloM
SceloM
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 190
I find what the problem was.
The code above works perfeclty when I select each parameter individually, problem occurs when all parameters are selected at once which occurs when the report first loads (this is because the lookup does not find the matching records).
So I will make the "All" parameter and handle its case.

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search