SSRS - How to Sum values on a LookUpSet expression

Question

Hi I have a column that uses a lookupset expression =Join(LookupSet(Fields!ReportUNC.Value, Fields!ReportUNC.Value, Format(Fields!cntSelfService.Value, "###,#######0"), "ExecutionCount")).I'm getting an incorrect parameter when I sum that expression to =Join(Sum(LookupSet(Fields!ReportUNC.Value, Fields!ReportUNC.Value, Format(Fields!cntSelfService.Value, "###,#######0")), "ExecutionCount")). The column to sum is cntSelfService. Please advise.

Answer 1

You have a few different issues with your expression.

1. When you use the FORMAT function, the result is a string, not a number.
2. JOIN is used to concatenate strings from a table into a single string which wouldn't help your issue.
3. SUM will not work with a LookupSet

Unfortunately, there's not a built-in way to sum values from a LookupSet.

Luckily, users have had this issue for a while and someone created a function in Visual BASIC SumLookUp that will add the values from a lookupset. You add the code in the Report Properties --> Code tab.

Your expression would be:

=CODE.SumLookup(LookupSet(Fields!ReportUNC.Value, Fields!ReportUNC.Value, Fields!cntSelfService.Value, "ExecutionCount"))

See the code in: Need help in calculation using two Datasets using Expression SSRS