Newbie at Master Detail Relationship

rbuser

Hi
I am trying to create a simple master detail report using 2 query's. I tried
to create the report using the directions given in the tech-tips (Linking
SQL Queries for master detail reports) Both queries have a field called
territory

The master query is select * from table where report_Type='TT' order by
territory,report_Type
The detail query is select * from table where report_Type='BB' or
report_type='HO' order by territory,report_type

These sql string for these queries are dynamically created because each user
get a different table that is named after their territory.
These queries are also used to populate a master/detail grid.(I am trying
togive the user the option to print the master/detail information they see
on the screen)
I have tried to link the two datasets by setting the datasource of the
detail query to master query datasource. When I do this the master detail
grid doesn't work because only one detail is being printed. I have also
tried to set the pipelines as well

I guess I am not sure what I am doing wrong
I have the master report that contains the master query and in the the
detail band, I have a subreport that is grouped by territory

What I am trying to do is have a
Master record that contains a Territory and 12 months of sales data
Each territory contains 2 types of sales data


Any help would be appreciated. I have read all the tech tips and all the
newsgroup posts and I still am not getting it.
I end up with a report that prints 2 master records and all the details
underneath.

Thanks
K

nicocizik

Hi K,

The idea behind a Master Detail relationship is that there needs to be a one
to many relationship between the linking field. For instance... for every
one Territory in the Master dataset, there are 12 corresponding identical
Territory records located in the Detail dataset (one for each month). The
Detail dataset needs to be ordered by the linking field for this reason.
Below is an example of what your data should look like...

Master Table...

Territory
---------
001
002
003
004
005

Detail Table...

Territory Month
--------- -------
001 Jan
001 Feb
001 Mar
001 Apr
001 May
001 Jun
...
002 Jan
002 Feb
002 Mar
002 Apr
...
003 Jan
...

and so on...


--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico
Thanks for the response.
I thought or think that I have it set up this way

My master query is select * from table where report_type='TT' order by
territory,report_type My detail query is select * from table where
report_type='HO' or report_type ='BB" order by territory,report_type

For every one territory in the master, the detail query contains 2
corresponding records that contain the same territory
The field layouts of both queries are identical, just the numbers/characters
are different inside the fields.

For example Master Table which contains total sales records
Territory 1234 Record_type=TT'

Detail Table which contains breakout of total sales records
Territory 1234 Record_type='BB'
Territory 1234 Record_type='HO

I am still not sure what I am missing and I am really in a time crunch. I
just don't know why I don't get it
Thanks for your help
Kara

nicocizik

Hi K,

Are you placing your detail records inside a subreport connected to the
Detail dataset? You will need to use a subreport in order to successfully
traverse the detail dataset. Take a look at the Master-Detail examples
located in the \RBuilder\Demos\1. Reports\... directory.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico
I currently have a subreport place in the detail band. The subreport is
connected to the detail dataset. The data is traversing correctly except for
the very first record. The first record prints out the entire detail datset.
After the first record master records print out with the correct detail. I
am not sure if it is just one of my settings. I just don't understand why
the first on the very first record it prints, the detail prints everything
including territories that don't match. Once past the first record, it
prints correctly
Thanks
K

nicocizik

Hi Kara,

Ok, thanks for the explaination. It looks as though you have everthing set
up correctly. The problem seems to lie with your pipeline linking. How are
you linking your datasets? The fact that you are dynamically creating the
SQL probably means that you will need to perform the linking in code after
the query has been executed. Take a look at the followng article on
defining pipeline links in code.

---------------------------------------------------------
Tech Tip: Define Master/Detail DataPipeline Links in Code
---------------------------------------------------------

Defining a Master/Detail relationship for a DataPipeline
requires that the detail pipeline have the
following properties defined:

1. MasterDataPipeline

2. MasterFieldLinks


At Delphi design-time you can use the object inspector
and the DataPipeline's FieldLinks editor to define
the master/detail relationship.


The following example illustrates how to define the
master/detail relationship dynamically at run-time.


var
lFieldLink: TppMasterFieldLink;


begin

{define the master/detail pipeline relationship}
plDetail.MasterDataPipeline := plMaster;

{create a new field link }
lFieldLink := TppMasterFieldLink.Create(nil);

lFieldLink.Parent := plDetail;

{assign the detail field name}
lFieldLink.DetailFieldName := 'CustNo';

{assign the master field name}
lFieldLink.MasterFieldName := 'CustNo';


end;


Note: The DataPipeline linking requires the records in the detail dataset to
be ordered by the linking fields. In the above example, the detail data must
be ordered by CustNo.


--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico
I created the masterfield links like the code you provided and the first
record still prints all the detail dataset while all the other records print
correctly.
Could it be something with the query component, I am using. I am using a
TDBISAMQuery
I am just getting stressed because my project is due to the client soon and
I never thought I would have this many problems creating such a simple
report.
Thanks again for your help,
Kara

nicocizik

Hi Kara,

If possible, please send an example application of your report including any
DBISAM tables that are needed to run it in .zip format to
support@digital-metaphors.com and I'll take a look at it for you.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico
I sent the code to the email address below. I have been able to get the
report to display correctly in the report designer but still when I run the
report, the first record prints out the entire detail. After the first
master record prints, the following records print correctly. I have debugged
it to be sure they are linking and I am creating the masterfield links
dynamically before the report prints so I am not sure why the first master
record still prints everything and all the others print normally. I am
losing my mind because I feel like I am trying everything and nothing is
working.
Thanks
Kara