Is it possible to get a date added filter when using the boards table in Reports?

[Adam Toms]

Hello,

I am trying to create a report that runs on a daily basis, for changes that were authorised during that day up until 16:15. I would then set the report to auto run at 16:20 each day uploaded via document manager.

Our change process is very mature, so standard and Normal with an impact of minor - do not require CAB approval. As there are different processes different changes follow I wish to create reports that are as efficient as possible.

I have looked at the change requests table, and there doesn't seem to be any option in there for the word authorised.

After having a bit more of a think, All changes that get approved, go onto our Change Management Board, and all changes regardless of process end up in the board lane called Awaiting Change Implementation.

I thought it could get away with inner joining the requests table and use the date modified feature, however that way does produce some anomalies. As any change modified in that lane will appear in the report, but that might simply be a member of staff adding an update to the request.

Is there a way as I haven't been able anything in the Board Lanes table that records the time a request has moved into the lane called Awaiting Change Implementation?

Looking at the time line of the ticket is seems to record the fact that the request has moved into the lane in the Change Management Board, but there seems to be no obvious way that I can find that I can use dates/ times a Change Request has moved lanes.

 

As always any help would be gratefully appreciated

Many Thanks.

Adam

[Steven Boardman]

@Adam Toms there is a column h_bpm_authorised in the main h_itsm_requests table - so if you use the change entity to generate your report this should be available to you, or if you build a sql report you can join the this and the change table.  

* There is a BPM operation to mark a request as approved. 

Assuming that all authorisations are driven from approval tasks - then you could in your business processes write the completion date / time to a custom field.

As you may have multiple approvals / weightings etc you could use the 

Get Last Authorisation Details node, after the change is approved - this will include in it's output params the completion date/time

Use the variable picker and update a custom field (using a date/time field custom field)

Name the Field Something like  Approval Date / Time

Then in your report you could look at the all requests where h_bpm_authorised = authorised AND Approval Date / Time is greater than Start of Today - assuming you ran this at 4:20 or 4:15 it would only include those authorised by that time, so no need to have an endofday value?

Obviously this would only work for change requests going forward where the BPM is writing to a custom field where you can use the date / time to filter the approved changes on, but may work for you?

[Adam Toms]

Thanks very much @Steven Boardman for your swift reply. I will take a look into this. This looks like a much better option than going down the change management board lane route, that I was originally looking into.

Many Thanks.

Adam