Calc Field in a Group By's Clause

rbuser

Hi,

How do I do to use a Calculation Field in clause Group By? I want use it
direct in Report Builder (I guess its name is DADE). I work only with file
.RTM

For example, I would like to query a table of Orders. This table have 3
fields: o_id, o_date e o_value. I want to make the query below:

select
udf_month(o_date),
sum(price)
from
orders
where
(o_date between '2005-01-01' and '2005-03-31')
group by
udf_month(o_date)

The result would be:

Month Price
-----------------------------------
Jan $ 120,00
Feb $ 365,00
Mar $ 365,54


This query work fine in Firebird. But, I cannot put the field
"udf_day(o_date)" in Group By's clause. I already use Report Builder a lot
since 2003, and I thought it problem had fixed in the actual version.

How do I solve this problem? I need to make up a report that list the orders
by month!

Jeferson

nicocizik

Hi Jeferson,

The query designer in DADE does not allow grouping by a calculated field.
If your database supports this type of action, you will need to edit the SQL
manually if you would like to create a query like this. Note that this will
then prevent you from using the automatic linking in DADE with that
dataview. If you still need to link this dataset to another, you can do so
manually.

http://www.digital-metaphors.com/tips/LinkEditSQLDatviews.zip

--
Regards,

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

rbuser

Ok

I'm disappointed with RB. I thought this feature was adding in current
version (10.x). I have a lot of situations where is necessary to use calc
fields in group by clause.

I understanded about to edit the SQL manually, but if I do it, I wouldn't
manage the property "Automatic AutoSearch" in "Where" clause. I use this in
all my reports (they are about 400 reports)!

Do you know if will be possible add this feature (calc field in group by
clause) in RB 10? I guess it is a fault from RB. Almost all databases allow
calc field in group by clause (Firebird, Oracle, SQL Server and others
databases).

Thanks for attention!

Jeferson

nicocizik

Hi Jeferson,

Thank you for the suggestion. We will consider adding this feature for a
later release of ReportBuilder.

--
Regards,

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

rbuser

Thank you for consideration.

Have you have some forecast in what version will be released this feature?
Could it would be in RB 10.05?

Thank you.

Jeferson