ssrs-2008ssrs-2008-r2

SSRS - How to Sum values on a LookUpSet expression


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.


Solution

  • 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