view raw
Oleg Oleg - 10 months ago 126
SQL Question

How to use SUM along with LOOKUP function

In my SSRS report I have two tables: Losses(Table2) and Earned Premiums(Table 1), both coming from different datasets. I need to calculate ratios based on those Losses and Earned Premiums for each month and year. For that I used LOOKUP function and it works fine:

SUM(Fields!PaidLosses.Value) / Lookup(Fields!AccidentYearNum.Value & Fields!AccidentMonthNum.Value, Fields!YearStartRisk.Value & Fields!MonthStartRisk.Value, Fields!EarnedPremium.Value, "EarnedAllCoverages")

enter image description here

But now I need to calculate
. Which is Total Losses for each month and year divided by Total Earned Premium for each month and year.
Based on this article

I inserteded this Custome Code:

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
If (ct = 0) Then return 0 else return suma
End Function

And now I am using this expression:

=SUM(Fields!PaidLosses.Value) / Code.SumLookup(LookupSet(Fields!AccidentYearNum.Value & Fields!AccidentMonthNum.Value, Fields!YearStartRisk.Value & Fields!MonthStartRisk.Value, Fields!EarnedPremium.Value, "EarnedAllCoverages"))

But that doesnt give me correct answer for some reason.
enter image description here
What am I missing here?


Answer: The expression for the total in the 3rd matrix needs some adjustment.

=SUM(Fields!PaidLosses.Value) / Code.SumLookup(LookupSet(Fields!AccidentYearNum.Value, Fields!YearStartRisk.Value, Fields!EarnedPremium.Value, "EarnedAllCoverages"))

The total is rolling up to the year, so the months are no longer needed.