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

Sub-Report Traverses too much data - Linked Dataviews

edited December 2002 in Subreports
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.

Comments

  • edited December 2002
    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

  • edited December 2002
    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.

  • edited December 2002
    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

  • edited January 2003
    "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
  • edited January 2003
    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,

  • edited January 2003
    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


This discussion has been closed.