How do I SUM distinct results of a grouped table??

[Michael]

I have a table that is grouped by the field CellWCCode. The SQL Query is returning multiple rows (one each for the child item in the BOM), so for example, I might get 5 lines per CellCode. The field StdLabCost returns the one item for each grouped item, but now I want to total the StdLabCost for all of the CellWCCode. I have not figured out how to Sum all of the distinct values.

I attached sample data and the report. 

[Petar]

Hi Michael,

Thank you very much for providing the report file along with the sample data.

Given the limitation that aggregate functions cannot be nested, a potential workaround I can think of is to use custom user functions. Custom user functions are essentially static methods of a class from a Class Library project. Therefore, the implementation of the summing logic is entirely up to you. For example, you could create a custom user function like this:

namespace UserFunctions
{
    public class Functions
    {
        public static float SumDistinct(object[] values)
        {
            float sum = 0;

            foreach (var value in values)
            {
                sum += float.Parse(value.ToString());
            }

            return sum;
        } 
   }
}

In the report definition, the function may be called like this:

= SumDistict(AllDistinctValues(Fields.StdLabCost))

As the custom user function is technically not an aggregate function, it passes validation and executes correctly.

For details on how to extend the Report Designer with custom functions, please refer to the Custom User Functions Explained - Telerik Reporting article.

I hope this helps. Please let me know if you have any further questions.

Regards,
Petar
Progress Telerik