Best way to group using sql

[KFR]

I have a reasonably large dataset that I need to group over a few levels and summarise. I started just with a straight SQL connection getting all the relevant columns for all records and, while the sql returning immediately, the Report Engine timed out trying to group and calculate.

I changed it up and changed the sql to group by all the relevant levels and to even summarise in the sql so it was now just a matter of displaying values. Again, the query returned almost immediately, but the report engine times out.

Is there a best practice for grouping using SQL? Can I tell the template that the data is already grouped, or is there some other way for the template to automatically group using sql like filtering does?

[Lech Kulikowski]

Hello,

You can group and sum data as you need in the sql query and then use only DataBand component to show data without grouping in the report.

Thank you.

[KFR]

But if we have more than one layer of grouping that's not going to work. For instance:

Group 1
Group2
Totals

With report grouping would be:

A1
B1
total
B2
total
A2
B1
total

etc.

But if I just used the data band and grouped in sql I would end up with:

A1
B1
Total
A1
B2
Total

So I would still need to do some grouping in the template. Its better than nothing at all though, I suppose.

[Lech Kulikowski]

Hello,

You can use GruopHeader bands for grouping data in the report.

Thank you.

[CWS116]

Hi

My report is built like these:

GroupHeaderBand1

GroupHeaderBand2
-DataBand1 (filtered type 1)
GroupFooterBand2

GroupHeaderBand3
-DataBand2 (filtered type 2)
GroupFooterBand3

GroupHeaderBand4
-DataBand3 (filtered type 3)
GroupFooterBand4

GroupFooterBand1

OR

GroupHeaderBand1

HeaderBand2
-DataBand1 (filtered type 1)
FooterBand2

HeaderBand3
-DataBand2 (filtered type 2)
FooterBand3

HeaderBand4
-DataBand3 (filtered type 3)
FooterBand4

GroupFooterBand1

But Neither of these works
Problem is that the GroupHeaderBand1 just grouping the DataBand1 and other databands don't grouping. How can I fix this??

[Lech Kulikowski]

Hello,

In that case, you should use master-detail construction. Instead of the GroupHeaderBand1, you should use master DataBand.

Thank you.

[CWS116]

Perfect, thank you!

[Andrew]

You are welcome!