Sorting with DADE

rbuser

Hi. I need to create a report in DADE (pipelines inside rtm file) and I am
having a problem when trying to sort data.
I have one "master" pipeline and some "detail" pipelines connected to the
master (details has on1 pointer to the master's primary key). The report
prints fine as long as I don't put any sorting on any of the pipelines.
I've traced the generated sql and for each pipeline it generates the
following:

select HeaderTable.field1, HeaderTable.field2, HeaderTable.field3
from headertable
inner join DetailTable1 on HeaderTable.PrimaryKey =
DetailTable1.PointerToHeaderKey
Order By HeaderTable.HeaderCode, DetailTable1.DetailCode

select DetailTable1.Field1, DetailTable1.field2, DetailTable1.field3
from HeaderTable
inner join DetailTable2 on HeaderTable.PrimaryKey =
DetailTable2.PointerToHeaderKey
Order By HeaderTable.HeaderCode, DetailTable2.DetailCode

and so forth...

results with NO sorting:
Header 1
Table 1
DetailTable1.fld1 ###
DetailTable1.fld2 ###
Table 2
DetailTable2.fld3 ###
DetailTable2.fld4 ###
Table 3
DetailTable3.fld5 ###
DetailTable3.fld6 ###

Adding sorting to header pipeline:
Header 1
Table 1
DetailTable1.fld1 ###
DetailTable1.fld2 ###
DetailTable2.fld3 ###
DetailTable2.fld4 ###
DetailTable3.fld5 ###
DetailTable3.fld6 ###
Table 2
Table 3

In short, how can I use "sorting" in DADE??? Help please.
Thanks
Gus.

nardmoseley

-------------------------------------------
Tech Tip: Database Collation and Linking
-------------------------------------------

Overview
---------

Linking DataViews in the ReportBuilder Data workspace (DADE), requires that
the detail data be sorted by the linking fields. When DataViews are linked,
DADE generates special linking SQL for the detail DataView. The linking SQL
(Magic SQL) can be viewed by positioning the mouse over the DataView window
and pressing Ctrl + Left Mouse button. The results of the Magic SQL query
can be viewed by pressing Shift + Left Mouse button.

ReportBuilder's DataPipeline architecture includes support for linked data
traversal. Whenver the record position of the master data changes, the
traversal logic compares the master and detail field link values and then
determines where to locate the record position for the related detail data.
The linking logic will only work properly if the Database engine's collation
of the detail query result set is in sync with ReportBuilder's internaly
field link comparison logic.

Typically the database engine can be configured to use various language
drivers which control the collation order used to sort query results.

ReportBuilder contains the two properties that can be configured to control
the internal field link comparison:

Designer.DataSettings.CollationType (ctASCII or ctANSI) and
Designer.DataSettings.IsCaseSensitive (True/False). Using these properties
results in one of 4 Delphi string comparison routines being used to compare
string data:

1. ctASCII

a. IsCaseSensitive = True --> CompareStr
b. IsCaseSensitive = False --> CompareText

2. ctANSI

a. IsCaseSensitive = True --> AnsiCompareStr
b. IsCaseSensitive = False -->AnsiCompareText


The current values for the Designer.DataSettings are used to create new
DataViews. The DataView's internal TdaSQL object saves the
Designer.DataSettings as part of the dataview's definition. The
CollationType and IsCaseSensitive values are assigned any DataPipeline field
links created for the dataview.


Designer.DataSettings -----> QueryDataView
|_ SQL object
|_ DataPipeline.Links[]: TppMasterFieldLInk


Therefore to modify the DataSettings once a query has been modified requires
that the SQL object and the DataPipeline.Links be updated.





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