multiple tables

rbuser

I have three tables that have a common ID with a higher level table. I want
to print my report in the format below:

Master Table Record #1
Sub table #1 records matching Master ID #1
Sub table #2 records matching Master ID #1
Sub table #3 records matching Master ID #1
Master Table Record #2
Sub table #1 records matching Master ID #2
Sub table #2 records matching Master ID #2
Sub table #3 records matching Master ID #2

I can only get my data to look something like this:

Sub table #1 records matching Master ID #1
Sub table #1 records matching Master ID #2

Sub table #2 records matching Master ID #1
Sub table #2 records matching Master ID #2

Sub table #3 records matching Master ID #1
Sub table #3 records matching Master ID #2

The report I have now that is similiar to above I did using three sub
reports on a main form.

I have tried setting the master/detail properties in the data table but then
all I get is duplicate data and its still sorted like above. I looked at
the sample reports and they all seem to have an heirarchy of tables
(customer/orders/details/) where my three sub tables are at the same level.

To make things more complicated, each sub table has a subordinate table with
information in it. But if I can get help with the first level I think I
will be there.

Thanks for any help,

Karen

digitalmetaphors

First, get the data linking setup correctly. Use TDBGrids connected to the
datasources. Use TTable linking to make sure you have linked data. Run the
project, at runtime, you should be able to click on the master records in
the grids and the detail grids should reflect the correct linked detail
dataset. Once you get htis working, then connect the report to
datapipelines. Make sure you do NOT set the datapipeline linking properties.
You should be abel to have many same level detail subreports. They just have
to be located in the same master detail band with their ShiftRelativeTo
properties set if they should print in order. If you want side by side
details, then set the Subreport.ParentWidth property to false and resize the
widths of the subrepotrs so they can fit next to each other in the same
band. This is shown in the main reports demo # 73.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Sorry Jim. I guess I need some clarification. I got it working somewhat at
one time because I set the pipeline's master/detail pipelines instead of the
actual ADOtables like I was initially. If I understand your message, then
that is wrong as well. Should I be setting the master/detail with the
pipelines, at the table level or both?

It seems like whatever I set in the datamodule design-time editor itself
never stays, so I am trying to do it in code now to ensure its stays set.
My code looks similiar to below. Now nothing is working so I think the
MasterFieldLinks seutp is wrong in the code. Could you please help me here?

//setup the plan master/detail connections
//
dataMod.pipPipe.MasterDataPipeline := datamod.planPipe;
dataMod.pipPipe.MasterFieldLinks := 'pipPlanID;plnPlanID';

dataMod.tipPipe.MasterDataPipeline := datamod.planPipe;
dataMod.tipPipe.MasterFieldLinks := 'tipPlanID;plnPlanID';

dataMod.mipPipe.MasterDataPipeline := datamod.planPipe;
dataMod.mipPipe.MasterFieldLinks := 'mipPlanID;plnPlanID';

dataMod.seqPipe.MasterDataPipeline := datamod.planPipe;
dataMod.seqPipe.MasterFieldLinks := 'seqPlanID;plnPlanID';

//setup the truss/misc/package connections
//
dataMod.trussPipe.MasterDataPipeline := datamod.tipPipe;
dataMod.trussPipe.MasterFieldLinks := 'trsName;tipTrussName';

dataMod.miscPipe.MasterDataPipeline := datamod.mipPipe;
dataMod.miscPipe.MasterFieldLinks := 'mscName;tipMiscName';

dataMod.packagePipe.MasterDataPipeline := datamod.pipPipe;
dataMod.packagePipe.MasterFieldLinks := 'pckName;pipPackage';

digitalmetaphors

If you want to use master detail data pipeline linking then you have to make
sure that you do not use ADO dataset linking. Then you have to order the
detail datasets by the master linking field and the master's ORDER BY.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

My first choice was getting the master/detail to work at the table level.
After way-too-long, I think I finally got it. My duplicate data seemed to
go away by making all my tables inactive, then setting them to active again.
If you know why this is, please let me know. My data grids always work,
but sometimes the report on the same exact data can be very flakey. Running
the report several times in a row can have it appear fine, then the next
time, no records are found for one of the tables. It has been very
frustrating.

My question to you is, the way it is somewhat working is by using the
pipelines pointing at my ADO tables only. If I base a subreport off a query
of that table, then I'm getting all the data again, not just the records
matching the master ID. How do I go about using a query when I need to
select records that are > 0 ect... Do I have to filter it at the table
level and never use Queries in the Report Data tab?

Sorry to be a pain, and thanks again for you help,

Karen

digitalmetaphors

You need to link the datasets, either use TADOTable linking (MasterFields
and MasterSource) only or specify the linking relationship on the pipelines
and ordering the data yourself manually to support the data traversal such
that the details are ordered by the master datasets. Don't mix the two
approaches. If the grids always work, then that should be enough. Make sure
you disconnect/nil the datapipeline MasterDataPipeline and MasterFieldLinks
properties. That should work as RB traverses the data using the TDataset
routines, just as those which are called when you navigate using a TDBGrid.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

I'm not mixing the two approaches. I am using the the linking only in my
tadoTable by setting the masterfields and master sources. I have turned off
all linking through the pipeline directly. The data is written (sorted) by
the master id #. This all works for the most part, but as soon as I use the
data tab in the report generator and build a query based off of one of these
tables, the master/detail stops.

Imagine that you have an ORDERS table that is master to the ORDER DETAILS
table. In my case everything works as long as I base my reports off the
ORDERS and ORDER DETAILS tables respectively. I go into the DATA tab of my
subreport and create a query based off my ORDER DETAILS where only orders
from this year are shown. If it matched my scenario, the act of using a
query would show all ORDER DETAILS records for all orders, not just the
current order record. Does this make sense?

Thanks,

Karen

digitalmetaphors

I would use DADE for all of the datasets and use visual query dataview
linking and delete the ADO components on the form.

If you go into DADE (the Data tab or Data Access Development Environment),
it will create a query that is independent of what you have on the form. It
should return all rows given the SQL that it created. View the SQL tab of
the query designer to see the SQL that is submitted to the server. An
alternative is to create another TADOTable or TADOQuery on your form to
print this subdetail data instead of using DADE for only some of the
datasets.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

I'm lost. I tried to use the DADE completely in the beginning and it didn't
work because I have three tables at the same level (similiar to ORDER
DETAILS) and I would get duplicate data by linking them in the same query to
the same ID. I was forced to print each table in its entirety this way.
This is why I had to go master/detail right? I can't delete the ADO
components on the form because they are what my pipelines are linked to.
I'm not sure what you mean by 'visual query dataview' unless this is just
act of setting up the query in the DADE.

What am I missing here?

Karen

digitalmetaphors

Setting up the query in DADE is visual. You shouldn't get duplicate data.
DADE should work. Did you drag from the detail field to the master field?
This is the way the linking works. You have to start the drag operation in
the detail dataview and drag to the master dataview. That may have been the
problem. Templorarily set the datapipelines on the form to not be visible.
Then retry using DADE. Then make sure that the subreports are connected to
the correct detail dataviews. It should work. It works in tests here on our
SQL Server database using ADO in DADE. Can you recreate the problem using
the NorthWind Traders databse on SQL Server with ADO or another sample
database where you are using ADO?


Cheers,

Jim Bennett
Digital Metaphors

rbuser

the

That was it, you are awsome. Thank you for all your help and patience.
Much easier than messing with the master/detail settings.

Karen