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

1 Answer 9 Views
Grouping Report Designer (standalone) Table
Michael
Top achievements
Rank 1
Iron
Iron
Michael asked on 02 Jun 2025, 08:11 PM

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. 

1 Answer, 1 is accepted

Sort by
0
Petar
Telerik team
answered on 05 Jun 2025, 03:07 PM

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

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Tags
Grouping Report Designer (standalone) Table
Asked by
Michael
Top achievements
Rank 1
Iron
Iron
Answers by
Petar
Telerik team
Share this question
or