You are viewing limited content. For full access, please sign in.

Question

Question

Pull report that shows the start time of the whole process, and the completion time of a specific task.

asked on May 9, 2022

I would like to pull a report that shows the time between when the process was started, and a specific task is completed withing the process. There are about 10-15 more tasks after this one, so I cant just pull the duration of the entire process.

Is there a way to get this information from either SQL or somewhere? 

 

I did set up some variables to capture the date and time when the process starts and when that task ends, so it will work for future processes, but I would love to be able to pull a history.

I am on version 10.4.5

0 0

Answer

SELECTED ANSWER
replied on May 11, 2022 Show version history

Hi Justin,

The instance history data is stored in Forms database table [cf_bp_worker_instance_history] and [cf_bp_worker_instances]. 

Following is a simple SQL query I developed to query the start date of start step and finish date of a specified step given step ids and process name, that you may start to build report with

--update process_name, start_step_id, task_step_id variable value before use

declare @PROCESSNAME nvarchar(50) = 'process_name'
declare @STARTSTEPID int = start_step_id
declare @TASKSTEPID int = task_step_id

SELECT T2.bp_instance_id, MIN(T1.start_date) AS start_date, MAX(T1.finish_date) AS task_finish_date
FROM cf_bp_worker_instance_history T1
INNER JOIN cf_bp_worker_instances T2
ON T1.instance_id = T2.instance_id AND T2.bp_instance_id IN
	(SELECT bp_instance_id
	 FROM cf_bp_main_instances
	 WHERE bp_name = @PROCESSNAME)
WHERE step_id = @STARTSTEPID OR step_id = @TASKSTEPID
GROUP BY bp_instance_id
ORDER BY bp_instance_id

 

1 0
replied on May 13, 2022

Thank you for this!

I was able to get the code to run. It appears it is pulling the start and end time of the STARTSTEPID, and ignoring the TASKSTEPID. The times are less than a second apart. 

I know SQL just enough to tweak some code, but I cant figure out how to make that adjustment.

0 0
replied on May 15, 2022

Hi Justin,

The script assumed that the instance has both start step and task step completed. So if the task step is not completed/started, the finish date column (MAX(T1.finish_date)) will return assigned date of user task/finish date of start step.

Could you check whether it is the situation you met?

 

You may filter instances by status first, or query start step and user task step separately then combine the result.

0 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.