QUESTION

How can we report on time adjustments, esp. the fields 'adjusted by' and 'adjusted on' would be required. But these fields are not available in report Designer and also not in Cognos.


ANSWER
In the database, adjustments are not simply a few more fields in the time record, like the 'adjustedon' or 'adjustedby' that is required. In fact, a time record can be adjusted several times and each of these adjustments has it's own creation date, submission date, approval date and each of these adjustments can be invoiced separately. That's why it is not possible to just add a few fields to the time record to handle adjustments.

Instead, in Changepoint, an adjustment is an extra entry in the time table and thus the adjustment can have it's own fields for when it was created, submitted, approved, invoicing etc. To link the adjustments with the parent time record, the adjustment time record has a field AdjustmentTimeParent which holds the ID of the time entry being adjusted. Both the parent and the child time record(s) have a field adjustmentTimeStatus which is 'P' for adjusted time entry and 'A' for the adjustments. On top of that, the parent time record has a field adjustedRegularHours which holds the total of all adjustments.

It is impossible to model this into a single data provider/query subject. What is available in our data provider is:

  • TimeRecordLevel: A or P if the time record is an adjustment or an adjusted parent time entry.
  • AdjustedRegularHours (or days): The total amount of adjustments for an adjusted time entry.

To report on adjustments, custom fields (using SQL) can be added to your report. Alternatively, a custom view can be used (for report designer reports) or custom queries in Cognos.