Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Aggregate in lookup Expand / Collapse
Author
Message
Posted Tuesday, July 16, 2013 7:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 39, Visits: 180
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.

Post #1474137
Posted Wednesday, July 17, 2013 7:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 39, Visits: 180
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
Post #1474606
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse