How to 'Link' queries on End-User Data Tab

rbuser

Greetings all:

So far I've used data modules in my executable for providing data pipelines
to any reports, so I handle any master-detail data for subreports at the
query level. I now have to walk a client through creating a linked subreport
(i.e. Master / Detail) entirely using the Report Designer, where the query
and data pipeline appear to be one entity.

We have several queries on the data page, and they are 'linked' using the
link button. Each of the dependent queries are sorted by the field on which
they are linked (and one additional field on which they're subtotaling).
There's no way to use a parameter in RB's query designer, so I'm assuming
the data restriction is handled at the pipeline level rather than within the
query. The detail subreports, however, don't seem to have their data limited
to the current record in the master query. Each of the subreports is set to
the appropriate query/pipeline for their data. The parent (master) report's
data pipeline is set to the master query.

Am I missing a step here? I can't seem to find any other locations that
refer to anything having to do with a master-detail relationship, and I
don't see anything in the documentation.

Thanks for your help. ReportBuilder has been and is an excellent tool.

Phil Frank

nardmoseley

Run the RBuilder\Demos\EndUser\ReportExplorer example - it contains a folder
that has examples of reports running off of linked queries.

You can link SQL DataViews via RB's Data workspace visually dragging from
the detail field to the master field OR by using Link button/menu option on
the detail dataview (as you have found).

When you link SQL DataViews via RB's Data workspace, special linking SQL is
generated for the detail query. You can view this SQL by positioning the
mouse over the detail dataview and selecting Ctrl + Left Mouse. The detail
query must be ordered by the same fields as the master plus the linking
fields. RB will generate linking SQL to ensure this occurs. Therefore you do
not need to worry about ordering the detail data on the linking fields.

With RB's datapipeline linking, the detail query will fire only a single
time - there is no parameterized query. This provides faster performance
than Delphi's TQuery linking.

The datapipelines contain traversal logic that will filter the detail
results based upon the current master record.

I do not know why your reports are not working properly. The article below
may be applicable.

Try creating some simple examples using the
RBuilder\Demos\EndUser\ReportExplorer project. Try to create a similar
report that demonstrates the issue you are encountering. Then save the
report to an .rtm file and e-mail in zip format to
support@digital-metaphors.com and we can check it out here.

-------------------------------------------
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

rbuser

> You can link SQL DataViews via RB's Data workspace visually dragging from
on

Actually I had discovered both methods.

is

This was a great tip, being able to see that the SQL code that was being
generated actually *was* correct.


The combination of the article and playing with the demo helped. Knowing how
it was supposed to function gave me the confidence to rip my report apart
and start over. Even though I rebuilt it the same way, the second time
around it worked as expected. Who knows what the problem was the first time,
except that my not knowing how it was supposed to function meant I couldn't
pinpoint whether it was something I'd done or was something the report was
doing.

Thanks for the direction.

Phil Frank