Sorting on Calc Field

rbuser

Hi,

I was wondering if it is possible to sort on calc field (expression). I
have a table which has a datetime field (MySQL). I am trying to use
calcs to extract the year (using YEAR(TableName.DateField)) and month
(using MONTH(TableName.DateField)) and sort on those calcs.

The sorting does not seem to work when I sort on these expressions.

The generated SQL query is attempting to sort by a number rather than
the expression (or the expression alias).

Here is an extract of the SQL statement generated by ReportBuilder:

SELECT Activity.ActivityDate, Activity.Description, YEAR(ActivityDate)
YEAR_ActivityDate
FROM Activity Activity
ORDER BY 67

Thank you.

nardmoseley

For a 'calc field', the column position of the field as it appears in the
Select clause is used in the Order By

example:

Select CustNo,
Sum(AmountPaid)
from Orders
Order By 2

In the above statement, the Order By 2 refers to the calc field, which
appears as the 2nd field listed by the Select clause



--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

I see. However, for some reason I cannot join a detail pipeline to a
master pipeline that is sorted by a calc field.

nardmoseley

That is a limitation of the visual linking. DataPipeline linking requires
that the detail data be sorted by the linking field(s). The automatica
linking feature works by generating special linking SQL for the detail
query.

The solution for this case is to manully link the datapipelines via code.
Here is an example...

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



--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com