Merging subreports

rbuser

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.

nicocizik

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

rbuser

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\)"

nicocizik

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

rbuser

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\)"

nicocizik

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

rbuser

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\)"

rbuser

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\)"

rbuser

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\)"

rbuser

Finally got it to work.
The 'custno' fields appears to be case sensitive...

Problem solved, thankx a lot.

Chantal

nicocizik

Hi Chantal,

Excellent! Glad you got it working.

--
Regards,

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