There are two ways that I know of to do this:

 

1.  Instead of linking tables together in your report, use a Command.  A Command is just a SQL select statement.  The trick is that you MUST include all of the fields that are required for your report.  There will be a big performance hit if you link a command to either another command or to any tables.  If you need to filter your data based on parameters, you must create the parameters in the command editor and use them in the Where clause of the command - parameters create in the main report will not work.  However, in the main report you can modify parameters created in the command editor to make them dynamic or or set other properties.  Using the Select Expert to filter a command will also cause a performance issue.

 

2.  Create a View in the database that will pull all of the data for the report.  Then build the report based on the view.

 

The basic logic for the query/command will be something from this:

 

Select <all required fields>

from <primary table>

  <inner or left> join <next table> on <linking fields>

  ...

  inner join (

    select personId, max(assessment_date) as last_date

    from assessment_table

    group by personId) as max_date

    on assessment_table.personId = max_date.personId

      and assessment_table.assessment_date = max_date.last_date

where <filter conditions>

 

Using the inner join from the data to the max_date query will automatically filter the assessments to only the most recent.

 

-Dell

inner join (
  select 
    ROOT_OFFENDER_ID, 
    max(NEXT_REVIEW_DATE)  as maxReviewDate
  from OFFENDER_BOOKINGS
  where NEXT_REVIEW_DATE is <= {?Next Review Month}
  group by  ROOT_OFFENDER_ID) as reviewDates
  on OFFENDER_ASSESSMENTS.ROOT_OFFENDER_ID = reviewDates.ROOT_OFFENDER_ID
    and OFFENDER_ASSESSMENTS.NEXT_REVIEW_DATE =reviewDates.maxReviewDate

 

Please post the whole SQL that you're using in the Command and I'll see what I can do to help you with it.

 

-Dell