Crosstab for detail data only

rbuser

Hi All,

Using RB 10.06 within a D2007 Win32 application and end user designer.

We have a master detail report linked within the dataview. We wanted the
main report grouped on each client and to display a crosstab for each
client's detail records (400 clients each with about 20..30 detail records).

It appears that the crosstab always traverses the whole dataset (all master
& all details) for every client, is there a way to stop this?

I did add code in the crosstab's OnTraverse event to skip records where
detail client number <> master client number which works but has made the
report unusably slow. It will be traversing and skipping thousands of
records each time to display just the 20 or 30 for the current client.

Hope you can offer a solution.

Regards, Paul Hughes.

nicocizik

Hi Paul,

Be sure your crosstab component has its DataPipeline property set to the
detail dataset. This will ensure that only the details for a given master
will print for that record. I did a simple test with the Customers and
Orders tables of the DBDEMOS database and the crosstabs seemed to output the
correct data for each master.

--
Regards,

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

rbuser

Thanks Nico,

That's what I thought should be happening and the pipeline was correctly set
to the detail data. Since posting, I've looked at it a bit more and found
the following possible problem.

When I do a quick preview of the records in the dataview, the master (which
is grouped so it should only bring back a single row per client) is bringing
back all the records with multiple entries per client (one for each detail).
This was strange so I copied the SQL and ran it in DB Desktop. Strangely, it
then worked as expected with only a single row per client.

This is BDE based with Paradox tables by the way. I then tried changing the
SQL type on the report (although we've not seen any problems with that
before) but it made no difference. It's as if the internal master query SQL
is executed but produces an ungrouped result set.

May have to start from scratch and see if reconstructing the report fixes
it.

Any other reports of anything like this or things I could check before
re-writing it?

Thanks, Paul

nicocizik

Hi Paul,

We are not aware of any issues like this with RB 10.06. I would first
suggest commenting out any event code you have to be sure this is not have
an effect on the output.

--
Regards,

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

rbuser

HI Nico,

Thanks again. This is an unusual problem and I've still not managed to sort
it out. Would you be able to post your DBDEMOS example RTM so I can see how
you're doing it. Just to make sure I'm understanding this correctly?

Regards, Paul.

nicocizik

http://www.digital-metaphors.com/tips/CrossTabMasterDetail.zip

--
Regards,

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

rbuser

Hello Nico,

Thanks for posting the example.

I compared our report to the example and could not find any significant
differences in the way I was attempting to write the report. I was
struggling to understand what was wrong.

I noticed that the only difference between the two reports was that the
DBDEMOS example tables had primary keys on the underlying tables which were
what you had linked them on in your example. I tried restructuring the
tables I was using so that they had primary keys matching the link in the
dataview. At first this made no difference so I rewrote a basic version from
scratch and everything then worked as expected!

I couldn't leave the restructured tables as the main application would have
needed some rewriting so I reverted the tables to their original structure.
I thought I'd just try the modified report anyway, expecting it to be
broken. Guess what? It still continued to work normally.

I don't know how the underlying code works for the crosstab but it would
appear that something is saved in the template which affects this ability to
do master detail linking correctly.

Can you check it out please, it doesn't make sense to me but I have re-done
the tests and haven't, AFAIK, gone mad! Though I was beginning to think I
had.

By the way, I had forgotten that the application in question (the current
version we have out there) is using 10.05 under D7 (not 10.06 under D2007 as
I mentioned in my first posting).

Regards, Paul.

nicocizik

Hi Paul,

Sorry for the delay, for some reason my newsreader overlooked your reply.

Using a crosstab in a master detail relationship is similar to creating a
master detail report with subreports. You need to follow the same rules in
that the detail dataset needs to be sorted on the linking field. This way
for each master record (detail band), the crosstab component will have
access to the proper detail records.

------------------------------------------------
TECH TIP: Report Data Traversal
------------------------------------------------


1. Single Table Listing Report

Assign the Report.DataPipeline property and leave the
DetailBand.Pipeline unassigned.

The report will traverse the data from start to end (based on the
datapipeline range settings and honoring any filters you've placed on
the datset etc.)


2. Master/Detail Report:

A. Assign the Report.DataPipeline property to the master. Create a
subreport in the detail band and assign the childreport.DataPipeline to
the detail datapipeline.

Use either the visual linking features available from the Report
Designer's Data tab, or Use standard Delphi dataset linking to define
the relationships between the datasets.

The Report will traverse the master records and for each, the subreport
will traverse the detail data related to the master.


3. Master with 2 Details

Configure as in 2 above. Add an additional subreport to the detailband.
Set subreport.ShiftRelativeTo property to the point to the first
subreport. Connect the ChildReport's DataPipeline to the detail data.


4. Report connected to no datapipelines.

When Report.AutoStop is set to True, the Report will print a single
detail band.

When Report.AutoStop is set to False, the Report will print detail bands
until instructed
to stop. You can control when the report stops by setting the
Report.PageLimit property or by calling Report.DataTraversalCompleted
method. Otherwise the report will never stop.


Additional Notes:
----------------

1. SubReports have a Report property that is of type TppChildReport.
Thus, programmatically you can code SubReport.Report.DataPipeline :=
myDataPipeline.

2. ChildReport's traverse data following the same rules as above. A
ChildReport prints in its entirety each time it gets a turn to print.

3. For a Child style SubReport use the Title/Summary band rather than
the Header/Footer (or use a GroupHeader/GroupFooter combo). A standard
Header/Footer will not work because these always print at the very
top/bottom of the page.

4. Do not filter the dataset of modify it any way once the report.Print
command is issued. If you need to do master/detail and cannot use
linked datasets, then use the master DataPipeline.OnRecordPositionChange
event to filter the detail dataset.

5. The reports and datapipelines use dataset bookmarking. Make sure
the dataset can support bi-directional navigation.



Check out the RBuilder\Demos\Reports\Demo.dpr project. Reports 0071,
0072, 0073 show examples of master w/mutliple detail style reports.
Number 0072 has two detail reports.

--
Regards,

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