Clarity

Is there a way to query the cmn_audits table so each change has a start date and an end date? We're trying to create an audit report for timesheet approvers. Since Resource Managers change we need to be able to compare the timesheet approver for that timesheet week to the Resource Manager at that point in time. 

You would query CMN_AUDITS for the attribute that you are interested to find the audit record that has the MAX id (or max created_date) where the audit record's created_date is earlier than the date you are interested in.

(i.e. "get the latest audit record for my attribute that happened before the date that that I care about" )

Probably need to cater for the case where there is no audit record as well (unless you are auditing resource creation too).

(and obviously you would need to be auditing that field and not clearing down audit history )

The Status Report has similar logic to work out the 'arrows' for each of the status (ie bring back the current record, and compare to the previous record).

The jaspersoft version of the status report uses a DWH view, but below is copied from a pervious version of BO:

          ROW_NUMBER ()
          OVER (PARTITION BY rr2.odf_parent_id
                ORDER BY rr2.cop_report_date DESC, rr2.id DESC)
             report_order

The report_order will be 1 for the first record, 2 for the second record.  Check out the Status Report as the full logic should be in there, which you can then translate back for your Audit data.