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

Merging subreports

edited February 2008 in Subreports
Hi,

Ik have a report made of one main table en 3 subtables joined to the
main table over 3 fields (data pipeline, report builder in embedded
in our software).

For some combination of the 3 fields in de main table I must get only
one link to the subtables...in order to merge the associated
subreports...

It's not possible to join tables on Calc variables so I don't know any
way of doing this.

For example:

Main table field value Subtable field value
A - A1 - A11 A - A1 - A11

B - B1 - B11 B - B1 - B11

C - C1 - C11 C-C1 - C11, C - C1 - C12, C-C1 - C13
C - C1 - C12 C-C1 - C11, C - C1 - C12, C-C1 - C13
C - C1 - C13 C-C1 - C11, C - C1 - C12, C-C1 - C13


I've made a report where I removed the link between the tables and
manually set the parameters of the 3 fields in the
beforeopendatapipeline according to the autosearch values.

Then I get a subreport including all records with C11, C12, C13 when C
en C1 are selected...

But this means the report has to be called individually for all the
possible parameters.

Is there a way to manually change the pipeline selection between
groups or pages in a report. So that the report still generates all
the pages by itself?

Or an another way of joining the tables of placing the subreports so
that the wanted records are merged instead of showing 1 subreport per
record?

Thankx,

Chantal M.

Comments

  • edited February 2008
    Hi Chantal,

    Sorry for the confusion, I don't fully understand which dataset the
    subreport(s) are connected to. Are you linking separate datasets in DADE or
    are you joining the tables into a single dataset? Have you considered
    creating a master/detail report?

    --
    Regards,

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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited February 2008
    Hi Niko,

    In the pipeline I have actually 4 separated tables, they are not
    linked together in the samedataset (I'm not sure I know what DADE
    means).
    I have a main report with the main table en 3 subreports voor the 3
    subtables. Those are called in the detail section of one of the group
    of the main report.

    For each of the main table records I get one subreport voor each of
    the 3 subtables.

    The problem is that some of the main table records must be "merged".
    So I want to be able to call the subreports with merge record from the
    main table...so 2 records in main table must be seen as 1...I cannot
    group because the link with the subtable is on 2 fields, wich I can
    only merge with a calc variable and cannot link on calc variable. So I
    always get 2 subreports per main record.

    I need something like this:
    Record 1a
    Subreport 1a
    Record 1b
    Subreport 1b
    Record 2a en record 2b
    Subreport 2a/2b
    Record 3a
    Subreport 3a

    I can't add a field to the main table, otherwise this would be easy, I
    could just link the tables on this by giving record 2a en 2b the same
    value voor example...but this is not possible...so is there a way to
    do this in code...?

    Can you describe what you call a "master / detail" report? Considering
    I work with a "dutch" version of the report builder I'm not sure if
    this is wat I'm already doing or not...!

    Thankx,

    Chantal

    On Mon, 4 Feb 2008 10:13:41 -0700, "Nico Cizik \(Digital Metaphors\)"
  • edited February 2008
    Hi Chantal,

    DADE is the Data tab in the report designer or the data workspace where
    queries can be created from within RB. By Master/Detail reports, I'm
    referring to linking separate datasets using DataPipeline linking. (See the
    main report demo for examples of Master Detail Reports in the
    \RBuilder\Demos\1. Reports\.. directory.


    Typically you would want to combine two records as one by either selecting
    distinct recoreds, performing a self join on the tables you are using, or
    adding a Group By to your SQL code.

    --
    Regards,

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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited February 2008

    Joining different dataset in the pipeline is what I am doing.
    I just have no way of grouping the data in the first table in the way
    I want because I don't have a "uniq" field to join with the other
    datasets, but 2 fields...although I actually want to join on a
    combination of those 2.

    If I could "group by" on not "all" the fields, but just some of them
    it might work, but this is only allowed when I type the sql statement
    myself, when I do this I cannot join the datasets anymore...

    I'm going to have to remove the join and filter the data in the
    subreports self...


    On Thu, 7 Feb 2008 10:52:29 -0700, "Nico Cizik \(Digital Metaphors\)"
  • edited February 2008
    Hi Chantal,

    One thing you might try is manually link your datasets. You just need to
    make sure that the detail querys are sorted by the linking field(s). And
    make sure you link the datapipeline in the Report.OnInitializeParameters or
    Report.BeforeOpenDatapipelines event, or prior to calling Report.Print

    Example:

    // parameters are MasterPipelnie, MasterFieldAlias, DetailFieldAlias
    myOrdersPipeline.AddLink(myCustomerPipeline, 'custno', 'custno');

    We are strongly considering adding the ability to link on non-aggregate calc
    fields for the next release of ReportBuilder.

    --
    Regards,

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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited February 2008
    Thanks,

    I have linked the tables on one of the 3 original fields, sorted
    every dataset the same way, and manually filtered on the last 2 fileds
    in the subreports self.
    So I set a variable to the current values and only use the record in
    the subreports that matches...I just have a problem when no records
    match, it's then too late to set the subreport title to invisible!

    I shall try adding the linkt and see if it also wordt.

    Chantal

    On Fri, 8 Feb 2008 09:49:44 -0700, "Nico Cizik \(Digital Metaphors\)"
  • edited February 2008
    Cool...it seams to be working!

    Is there also a parameter/function to set the link type? (LEFT OUTER
    JOIN, INNER JOIN...etc?)

    Chantal

    On Fri, 8 Feb 2008 09:49:44 -0700, "Nico Cizik \(Digital Metaphors\)"
  • edited February 2008
    I spoke too fast.

    The link doens't 100% works.

    The first linking field (A) works fine. But the second (B) doens't
    seems to be considered.

    I don't get any error but I get voor each main record one subreport
    with everything in it...no matter wich field B it is supposed to be
    linked to.

    It is possible to AddLink more than once for more than one field isn't
    it?

    Field A comes from the main table, field B comes from another table in
    the dataset, joined to the first table with an inner join...

    Chantal

    On Fri, 8 Feb 2008 09:49:44 -0700, "Nico Cizik \(Digital Metaphors\)"
  • edited February 2008
    Finally got it to work.
    The 'custno' fields appears to be case sensitive...

    Problem solved, thankx a lot.

    Chantal
  • edited February 2008
    Hi Chantal,

    Excellent! Glad you got it working.

    --
    Regards,

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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
This discussion has been closed.