Database Collation and Linking

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 DataSettings properties can be configured to control the internal field link comparison. Applicable properties are CollationType, IsCaseSensitive, NullCollationType, and GuidCollationType.

Designer.DataSettings.CollationType and IsCaseSensitive properties specify how strings are compared:

ctASCII

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

ctANSI (use Windows Locale)

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

ctBinary

a. IsCaseSensitive = True  –> Compare Ord(Char) Values
b. IsCaseSensitive = False –> Compare Ord(UpperCase(Char)) Values

ctVariant

a. IsCaseSensitive = True  –> VarCompareValue
b. IsCaseSensitive = False –> VarCompareValue

Designer.DataSettings.NullCollationType specifies whether Null string values are collated first or last

Designer.DataSettings.GuidCollationType specifies how Guid field values are compared.

gcMSSQLServer – compare last 6 bytes
gcGuid – compare all bytes
gcString – compare string values

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 DataSettings Collation properties are assigned to 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.