Master Detail With Aggregate Query

rbuser

I have a problem that is consistent and I can't seem to come up with a good
work around. It is in creating Master / Detail reports. I have no problem
creating a master / detail report as long as the detail query does not
include any kind of agregate query. But if the detail involves calculations
the values get multiplied by the number of records. This issue doesn't
involve report design. In the Data tab, resulting data gets skewed once I
link the tables.

Master Example:
Query VendorNumber from Vendor table

Detail Example (Works):
Query all Vendor Transactions for date range, sorted by Vendor Number, join
to Master Query.
Every thing works correctly and resulting data is correct.

Detail Example (Incorrecct)
Query all Sum of Vendor Transactions grouped and sorted by Vendor Number
Resulting data is correct with proper sums per Vendor
However, if I link that detail query to the Master table, the resulting data
in the detail dataset is now skewed and multiplied times the number of
records.
Unlink the detail query from the master and refresh the query and the data
is correct.

I am encountering this in all reports. I need to be able to do this in
order to get beginning balance, ending balance, etc.

Any thoughts or suggestions would be appreciated.

\Bob

rbuser

Further Clarification

Just to clarify why I am doing a master detail on a group by. I am actually
using 4 queries (datapipelines)
1. Master: Query all vendors that have any transaction activity through a
date range
2. Detail1: Query the sum outstanding balance per vendor for the start of
the date range (beginning balance)
3. Detail2: Query the detail transactions for the date range
4. Detail3: Query the sum outstanding balance per vendor for the end of the
date range (ending balance)

It is Details 1 and 3 that produce inflate numbers when linked to the
master. As long as they are not linked to the master they work properly.

Thanks,

nardmoseley

The issue is caused by the master query selecting a different date range of
records than the detail query's in 1 and 3.

What is happening is that when query's are linked in the RB Data workspace,
special linking SQL is generated for the detail queries. The detail SQL
Where clause is augmented with information from the master SQL Where
clause - this works for most scenarios, but no query 1 and 3. (You can view
the linking SQL by position the mouse over the dataview tool window and
pressing ctrl + left mouse button.

You can write code to manually link the datapipelines. 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');

myReport.Print;




--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Ok. Good to know I am not going crazy.
What if I just don't link the two queries and do a locate of the detail on
the position of each record of the detail? I think that would be simpler
since queries 1 & 3 are just beginning and ending balance and just one
record per master record.

I tried the standard locate function but did not have any luck.
What would be the syntax for the locate?
On what event should I put the locate?

Thanks,
Bob

nardmoseley

I recommend linking the pipelines per my prior post.

When datapipelines are linked, the detaildatapipeline will automatically do
a locate when the master record position changes. It is much simpler and
will be more optimized.


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

I misunderstood at first. But you were right. Just adding the link
manually solved the problem and it was
very simple to do. Understanding this has solved a lot of problems for me
with numerous financial related reports where I need to show beginning
balance, detail transactions and then ending balance.

The only problem now is I am compelled to go back and cleanup several
reports where I did variable galore to deal with the aggregrate query join
issue.

Thanks for the valuable information and great information.

Bob