Linked Field Data not Appearing.

rbuser

Hi

I have a very peculiar problem happening on a report.

I have a main report with a primary data set, and a secondary data set
containing some summed data for display during different part of the report.
There is a common link field between the two data sets, and I link the two
together using the standard link mechanism showing all rows from the primary
data set, irrespective of a Match in the secondary data set. A Standard
report takes about 45-70 seconds to run depending on data etc.

The pecularity is this.

When the report is executed, it appears some of the linked data from the
secondary data set isnt visible/linked properly. If i restrict the secondary
data set to be the individual row of data I am missing the data appears in
the report as expected. If all of the data from the secondary data set is
used, it appears to miss certain rows of data from the secondary data set. I
am not 100% sure yet (as I am still investigating), as to how many other
rows are affected.

Is there any reason why linked data sets would seem to drop data? I am
trying to access the data in a group footer field (which happens to be the
link field between the two datasets.

I am using D6 and rb 6.03 enterprise.

It's a strange error and I cant for the life of me figure out whats wrong.

Any suggestions on why this behavoir is occurring?? Is there some mechanism
by which i could insert some code, either in the report, or in the source to
debug why the link field is missing? Is there some resource restriction
perhaps? Computer is a pentium 4, with 2GB of ram

Thanks
Scott

nicocizik

Hi Scott,

First be sure your detail dataset is ordered on the linking field. This
enables ReportBuilder to successfully find the proper data. Are any of the
records in the master dataset (linked field) null? If so, be sure the
SkipWhenNoRecords property of the detail datapipeline is set to False.

If none of this helps, try downloading a trial copy of the latest version of
ReportBuilder for Delphi 6 (RB 10.05) and test with that. Be sure you back
up any templates you might be using and that you have a copy of your RB 6.03
installation file before installing the new version in case you need to roll
it back.

--
Regards,

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

rbuser

Hi Nico

There are no null values in the primary data set - so that would eliminate
that as a possible source of error.

Some further information

1) On the Secondary/Detail Data Set, If I preview the detail data set, i
actually see the rows of data that are missing. So i know the data is there.
2) The Master/Primary Data Set is not ordered on the Link Field, but a
different order, whereas the Detail/Secondary Data set is ordered on the
link field
3) Does the fact I am trying to reference the link field in the group footer
(where the link field is changing) have any impact?

I'm doing as you suggested as well and downloading the newer version of RB
to see if that works as well. I just thought to add the extra information.

Thanks
Scott

nicocizik

Hi Scott,


I'm a bit unclear about the design of your report. Are you trying to
display all of the details for a given master inside the group footer? If
so, be sure you do so by adding a subreport to the group footer band, then
connecting it to the detail dataset before displaying the records.
Otherwise only the last detail record will be displayed. Perhaps a little
more information on how your report is designed will help me track down the
issue.

--
Regards,

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

rbuser

HI Nico

Thanks for your continued support. My apoloies for the delay in responding -
i've had a few sleepless nights recently. I'll explain as clearly as i can
as to what i am trying to achieve.

The report I am working on is a "Buyers" Report. In the industry I work in,
we receive pre-orders for a given date. We print the buyers guide based on a
given date - display a summary of what the buyer(s) needs to sumarise. The
following are the tables I have to work with (just relevant fields).

Stock Table
StockID int (Join A)
StockCode varchar(32)
QtyOnHand float
BuyerID varchar(16)
StockDesc varchar(64).

OrderMaster Table
OrderID int (Join
OrderDate datetime
CompanyID integer (Join C)

OrderDetail Table
OrderDetailID
OrderID (Join
Qty
StockID.(Join A)

Company Table
CompanyID int (Join C)
Name varchar(64)

The Nominated Joins are done within a single data view.

Data is Sorted in the order BuyerID, StockDesc, StockCode, Name

The Current Report is configured as

Group 1: StockStockCode
Group 2: Company.Name

All Data is displayed in the Group Footer for Group 1.

The Following is displayed

DBText: QtyOnHand ====> Qty of Stock On Hand
DBCalc: Sum(Qty) =====> Qty of Stock Required for Date X
Variable: DBText - DBCalc =======> Delta of Stock (New On Hand)

The Variable Calculates basically shows what the on hand would be after the
sales (a negative value indicates the amount of stock we need to buy).

This report as is works as is.

Where I need to modify this is as follows. On a Monday, I need to obtain the
Qty of Stock that was ordered on Saturday and Sunday, and then subtract this
value from the On Hand Value.

So what i did, I added a new Data View to the Data Module of the report
which is based on the OrderMaster and OrderDetail Table above, defined as
follows

OrderDetail.StockID
Sum(OrderDetail.Qty) as TotalWeekendOrdered
For the OrderDate >= Saturday
and Order < Monday
Order By StockID

So this new data view is basically a list of stocks and the total ordered in
the last 2 days. Then in the data tab in the report designer, I link the
StockID from the initial view to this new data view, displaying all rows
from the Initial Table (so a left outer join).

What I am trying to get in the Group Footer of the Report is follows

Variable: QtyOnHand (Orginal Data view) - TotalWeekendOrdered (from New Data
View) =====> Qty Stock On Hand (Adjusted for the Weekend)
DBCalc: Sum(Qty) =====> Qty of Stock Required for Date X
Variable: Variable - DBCalc =======> Delta of Stock (New On Hand)

So what i found was when the report was run, I would find that the
"TotalWeekendOrdered" wouldnt be linked from the second data view, meaning
my on hand value was wrong. So i put the OrderDetailID, OrderDetail.Qty and
the TotalWeekendOrdered into the detail band. This showed me that some lines
did have a linked value, and others didnt (and i couldnt explain why). But
finding this out explained why the group footer didnt display the
TotalWeekendOrdered - as the last line in the group didnt have the linked
field value.

So thats what I am hoping to achieve.

If you have any advice to try and help achieve the desired result, it would
be great.

Thanks again
Scott

nicocizik

Hi Scott,

Master-Detail reports work in the following way...

The master pipeline will traverse the first record in your master dataset
which will hopefully contain a unique linking field (StockID). If the
dataset is linked, the detail pipeline will search its dataset for the first
occurance of the master record value and traverse until that record changes
(This data is viewed using a subreport linked to the detail pipeline).

1. Be sure you have a one to many relationship between your master and
detail dataset and that your master linking field is unique. Also be sure
you dragged your link from the detail to the master.

2. My guess is that your detail dataset is never fully being traversed due
to the fact that you do not have in connected to an actual report. If you
simply place the detail fields in the main report's detail band, the
pipeline will not traverse. You need to connect a subreport to the detail
pipeline and then place the fields in its detail band.

--
Regards,

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