Hey Folks!

I just wanted to share something that I found useful just in case anyone else finds themselves in a similar situation.

We wanted to be able to easily report on tasks metrics for one of our groups to show how many items were being completed, how many approved, how many rejected etc.

Laserfiche has some decent built-in reports to show those metrics, but unfortunately we did not have the licenses to spare for every user that wanted to be able to view the reports, and it requires a full license to get into the reporting part of Forms. So, I decided to map the data out as best as I could figure out and reproduce the reports in Tableau.

Here is the View I put together in the Laserfiche database. (I am perfectly aware of how sloppy it is)

SELECT dbo.cf_business_processes.bp_id,
dbo.cf_business_processes.name AS BP_Name,
dbo.cf_bp_worker_instance_history.finish_date,
dbo.cf_bp_steps.step_id,
dbo.cf_bp_steps.name AS Step_Name,
us.displayname AS Owner_User,
cus.displayname AS Assigned_User,
dbo.cf_bp_worker_instance_history.start_date,
dbo.cf_bp_worker_instances.bp_instance_id,
dbo.task_metrics_mapping.status,
subUser.displayname AS Submitter,
dbo.cf_bp_main_instances.title,
dbo.cf_bp_worker_instance_history.instance_id,
dbo.cf_bp_worker_instance_history.submission_id,
dbo.cf_submissions.action
FROM dbo.cf_user_snapshot AS subUser RIGHT OUTER JOIN
                         dbo.cf_submissions ON subUser.id = dbo.cf_submissions.user_snapshot_id RIGHT OUTER JOIN
                         dbo.cf_business_processes RIGHT OUTER JOIN
                         dbo.cf_bp_worker_instance_history ON dbo.cf_business_processes.bp_id = dbo.cf_bp_worker_instance_history.process_id ON dbo.cf_submissions.submission_id = dbo.cf_bp_worker_instance_history.submission_id AND 
                         dbo.cf_submissions.history_id = dbo.cf_bp_worker_instance_history.history_id LEFT OUTER JOIN
                         dbo.cf_bp_main_instances RIGHT OUTER JOIN
                         dbo.cf_bp_worker_instances ON dbo.cf_bp_main_instances.bp_instance_id = dbo.cf_bp_worker_instances.bp_instance_id ON 
                         dbo.cf_bp_worker_instance_history.instance_id = dbo.cf_bp_worker_instances.instance_id LEFT OUTER JOIN
                         dbo.cf_user_snapshot AS us ON dbo.cf_bp_worker_instance_history.owner_snapshot_id = us.id LEFT OUTER JOIN
                         dbo.cf_user_snapshot AS cus ON dbo.cf_bp_worker_instance_history.target_snapshot_id = cus.id LEFT OUTER JOIN
                         dbo.task_metrics_mapping ON dbo.cf_bp_worker_instance_history.history_id = dbo.task_metrics_mapping.history_id FULL OUTER JOIN
                         dbo.cf_bp_steps ON dbo.cf_bp_worker_instance_history.process_id = dbo.cf_bp_steps.process_id AND dbo.cf_bp_worker_instance_history.step_id = dbo.cf_bp_steps.step_id

Essentially this allows you to drop a single view into Tableau, and report on several major Forms Metrics.

Here is a basic Dashboard that I was able to generate based on a single DB View:

I am open to suggestions on ways I could improve the query. What are some ways that you have used LF database data?