Data Grouped on Aggregate Function

[Yavor]

Hello,

I want to design a master-detail report with the main select from the query below as the master and the subselect as the detail. I set up the report data source to use the subselect query and I have to do the outer grouping on the report level. However, I can't figure out how to achieve this behavior in Telerik Reporting, since it doesn't allow aggregate functions in the group by clause and I need to group by SUM(Amount). Is it possible to use alias, as I do in the T-SQL query?

SELECT sub1.ID, SUM(sub1.Value) AS Value, sub1.Amount
FROM
    (SELECT ForeignID AS ID, Value, SUM(Amount) AS Amount
    FROM Tbl
    GROUP BY ForeignID, Value) sub1
GROUP BY sub1.ID, sub1.Amount

[Elian]

Hi Yavor,

You cannot group by SUM(Amount). Even in a regular SQL Query you group by some values and select the aggregates for the rest. If your data-source is the inner query:

SELECT ForeignID AS ID, Value, SUM(Amount) AS Amount
FROM Tbl
GROUP BY ForeignID, Value

Then the report grouping expressions would be (2 expression):

= Fields.ID
= Fields.Amount

The "SUM(Amount)" is already produced by the SQL Query.
All the best,
Elian
the Telerik team

[Yavor]

Hello,

Everything you said is correct, but the suggested solution works only if I have a two-level grouping. In my business case, I actually have three, and even four, levels and my original query looks similar to:

SELECT sub2.ID, SUM(sub2.Value1) AS Value1, sub2.Value2, sub2.Value3
FROM
    (SELECT sub1.ID, sub1.Value1, SUM(sub1.Value2) AS Value2, sub1.Value3
     FROM
         (SELECT ForeignID AS ID, Value1, Value2, SUM(Value3) AS Value3
         FROM Tbl
         GROUP BY ForeignID, Value1, Value2) sub1
     GROUP BY sub1.ID, sub1.Value1, sub1.Value3) sub2
GROUP BY sub2.ID, sub2.Value2, sub2.Value3

So, the SQL Server grouping will do the job for the first two layers, but what about the other? I want to point out that my goal is to display data from ALL the groups in the report by using either report groups or table with grouping. I hope that my question is clearer now.