cancel
Showing results for 
Search instead for 
Did you mean: 

Problems with mutiple detail tables due to cartesian product

Former Member
0 Kudos

Hello All,

The problem is that I have multiple detail level tables selected, I need to use these tables to calculate and display data. There is an old post in this forum which deals with the displaying part. It uses subreports to display all the data at once so that way multiple tables are not joined together.

This old post ->>

But I have this situation where I need to calculate the difference between 3 child tables in addition to displaying them and for that I need a solution other than the one in the link above. This is for a garment factory and here is how it goes:

There are items in the item table, then there is a order table, cutting table, checking table and packing table. All these tables have itemid's and quantities.

I need to create a size-wise crosstab where i can calculate the difference between order and cutting, order and packing, cutting and checking.

I've attached the image showing this problem.

As you can see here, it works great till order and cutting, then it goes all Cartesian on me when i add the checking table. If I uncheck 'Select distinct records' the checking section will show the correct values but the rest of them will multiply and show very large values.

Hope I can get some help on this. Thank you all.

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

What type of database are you connecting to and how good are your SQL skills?  I would create either a View in the database or a  Command (SQL Select statement) in Crystal that unions together the data from the 3 child tables.

If you create a View, you can use it just like any table in your report.

If you use a Command, you'll need to be aware of the following:

1.  Use a single Command to get all of the data for your report.  If you combine a Command and tables or another command, Crystal won't be able to push the joins down to the database for processing.  Instead, it will pull ALL of the data into memory and join it there, slowing down report processing.

2.  DO NOT use the Select Expert to filter your data.  Instead, use the Where clause in the Command.  If you use the Select Expert, Crystal will not be able to push the filter to the database for processing and will pull all of the data into memory to filter it there, slowing down report processing.

3.  If your report uses parameters to filter data, those params MUST be created in the Command Editor and used in the Where clause of the Command.  The Command Editor does not "see" parameters that were created in the report itself.  However, after creating the parameters in the Command Editor, they can be modified in the report to make them dynamic, set edit masks, etc.

-Dell

Former Member
0 Kudos

Thank you Dell. I'm using Oracle, I created a view and used it to calculate the sum of one detail field grouped it by another. Used that view in the sub report and it worked great.

Answers (0)