Home Datapipelines
New Blog Posts: Merging Reports - Part 1 and Part 2

Newbie at Master Detail Relationship

edited October 2005 in Datapipelines
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

Comments

  • edited October 2005
    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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited October 2005
    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

  • edited October 2005
    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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited October 2005
    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
  • edited October 2005
    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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited October 2005
    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
  • edited October 2005
    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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited October 2005
    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
This discussion has been closed.