Getting Sub Report To Return Correct Record 2

[BradW]

This is a bit complex, I'll try to explain it clearly and simply. I have an Oracle database with multiple tables and I am trying to pull specific matching info from several tables. I have no control over the database and can make no changes to the data, so all this must be done in Crystal (10 Professional).

I have a main report with several tables attached, one table contains information about incidents that happen (date, time, where, etc), a second table contains information about vehicles (make, model, damage, etc) involved in those incidents. There can be up to 5 vehicles related to an incident, and I have all of the vehicles relating to the incidents correctly and reporting the way I want - this report has one page per vehicle (it is a letter to vehicle owner).

Now the problem, there is a third table in use that has the status of the incident. The status can change over time. There is a sequence number (1 through whatever, in order) showing the sequence of changes. I need to get one field from the last record (highest sequence number) relating to the particular vehicle on the specified incident and show the current status of the incident on the report. I can get the fist status fine, but am having touble getting the last status record to consistently display.

Of course, the indident table has one record per incident, then there is a 1 to many relationship to vehicles and then another one to many relationship from vehicles to statuses.

I am using a sub report to get the status, and this works fine until the status has changed and it still pulls the first status rather than the current status (with the highest sequence number)

Thanks for reading this far and thanks for any help!

 

[synapsevampire]

I doubt that you need a subreport. Join the

Group the report by the incident, and sort by the sequence number.

Suppress the group header and the details and place the fields in the group footer.

Of course I'm guessing here because you elect not to share how the tables/subreports are joined/linked.

The best way to demonstrate requirements is to post example data and the expected output, rather than trying to describe it.

-k

 

[synapsevampire]

btw, in your current design, you could go into the subreport and group by the incident, and in the Report->Selection Formula->group place:

maximum({table.sequencenumber},{table.incident})

Now it will return the maximum only.

It'll prove much slower though.

-k

 

[lbass]

SV means:

{table.sequencenumber} = maximum({table.sequencenumber},{table.incident})

-LB

 

[synapsevampire]

<nodding>

What LB said

-k

 

[BradW]

SV and LB, thank you both very much, solved my problem and made my boss very happy!

I would have posted code and more specific detail, but I'm under NDA and so I had to try and be as specific and I could wihtout posting actual information that I can't. THanks so much for helping despite my vagueness!

 

[synapsevampire]

In the future post fake data, no nda prevents this, and most are under NDAs here and manage to supply technical information.

-k