Problem in creating a report from different tables

[Former Member]

Hi Guys,

I was preparing a crystal report for my Rate Analysis Project. I used a data set for storing the result set. I got 5 tables (Project,Work Item,Work Item Equipment Cost,Work Item Labor Cost and Work Item Material Cost) and these tables are filled with a criteria that's based on a project number.

Basically a project has a certain work items and each work item has its own labor,equipment and material involved.Having this i tried to fill each data table with its respective sql statement.And then add the data tables to the data set and at the end load the data set to the report.

But i got the results printed on every single page which i don't know.I designed the report by putting some fields on the page header and the others on the detais section.But i wanted the result to be displayed like the second picture.The first one is the result i got.

[Former Member]

The thing that is giving me a different result is that crystal reports by default creates sql query that doesn't match the one i wrote on vb.Is there a way to tackle this problem.Crystal report is using JOIN but i'm using data tables and then loading them into a dataset.

Is there a way to overcome this problem? Plus is it necessary to link the tables that have common fields?

[abhilash_kumar]

I still don't understand the structure of the report to be honest.

When I asked you to create a group on the field, I expected that the values 'Material, Labour' etc would be part of the same field that can be grouped on.

You can't create 'static' labels and exptect those groups to show details for Material, Labour etc. Rows have to be associated to Material, Labour etc.

If there are multiple tables in the report and there is info coming in from both the tables, of course, you need to join.

-Abhilash

[Former Member]

To clearly set the image to you,i have a project and this project has work items and each work item has a labor,material and equipment of it's own.

So they are related by project and work item.That's why i created 2 groups.

But what i really wanted was after finishing displaying the all labor records under that group,it would move to all material records,and so on.

Shall i try sub group or another mechanism?

[abhilash_kumar]

Hi Nebil,

1) Go to the Group Expert > Add the Work Item No. field to the Group List

2) Also add the field that contains records like Material, Labour, Equipment etc to the Group List below Work Item No.

3) Add all the other fields to the Details Section

4) Get rid of whatever database fields were used in the Page Header.

5) Format the report as required.

-Abhilash

[Former Member]

Am i going to create a material,labor and equipment group under work item?If so,by what criteria,i mean by their ID?

Please clarify it for me

[abhilash_kumar]

It can be by ID or Name,

If it is by ID you would need to show the 'Name' field anyway.

-Abhilash

[Former Member]

Here is the design i made and the result i got,it still gives me the same result.