Linking files in my report

[QPRphil]

I have a report in which I have one large file, something like 250,000 records, of which I am selecting 3,000, which I wish to link to a much bigger file, a couple of million records in all, of which I suspect I will be selecting about 20 for each record selected from the first file.

I actually only need one record from the second file for each record from the first. Which will be the first one in the selected list with the required value in one field.

The second file is an audit file containing details of changes to the field contents of the first.
I want to be able to run this report without bringing the system or network down due to excessive database accesses. What are my best options for selecting what I want from the second file before, or during, the linking process.

 

[Naith]

Either handle this in your SQL:

SELECT 1stEntity.Field,2ndEntity.Field
FROM 1stEntity,2ndEntity
WHERE 1stEntity.JoiningCriteria = 2ndEntity.JoiningCriteria
AND 1stEntity.Row = ( SELECT 2ndEntity.Row
FROM 2ndEntity
WHERE 2ndEntity.Row = Maximum(2ndEntity.Row,ID)
GROUP BY ID)

or use a subreport to access your 2nd file's information. Place it in your main report and use MainReport.Row = Maximum(SubReport.Row,ID).

In the latter option, you would be grouping by ID in the subreport, and using Group Selection criteria to ensure that you only bring back the highest sequenced row per group.

Subreports will slow the report down, but there's no reason why you shouldn't get maximum pass to the database, ensuring that the database traffic is minimalised.

All the best - and if you need any additional help, don't hesitate to come back with some examples of what's going on.

Naith

 

[QPRphil]

Thank you Naith. I was beginning to wonder if sub-reports were an option worth pursuing on this.
My background is AS400, very traditional, so some of these things are a bit of a stretch for me. For your first solution, where abouts would you expect me to be putting that SQL?
In AS400 terms I would expect to build it into a CL program that executes my main report after preparing the data. I am conscious of the fact that my traditional approach probably won't work in a Crystal Reports scenario.

 

[Naith]

You can check and modify the SQL that Crystal generates by going to the Database menu, and selecting Show SQL Query.

If you find that you're heavily modifying the SQL, you may find it's cleaner (and undoubtedly quicker to process) to create a stored procedure which handles this for you, and use Crystal to simply query the resultset.

Ultimately, you can do this any number of ways:

1. Group by ID in the main report, and use an equal link to the ordered 2nd table. Place the information in the Group Header of the report, and suppress the details section. This will display the latest/greatest/minimum linking record from the 2nd file, depending on the order.
Drawback: Although the additional records are not displayed, they are still processed, meaning that your database query would be a very loose SQL statement which queried the maximum number of records for your report.

2. Modify the SQL in the report as per the initial post.
Drawback: Can't really think of any, as long as you're competent in SQL.

3. Subreport as per the initial post.
Drawback: Slower than straight SQL, as the subreport represents an extra database hit. But, still quicker and cleaner than returning everything.

4. Stored procedure.
Drawback: You have to know how to write stored procedures! As far as perfomance is concerned, this is the quickest option because it minimises the amount of processing left for Crystal to do.

You know what to do if you have any more questions.

SpursNaith