How do I use the Detail Section when I have multiple Tables

Question

I recently had to take over creating reports using CR 2013, but the introduction I got was kinda lackluster, so from time to time I run into some Problems.

Recently I had to use the detail section for the first time since I was somehow able to handle previous reports with just grouping and Subreports.

Now, what I was doing was create a report listing certain personal assets for each person in a table. First I grouped by Organisation, then by Person and then put the field with their assets into details.

This worked fine until I was supposed to attach a subreport that should only be shown if it contains any records. In my first attempt I attached the subreport in the report-footer and suppressed the section if the ID-Field for the main object of the Subreport was Null.

But that caused all personal assets in the detail-section to be duplicated, I guess because it reprinted the details for all records in both the table of the original, and of the subreport.

I solved this by removing the table from the main record and suppressing the subreport within itself.

But is there a solution to only print a detail section for the records of a specific table? Is there even a reason to do that? I am asking since I want to be sure I understand such concepts going forward.

Thank you in advance.

Answer 1

I understand that the introduce of the second table caused the repetition of details because it lead Crystal to make a cartesian product of the two tables.

Let's call the two tables like this to facilitate: TableA, TableB.

When you use only the TableA, think that Crystal do this:

select * from TableA

Then you introduce TableB, then Crystal would do this (just a mental model, I am not saying it does it really):

select * from TableA, TableB

If you understand SQL, you will notice what happens: a combination of all elements in both tables.

Then, the details section will consider each result of this combination.

Ex: TableA = {1,2}; TableB = {X,Y}; Result = {1X,1Y,2X,2Y}

In general, there are two approachs to avoid this. I don't know which one is applyable, since I don't know all the details of your case.

1. Let your main report know about the TableA only and the subreport know about the TableB only.
2. Create an extra group (surrogate, innermost). It should group by an unique value in TableA (an Id value would be great). Then you move the fields from details section to this new group footer section and suppress the details section.

There is another approach: create a link between both tables, but it is possible depending on the data, so I can't claim it will work. That would lead to Crystal to do something like:

select * from TableA, TableB where TableA.Id = Table.ReferenceToA

And it would possibly remove the repetitions.