group by in aggregate query

rbuser

I can perform the following aggregate query in sql where I group by the
calculated field for Month and Year portion of a date. However, I cannot
figure out how to accomplish this in Data tab / Query Designer. I can
create the calculated fields but cannot figure out how to get the group by
them

select Month(ScheduledDate), Year(ScheduledDate), Count(Numbered) from
WoMaster
group by Month(ScheduledDate), Year(ScheduledDate)
order by Year(ScheduledDate), Month(ScheduledDate)

I named them SMonth and SYear for the alias but they do not show up in the
Group By tab for selection.

Any suggestions?

Thanks,
Bob

nicocizik

Hi Bob,

Grouping by non-aggregate calc fields (as in your example) is currently not
supported in ReportBuilder due to the fact that not all Databases
(especially in the past) support this action. We are strongly considering
adding this capability for the next release as it seems it is becoming more
widely supported by the latest DB's used by our customers.

The current alternative is to edit the SQL as text and manually add the
GroupBy clause yourself.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

When you say the alternative is to edit the SQL as text and manually add the
GroupBy clause, is this something the user can do in the report builder at
runtime or do we have to do it from Delphi?

Thanks,
Bob

nicocizik

Hi Bob,

At runtime if the TppDesigner.DatabaseSettings.AllowEditSQL property is set
to True, you have the ability to navigate to the SQL tab in the Query
Designer, right click and edit the SQL code manually.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

After all these years.. I did not know that.

Thanks,
Bob