Hi All,

I have created a report with 2 sub reports, I had to use sub reports as the table links were conflicting and causing duplicates.

I am attempting to use shared variables to get the data out of the sub reports, Which succeed with no issue.

However I cannot get the data into a pie chart (client request) any suggestions on where I am going wrong?

Crystal reports version is XI

Kind Regards

Tom

You are right about the summary, so in the sub report i used the count({Table1.coloumn5}). But the issue is in the count when the duplications appear, for example one table will have 150 rows the other 200 and I will always get back 290-320 depending on the data.

but I will try your suggestion, and give it a go.

thanks for the help

I see, I am considering a redesign of the report, can I include a table without a link to another table?

The reason I wonder is I only want to use 1 table for a count, the other I want to display the information as well as do a count, (which lead to my sub report idea).

The link is what is causing the issue because the tables share a couple columns of the same information, because they both link to other tables and are used in separate instances.

Or can I link the tables to 1 table with a common field? to prevent duplicate data?

I tried doing a distinct count and it was returning incorrect numbers (awkward I know).

Data wise I have the following layout, and I cannot change the arrangement of the tables.

we have these tables required for the report:
Site
Area
Location
Item
ItemType
TestList
TestNextDue
TestResult

They are linked in the following way

Site.siteID - Area.SiteID
Area.AreaID - Location.AreaID
Location.LocationID - Item.LocationID
Item.ItemTypeID - ItemType.ItemTypeID

Item.ItemID links to both TestNextdue.ItemID and TestResult.ItemID

TextNextDue.TestID and TestResult.TestID link to TestList.TestID

The TestNextDue Table is a list of when tests are due and what tests, what date and last test date.

TestResults is a list of all results and what test they were and what date.

I need the report to show the following:

1. a Pie chart comparing the number of rows in the TestResults table, against the Number of rows in the TestNextDue Table

2. A breakdown of the TestNextDue Table. (Test Results Table is clearly for the count to compare in the pie chart)

3. the above to be filterable by date.

The problem Arises when Include both TestsNextDue and TestResults table in the same report. Causes the duplicates as the common fields are TestID and ItemID
Which is why I resorted to 2 subreports to get the numbers out.

Any suggestions on how to achieve the above would be greatly appreciated and I will try and update if I make any progress.

They do have a primary key generated by autonumber within the database, I did not think to try that.

Would I use distinctcount({TestNextDue.TestNextDueID})?

And assuming that if I use record selection to filter the results it should return the correct numbers?

Thanks for the thought, I will give it a go.

Curbish