Sub-Report Traverses too much data - Linked Dataviews

rbuser

Using modified end-user reporting RB7/D6 with Asta OR ODBCExpress
(ASA5)

Skinnied down:I have a Master with a PK MasterID and a Name field, a
Child with a PK ChildID, a phone field and a FK MasterID. The data
views are linked on the PK/FK.

I have a Master row with a Name of D'SOUZA that has two Child rows,
plus a number of other master/child rows.
The report has a group level with a sub-report in the detail band set
to the child pipeline. The sub report just puts out the three Child
fields.

1) Master sort on MasterID, Group on MasterID: Sub-report has the two
rows.
2) Master sort on Name, Group on Name: Sub-report starts with the two
rows but keeps going for more rows (but not all). The main report
detail band omits the rows for the masters whose Child rows were
mistakenly reported in the sub report
3) Change D'SOUZA to DSOUZA in the db and all is well.

The single quote seems to cause a problem in some records. Perhaps
sort order with/without a quote in relation to other records?

Thanks.
Rick Matthews
Dartek Systems Inc.

digitalmetaphors

Each database can sort the data differently. Some use ASCII and others use
ANSI string comparison. Try changing the CollationType property on the
DataSettings property of the designer.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Thanks but setting to ANSI made no difference.

My db is using Code Page 850 Latin 1 and my desktop (Win2k) is
standard locale English (United States).

A report that uses a single data view grouped on Name with the Master
info in the group header and the sub report in the detail band (no
pipeline assigned) works fine with the correct order and number of
child rows.

digitalmetaphors

Are you saying you built a single dataview by joining the two tables
together and using a group in the report instead of a subreport to print the
data? This should work as an alternative.

The SQL that RB generates can be placed in a Query component for you to see
what it is returnign. Hit Ctrl-mouse click over the each dataview. This is
the SQL that is sent for the server to return the records in the order which
will support the master detail linking in a single detail dataset for all
detail records for all master records. If you compare there two datasets in
a DBGrid on a form, can you see any ordering problem?

When RB traverses the data it compares the linking field values. This is
located in TppCustomDataPipeline.CompareLinkedData in ppDB.pas. Create a
simple dataset example report (filter the master dataset) and trace through
this code to see the result of the call to CompareStr().

Cheers,

Jim Bennett
Digital Metaphors

rbuser

"Rick Matthews" wrote ...

Sounds like code someplace neglects to properly escape single
quotes in strings placed in an SQL query string. Something along
the lines of sql := sql + sFieldname + ' = '' ' + sFieldvalue + ''';
should be sql := sql + sFieldname + ' = ' + QuotedStr(sFieldValue);

Invalid SQL:
SELECT * FROM CUST WHERE NAME = 'D'SOUZA'
Valid SQL:
SELECT * FROM CUST WHERE NAME = 'D''SOUZA'

Brian Evans

rbuser

Thanks Brian but there is no where clause on the name - D'SOUZA is
only referenced as part of the result sets coming back.

I have just returned to this using rb7.01 D6 and Asta and the issue
still exists.

Jim:

TppCustomDataPipeline.CompareLinkedData compares the data correctly so
the issue is at a higher level.

I have skinnied it down as far as I can - Two master records, two
detail records.

When the Name is D'SOUZA it enters the function 53 times. When the
Name is D SOUZA it enters it 42 times.

Due to the large number of calls I am unsure where else to look. Just
to remind you this is only a problem with linked data views. The same
data using grouping works fine (but I need linked data views to work).

Thanks,

digitalmetaphors

Can you take ASTA out of the equation and send us a demo we can try to see
if we can come up with a solution. Send a minimal simple project with some
demo data to support@digital-metaphors.com

Cheers,

Jim Bennett
Digital Metaphors