Master/Detail link with null value

rbuser

Using D5, RB7.03 Ent,
I am using the MasterDataPipeline & MasterFields to link two sets of data (ADO
queries from SQL Server). This works great and performance is fantastic
compared to the old Delphi linking of datasources.

However, I found that the report goes weird when the first field in the first
record(s) in the master table is null. The master prints fine, but the
subreport using the detail pipeline stops, then dumps all detail records under
one master.

Excluding the records with the nulls from both tables and the report prints
perfectly.

I saw that this area was fixed in 7.00 for D6 SP2 but it is broken in D5.

Any suggestions for a fix?

Grant

nicocizik

Hi Grant,

This may be a limitation of ReportBuilder. Nevertheless, it is not a good
idea to have Null records in your master dataset. You might try suppressing
this null value using the DataPipeline.SuppressNullValues or simply removing
the null record from the dataset completely.

--

rbuser

While I agree that null values (in just one field of the record) are not
necessarily desirable & I am taking steps to prevent them in new data, I found
this as a limitation.

The null values are obtained via a query performing a lookup for the purposes
of sorting & grouping the report. Unfortunately, removing the record is not an
option as these are invoices, they just don't have one field populated.

It appears to be a problem with RB's linking datasets.

Grant

nicocizik

Hi Grant,

You should be able to work around the null values by filtering your master
dataset to exclude all the null occurences of the master field. Something
like the following:

SELECT *
FROM Customers
WHERE Customers.CustID IS NOT NULL

This would filter out any null master fields and should allow your report to
run correctly.

--

rbuser

Nico,
Thanks for the suggestion but this is not the problem. The actual master table
for the report doesn't have null values just a lookup table used in the SQL
"Order By".

e.g.
Select Invoice,*, Division.DivisionName
from Invoice left join Division on Invoice.DivisionCode = Division.DivisionCode
ORDER BY Division.DivisionName, other fields

The report has a group on DivisionName with subtotals.

Some of the records in the Invoice table have a blank DivisionCode which gives
the first group of records with a blank division. The report on the master
table only works fine.

Add a subreport on the detail lines for the invoice linked via
MasterDataPipeline & MasterFields. This version prints the master records fine
but not the detail records.

Do you want a demo project?

Grant

nicocizik

Hi Grant,

If possible, please send an example demonstrating the issue in .zip format
to support@digital-metaphors.com so we can take a look at it and find a
solution as quickly as possible.

--