CODEWITHSUNDEEP
crystal-reports
Randy Minder
Randy Minder

Reputation: 48422

How do I create a report with multiple 1-M relationships?

I need to create a report that shows, for each master row, two sets of child (detail) rows. In database terms you can think of this as table 'A' having a 1:M relationship to table 'B' and also a 1:M relationship to table 'C'. So, for each row from table 'A', I want to display a list of child rows from 'B' (preferably in one section) and a list of child rows from 'C' (preferably in another section). I would also prefer to avoid the use of sub-reports, if possible. How can I best accomplish this?

Thanks.

Upvotes: 0

Views: 1221

Answers (3)

PowerUser
PowerUser

Reputation: 11791

This is exactly what Crystal was made for :)

  1. Make a blank .rpt and connect to your data sources as you normally would.
  2. Go to Report->Group Expert and choose your grouping field (aka Index field).
  3. You will now see a Group Header section in your design view. This is your "Master row". The Details sections will be your "Child rows".

In the example image below, this file is grouped by {Client}. For client "ZZZZ", there are 2 records, so there are 2 details sections while all the other clients only have 1 details section.

alt text

Edit

Based on your response, how about this:

  1. In your datasource (or perhaps using some kind of intermediary like MS Access), start SQLing as follows.
  2. Make a subquery left joining the primary key of TblA and the foreign key of TblB. Add a third column containing a constant, e.g. "TblB"
  3. Make a subquery left joining the primary key of TblA and the foreign key of TblC. Add a third column containing a different constant, e.g. "TblC"
  4. Union those 2 queries together. That'll be your "index table" of your crystal report.
  5. In Crystal, you can have multiple grouping levels. So group first by your constant column, then by the TblA primary key, then by the foreign key.

This way, all the results from TblB will be displayed first, then TblC. And with a little work, tables B & C won't even have to have the same field definitions.

Upvotes: 2

MartW
MartW

Reputation: 12538

I think I understand your question correctly, ie for a given row in Table A, you want the details section(s) to show all connected rows in Table B and then all connected rows in Table C (for any number of rows in B or C, including zero). I only know of two solutions to this, neither of which is straightforward.

The first is, as you've guessed, the disliked subreport option. The second involves some additional work in the database; specifically, creating a view that combines the entries in Table B and Table C into one table, which can then be used in the main report as a linkable object to report on and you can group on Table A as desired. How straightforward this is will depend on how similar the structures of B and C are. If they were effectively identical, the view could contain something simple like

SELECT 'B' AS DetailType, Field1, Field2, FieldLinkedToTableA
FROM TableB
UNION ALL
SELECT 'C' AS DetailType, Field1, Field2, FieldLinkedToTableA
FROM TAbleC

However, neither option will scale well to reports with lots of results (unless your server supports indexing the view).

Upvotes: 2

Mark SQLDev
Mark SQLDev

Reputation: 539

You can use or create columns that are used for grouping, then group on the table A column, then the table B column, then C. (Crystal's group is not the same as t-sql "group by". In Crystal, it's more of a sorting than a grouping.)

Upvotes: 0

Related Questions