Subreport data mixed up

rbuser

Hello,

I am observing some very strange behaviour in end-user subreports.
As a test I have created a very simple report with two datapipelines
(one for the main report and one for the subreport).
*) The main query is
select sam.sam_id, doss.name
from sam, doss
where sam.doss_id = doss.doss_id
order by name;
*) The subreport query is
select sam.sam_id, gev.code
from sam_gev, gev
where gev.gev_id = sam_gev.gev_id
order by sam_id

The sub-pipeline is joined to the main-pipeline on the 'sam_id' field.

If the main report is ordered on 'sam_id' all works fine, but if the
main report is ordered on 'name' then everything gets mixed up: some
'sam' records show the 'gev' records of the next 'sam' record and the
next 'sam' record is then left with no 'gev' records. And sometimes the
'gev' records of a 'sam' record get completely lost.

The really weird thing is that based on the executed queries (using SQL
monitor), all seems fine. The query executed for the sub-pipeline is

SELECT SAM.SAM_ID SAM_ID2,
DOSS.NAME,
SAM_GEV.SAM_ID,
GEV.CODE
FROM SAM
, DOSS
, SAM_GEV
, GEV
WHERE (DOSS.DOSS_ID = SAM.DOSS_ID)
AND (SAM_GEV.SAM_ID = SAM.SAM_ID)
AND (GEV.GEV_ID = SAM_GEV.GEV_ID)
ORDER BY DOSS.NAME, SAM_GEV.SAM_ID


This results in the following (correct) sample data
SAM_ID, NAME, CODE
909, Anopav Strip, Xn
740, Anti Schimmel, Xi
931, "Anti Taret" - "Tegen Paalworm", Xn
887, Anti-Mos, Xn
887, Anti-mos, O
550, Antimoss, Xi

which results in the following (incorrect) report with missing subreport
data:
909 Anapov Strip
Xn
740 Anti Schimmel
931 "Anti Taret" - "Tegen Paalworm"
887 Anti-Mos
Xn
O
550 Antimoss


Does anyone have any idea what might be going on here? I have no clue
what I might be doing wrong.

Thanks a lot.

Luc Feys

nardmoseley

-------------------------------------------
Tech Tip: Database Collation and Linking
-------------------------------------------

Overview
---------

Linking DataViews in the ReportBuilder Data workspace (DADE), requires that
the detail data be sorted by the linking fields. When DataViews are linked,
DADE generates special linking SQL for the detail DataView. The linking SQL
(Magic SQL) can be viewed by positioning the mouse over the DataView window
and pressing Ctrl + Left Mouse button. The results of the Magic SQL query
can be viewed by pressing Shift + Left Mouse button.

ReportBuilder's DataPipeline architecture includes support for linked data
traversal. Whenver the record position of the master data changes, the
traversal logic compares the master and detail field link values and then
determines where to locate the record position for the related detail data.
The linking logic will only work properly if the Database engine's collation
of the detail query result set is in sync with ReportBuilder's internaly
field link comparison logic.

Typically the database engine can be configured to use various language
drivers which control the collation order used to sort query results.

ReportBuilder contains two properties that can be configured to control the
internal field link comparison:

Designer.DataSettings.CollationType (ctASCII or ctANSI) and
Designer.DataSettings.IsCaseSensitive (True/False). Using these properties
results in one of 4 Delphi string comparison routines being used to compare
string data:

1. ctASCII

a. IsCaseSensitive = True --> CompareStr
b. IsCaseSensitive = False --> CompareText

2. ctANSI

a. IsCaseSensitive = True --> AnsiCompareStr
b. IsCaseSensitive = False --> AnsiCompareText


The current values for the Designer.DataSettings are used to create new
DataViews. The DataView's internal TdaSQL object saves the
Designer.DataSettings as part of the dataview's definition. The
CollationType and IsCaseSensitive values are assigned any DataPipeline field
links created for the dataview.


Designer.DataSettings -----> QueryDataView
|_ SQL object
|_ DataPipeline.Links[]: TppMasterFieldLInk


Therefore to modify the DataSettings once a query has been modified requires
that the SQL object and the DataPipeline.Links be updated.





--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

Nard,

thank you for your answer, but I still wasn't able to solve my problem.

The link between the two datasets is on an integer field, so I would
think the collation order is not of any importance then? I tried
changing the collation from ctAscii to ctAnsi, but that makes no difference.

I produced some data that might give you a clue of what I might be doing
wrong, because I really have no idea what is going on.

The main report shows the fields SamId, Nr and Name and is sorted on 'Nr'
The subreport shows the fields SamId and Code and is linked to the main
report on the SamId field. It is also sorted on the SamId field as
required for joining datapipelines.

This is the report as it is generated. You can see that the subdata for
SamId 28 and 955 is shown as belonging to SamId 131.

SamId Nr. Name
131 498B Desinfectant Detergent
SamId Code
131 Xi
28 Xn
28 Xi
955 C
28 4988B Aseptypol
955 4988B Aseptypol
302 4989B Resacryl AS Antischimmel

This is what it should have been (and also what I can see in the 'Magic
data')

SamId Nr. Name
131 498B Desinfectant Detergent
SamId Code
131 Xi
28 4988B Aseptypol
SamId Code
28 Xn
28 Xi
955 4988B Aseptypol
SamId Code
955 C
302 4989B Resacryl AS Antischimmel


Any clues? Does he get stuck on the ordering of the 'Nr' field? So would
it be a collation problem after all? And if so, what could I do to solve it?

Thanks in advance for any suggestions anyone might have.

Luc Feys

nardmoseley

Create a simple, minimal example that we can run here. Use standard Delphi
components and RB. Use DBDemos or create some MS Access or paradox data that
we can run here. Send in zip format to support@digital-metaphors.com.




--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

Nard,

I tried to create an example project, but I can't reproduce the problem
with paradox.

Paradox sorts the records like this: 4988B, 4989B, 498B
while Oracle sorts them like this: 498B, 4988B, 4989B
And in this case the sortorder is what is causing the problem.

I guess internally ReportBuilder expects a sortorder like the one
paradox provides (because there the report is generated OK), but gets
confused with the sortorder Oracle provides.

Is it possible that reportbuilder, when trying to match the detail
records to the master records, compares other fields of the master and
detail pipelines than only the join-field (which in my case is an
integer which should not cause any collation problems)?

If that would be useful to you, I could create an example for Oracle,
but I guess the language settings of the oracle database might have to
be set to 'belgian dutch' to reproduce the problem exactly.

I tried setting the CollationType of the designer to both ctAnsi and
ctAscii, but this does not make any difference. I checked the saved
reportfiles to make sure the CollationType setting was set correctly for
the TdaSQL object of the report, which was the case.

Any suggestions on how I could resolve or work around this problem? Or
how I could provide you with a sample project you could use to verify
this problem?

Thanks a lot.

Luc Feys

nardmoseley

From your response I recommend that you re-read the article that I posted
before. Please let me know if you have specific questions about it. No where
in the article does it say that RB expects the data to be sorted like
paradox. In fact we have customers using Oracle, SQL Server and just about
any and every database engine that Delphi developers use.

--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

Hello Nard,

I think I have read the article about 20 times by now and I still can't
figure out what I might be doing wrong.

*) My detail data is sorted on the link field
*) The link field between the two datasets is an integer. I presume this
makes the CollationType and IsCaseSensitive settings of the datapipeline
of no importance since they are being used to determine 'the STRING
comparison routines being used to compare STRING data'
*) Are there any other considerations to keep in mind?

The data of my master report is (as available in the data preview on the
data tab of the reportdesigner). The data is sorted on 'Nr'.
SamId Nr
131 498B
28 4988B
955 4988B
302 4989B

The 'magic' data of my detail report is (as available in the data
preview on the data tab of the reportdesigner). The data is sorted on
'Nr' and 'SamId')
SamId Code SamId Code
131 498B 131 Xi
28 4988B 28 Xn
28 4988B 28 Xi
955 4988B 955 C


Still the reportgenerator does not seem to be able to correctly match
the linking fields ('SamId' in both pipelines) since this is the
resulting report (notice the incorrect lines in the subreport: the
detail 'SamId' does not always match the parent 'SamId')
SamId Nr
131 498B
SamId Code
131 Xi
28 Xn
28 Xi
955 C
28 4988B
955 4988B
302 4989B


I don't know if this example gives you any clue of what might be going
wrong. If you want I can create an Oracle example to reproduce the
problem. But I can not reproduce it in Paradox or Access.

If sending you an Oracle example is not an option, could you please
point me to the rbuilder unit(s) where the actual link field traversal
and comparison is implemented? I could then try to debug the code myself
to get a better view on what is going on so I can provide you with more
information.

I must say I am getting a bit desperate, since I haven't moved one step
forward on this matter for over a week now.

Thanks in advance for you help.

Luc Feys

rbuser

Hello,

I have been able to solve the problem related to oracle sortorder I had
a few months ago. I just post the solution here so others might benefit
from it. After all, it took me a while to figure it all out.

The situation:
*) If you work with subreports in end-user reports reportbuilder creates
'magic sql' for the subreport datasets, so only 1 query has to be
executed to retrieve all subreport data. This 'magic sql' results in a
'magic dataset' whith the same sortorder as the master dataset (apart
from the addition sortfields in the subreport dataset).
*) Records in the subreport dataset are then linked to the master
records based on the linking fields. One important point to note here is
that when the master dataset is sorted on certain fields, reportbuilder
creates additional linking fields for those sort fields.
*) To find the related records for each master record in the subreport
dataset reportbuilder then performs a comparison on the linking fields
using the Delphi CompareStr and AnsiCompareStr functions. And since both
datasets are equally sorted this step is pretty simple and both datasets
can be traversed forwardly.
*) But in order for this comparison to succeed the sortorder of the
datasets is crucial, and that's where my trouble began: Oracle NLS
sortorder is different from AnsiCompareStr sortorder, which sometimes
resulted in an incorrect match of the linking fields, and thus mixed up
subreport data.

The solution:
The solution was to create an Oracle sortorder that matches the
AnsiCompareStr sortorder. Starting from Oracle 9 this is pretty easy and
well documented. The problem was I had to get it done on Oracle 8i.
But I was lucky, the procedure described in the Oracle 9 documentation
can also be used on Oracle 8i. So I defined a new sortorder using the
Oracle 9 'locale builder' tool and then compiled it using the 'lxinst'
tool of my Oracle 8i installation. All that was left to do then was to
change the sortorder in my application by using an 'alter session set
nls_sort=my_sort' at application startup. No more mixed up data in my
customers reports. End of nightmare.

Cheers.

nardmoseley

Congrats on solving the issue and thank you for sharing this information

--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com