Hi How would it be possible to do something like this? sum(LookupSet(Fields!ENT_AGE.Value, Fields!AGE_AT_PERIOD_END.Value, Fields!PD_REGULAR_AFTER_EXCESS.Value, "Claims"),"Chart4_CategoryGroup")

Please explain what exactly you want to do. If you want to know more about how to use lookupset read this article by Stacia Misner http://blog.datainspirations.com/2010/04/07/sql-server-2008-r2-reporting-services-look-up-part-iii/

Please explain what exactly you want to do. If you want to know more about how to use lookupset read this article by Stacia Misner http://blog.datainspirations.com/2010/04/07/sql-server-2008-r2-reporting-services-look-up-part-iii/

Hi, This can definitely be done and is supported. The following is a trivial example to Sum the lengths of Employee names that I have used to test this feature: =Sum(Lookup(Fields!EmployeeID.Value,Fields!EmployeeID.Value,Fields!FirstName.Value,"Employees").ToString().Length) Malcolm Stewart

Hi You take the Lenth of the Frstnames of the employees, I need to use the actual values (monetary values) in the array. How do I sum that? When I use the equation in Chart as stated above, it returns blank. The error warning states "Warning 1 [rsAggregateOfInvalidExpressionDataType] The Y expression for the chart ‘Chart4’ uses an aggregate function with an expression that returned a data type not valid for the aggregate function."

Hi You take the Lenth of the Frstnames of the employees, I need to use the actual values (monetary values) in the array. How do I sum that? When I use the equation in Chart as stated above, it returns blank. The error warning states "Warning 1 [rsAggregateOfInvalidExpressionDataType] The Y expression for the chart ‘Chart4’ uses an aggregate function with an expression that returned a data type not valid for the aggregate function."

Hi, That is because the Lookup function basically returns an Object and not an Integer or Decimal, since the value could be null. The .ToString() in my sample takes care of null values for the purpose I put it to. However, you can use the CDec() function to cast to a Decimal, if you're sure there will be no misses in the lookup. You could also write a custom function that returns a Decimal and returns 0 for null values. e.g. Public Function ToDecimal(ByVal InValue As Object) As Decimal If IsNumeric(InValue) Then Return CDec(InValue) Else Return 0 End If End Function =Sum(Code.ToDecimal(LookupSet(Fields!ENT_AGE.Value, Fields!AGE_AT_PERIOD_END.Value, Fields!PD_REGULAR_AFTER_EXCESS.Value, "Claims")) ,"Chart4_CategoryGroup") Malcolm Stewart

Hi, That is because the Lookup function basically returns an Object and not an Integer or Decimal, since the value could be null. The .ToString() in my sample takes care of null values for the purpose I put it to. However, you can use the CDec() function to cast to a Decimal, if you're sure there will be no misses in the lookup. You could also write a custom function that returns a Decimal and returns 0 for null values. e.g. Public Function ToDecimal(ByVal InValue As Object) As Decimal If IsNumeric(InValue) Then Return CDec(InValue) Else Return 0 End If End Function =Sum(Code.ToDecimal(LookupSet(Fields!ENT_AGE.Value, Fields!AGE_AT_PERIOD_END.Value, Fields!PD_REGULAR_AFTER_EXCESS.Value, "Claims")) ,"Chart4_CategoryGroup") Malcolm Stewart

Thanx for the assistance. It still doesnt work but I don't have time to debug this, two graphs next to each other will have to do.

Thanx for the assistance. It still doesnt work but I don't have time to debug this, two graphs next to each other will have to do.

Hi, Sorry, digging up a bit of an old thread here, but this came up as a search result and was actually pretty relevant to what I'm trying to do. Background: Got one data source and 2 datasets - both are very similar, but one is for one week's worth of sales data, and one is for a cumulative period. I am now trying to work the variances between the one week, and an average week from the cumulative period. Tried using the above from Malcolm to allow me to sum up my LookupSet, but now I am just getting 0 values (I've kept the formula simple for now and just asked it to sum up the sales from one dataset). This suggests to me that the sales values are not passing the IsNumeric test, so I am just summing up zeros. The sales values are numeric (can sum them up within their own dataset). And when I attempt a straight Lookup instead of a LookupSet, it correctly returns the sales values from the first instance (the first week), which is what I'd expect to see. Any ideas about how I can fix this? Thanks

Hi, Sorry, digging up a bit of an old thread here, but this came up as a search result and was actually pretty relevant to what I'm trying to do. Background: Got one data source and 2 datasets - both are very similar, but one is for one week's worth of sales data, and one is for a cumulative period. I am now trying to work the variances between the one week, and an average week from the cumulative period. Tried using the above from Malcolm to allow me to sum up my LookupSet, but now I am just getting 0 values (I've kept the formula simple for now and just asked it to sum up the sales from one dataset). This suggests to me that the sales values are not passing the IsNumeric test, so I am just summing up zeros. The sales values are numeric (can sum them up within their own dataset). And when I attempt a straight Lookup instead of a LookupSet, it correctly returns the sales values from the first instance (the first week), which is what I'd expect to see. Any ideas about how I can fix this? Thanks