Using a datasource to filter a query, and how that effects pipeline Mast/Detail relationships

rbuser

I broke down a queary into three queries to permit using 3 pipelines to
create a Master/Detail-1/Detail-2.
Since I do not want ot bring over all the records in the details, I use the
master dataset and a filter in the detail-1 and the dataset of detail-1 as
the datasource for detail-2.

I placed three "sanity-check grids" on a form and all works as expected.
But when I line up the pipelines as per the tutorial
Master/Detail-1/Detail-2, I am not getting all the records. The
relationship the Master/Detail-1 is not the same order as the
Detail-1/Detail-2 relationship. This stems from the fact that the user may
not enter the data in date order, which is the output of the report.

To be honest, I have it all working now, but I did so by removing all
Pipeline bindings and handling them as independent entities. My question
is, what are the traversal rules of the pipelines in relation to queries.
All my datasets are returned prior to the report running. I am not sure why
RB cannot handle them.

nicocizik

Hi Larry,

I am a bit unclear how you are linking your datasets? Are you filtering the
datasets and linking the datapipelines? It is never a good idea to filter
the dataset as the report traverses the data. Also, be sure you are not
linking the actual queries and datapipelines at the same time. Check out
the following article for more information.


------------------------------------------------------
Tech Tip: Linking SQL Queries for Master/Detail Data
------------------------------------------------------

The following example shows two options for linking SQL queries to create a
master/detail relationship.

In this example, we are using Delphi's DBDemos data to create a
Customer/Order relationship. Thus we wish to link the Orders detail to the
Customer master.

I. Delphi Query Linking
------------------------

a. Set the detail TQuery.DataSource property to point to the master
query's TDataSource component.

b. In the SQL "Where" clause for the detail query use a ':' followed by
the linking field name from the master:

example
select *
from orders
where orders.CustNo = :CustNo

Now each time the master record position changes, the detail query will
automatically be refreshed with the correct result set.


II. RB DataPipeline Linking
-----------------------------

a. Set the detail DataPipeline.MasterDataPipeline to point to the master
DataPipeline.

b. Use the detail DataPipeline.MasterFieldLinks property to define the
linking relationship

c. In the SQL for the detail, retrieve all records and sort them by the
linking master field:

select *
from Orders
order by CustNo


Notes:

1. Using RB DataPipeline, each query is executed only a single time - thus
performance is much faster.

2. RB Professional and Enterprise Editions include a visual Data environment
for creating SQL queries, defining linking relationships, and creating
Ask-At-Runtime parameters. Using the RB tools you could create the above
linked queries in about 10 seconds.

--

rbuser

I am using
property DataSource: TDataSource;
Specifies the data source component from which to extract current field
values to use with same-name parameters in the query's SQL statement.

As best I can tell, my choices would be:

1.) Select M.*, D.*
From Detail D
Left Inner Join Master M on (D.MyID = M.MyID)
Where M.BegDate = :StartDate and
M.EndDate = :EndDate
Doing this would flatten my data, in which case, I don't know how to do a
Master/Detail report correctly.

2.) Select * {contains MyID as pk}
From Master
Where M.BegDate = :StartDate and
M.EndDate = :EndDate

Select * {contains MyID as fk}
From Detail

Now I can put my pipelines in place and do my report.

But the detail is hugh and I have nothing to filter it on. I only want
30 records from my Master, at most but I will end up retrieving all the
detail records because they can only be filled on MyID. There is no date
attribute.

3.) Select M.* {contains MyID as pk}
From Master M
Where M.BegDate = :StartDate and
M.EndDate = :EndDate


Select * {contains MyID as fk}
From Detail
Where MyID in :MyID // by setting the DataSource property, I filter
this query based on the the records returned from the first query.

I attach pipelines but do not add the M-D links.

As stated, this works and is much faster but is there a downside?

nicocizik

Hi Larry,

I strongly recommend that you do not use filtering with your datasets. If
you use filtering with a report, ReportBuilder will need to access your
datasets after every traversal, extremely slowing down performance. Your
best option would be to use datapipeline linking. In your examples, option
2 would be the closest to this. The only thing you would need to change is
the order of the Detail dataset to order by the Key of the master dataset.
For example:

Select *
From Detail
Order By fk.

This way ReportBuilder will not have to traverse the entire dataset,
increasing performance. Please see the following article for more
information on pipeline linking.


------------------------------------------------------
Tech Tip: Linking SQL Queries for Master/Detail Data
------------------------------------------------------

The following example shows two options for linking SQL queries to create a
master/detail relationship.

In this example, we are using Delphi's DBDemos data to create a
Customer/Order relationship. Thus we wish to link the Orders detail to the
Customer master.

I. Delphi Query Linking
------------------------

a. Set the detail TQuery.DataSource property to point to the master
query's TDataSource component.

b. In the SQL "Where" clause for the detail query use a ':' followed by
the linking field name from the master:

example
select *
from orders
where orders.CustNo = :CustNo

Now each time the master record position changes, the detail query will
automatically be refreshed with the correct result set.


II. RB DataPipeline Linking
-----------------------------

a. Set the detail DataPipeline.MasterDataPipeline to point to the master
DataPipeline.

b. Use the detail DataPipeline.MasterFieldLinks property to define the
linking relationship

c. In the SQL for the detail, retrieve all records and sort them by the
linking master field:

select *
from Orders
order by CustNo


Notes:

1. Using RB DataPipeline, each query is executed only a single time - thus
performance is much faster.

2. RB Professional and Enterprise Editions include a visual Data environment
for creating SQL queries, defining linking relationships, and creating
Ask-At-Runtime parameters. Using the RB tools you could create the above
linked queries in about 10 seconds.

--