Total of LOOKUP function in SSRS

Question

For each group in SSRS (Countries, cities...) I calculate sales divided by exchange rates:

Sum(Fields!Net_Sales_Amount.Value)/LOOKUP(Fields!Country.Value, Fields!Country.Value, Fields!RATE.Value, "Currency")

How I can create Total of this sum (sum for last group - countries)?

Each solution that I found show how to do it only for alone LOOKUP function. I need to divide field by it.

Here is layout of my report:

and structure:

To calculate sales for country I just need to sum sales and divide it by LOOKUP function, but I couldn't do it for Total. I need to some below values.

Do you want to total each sum(sales)/lookup(value) or do you want to divide the total of sum(sales) by the total of all lookup(values)? — iamdave
I need to divide each sales before sum (I have one, different value for each country) — Testtest11
Please add an actual screenshot of your report and not a recreation of it in excel... — iamdave

iamdave iamdave · Accepted Answer · 2016-12-21T10:36:49

I think you just need to change which data you are summing together, as it looks like you are simply finding exchange rates?

If this is the case, you should be able to just use

=Sum(Fields!Net_Sales_Amount.Value/LOOKUP(Fields!Country.Value, Fields!Country.Value, Fields!RATE.Value, "Currency"))

in your total textbox, which needs to be outside the group you are using to show the individual values.

If you want to lookup a lot of values and then sum them all together however, this is a little more complex.

Rather than using lookup, you will need to use lookupset, which uses the same syntax but can return more than one value. There is, however, no built in function that can sum these returned values up, as the values that get returned in the lookupset function are actually objects rather than numbers. As such, you will need to use custom code.

Open up your Report Properties and then the Custom Code window, into which you can paste the following:

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

This can then be used in a textbox as an expression:

=Code.SumLookup(LookupSet(Fields!Country.Value, Fields!Country.Value, Fields!RATE.Value, "Currency"))

which you can use alongside other numeric values for aggregations or calculations.

Total of LOOKUP function in SSRS

1 Answers