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

Question

Question

Generating Report on Unassigned Tasks

asked on May 5, 2023

Hi, 

There is a process where a task gets assigned to A Team and they assign it to the respective user/another team to proceed forward. 

We would like to create a report on unassigned tasks of the Team (assigned by A team) and/or on the task assigned to a user by A team. Since everything happens within a User Task, there is noway to capture the data into SQL to generate a report in Power BI. 

Is there any way to make use of Forms DB or data from forms to generate report, so that we get an insight on the Unassigned tasks? 

0 0

Replies

replied on May 8, 2023

This might help.

This is a custom view I added to my LFForms database (note this assumes you are on version 11 Update 2 or Update 3 - there is a field that was named differently prior to 11 Update 2).

This view roughly mimics the Monitor page (when searching for all in-progress instances).

Note that it does list multiple rows of data for instances that have multiple tasks in progress, or tasks assigned to multiple users and not claimed by 1 of them.

I use this for some automated reporting and workflows, and I do use it in Power BI for some metrics as well.

There is one spot you need to update, labeled as  "ENTER_THE_EMAIL_ADDRESS_OF_YOUR_LASERFICHE_ADMINISTRATOR_HERE".

SELECT        [Process Name], [Instance name], [Started by], [Last updated], [Assigned to], [Assigned to (username)], [Assigned to (email)], [Current step], [Step start date], [Start date], [Instance ID], [Step due date]
FROM            (SELECT        instance.bp_name AS [Process Name], instance.title AS [Instance name], CASE WHEN start_user.displayname LIKE '%WORKFLOW%' THEN 'Workflow' ELSE start_user.displayname END AS [Started by], 
                                                    instance.lastacted_date AS [Last updated], CASE WHEN bp_worker_resume.status = 5 AND assigned_user_snapshot.displayname IS NOT NULL 
                                                    THEN assigned_user_snapshot.displayname WHEN bp_worker_resume.status = 5 AND assigned_user_snapshot.displayname IS NULL 
                                                    THEN assigned_user_record.displayname WHEN bp_worker_resume.status = 2 THEN assigned_user_snapshot.displayname WHEN assigned_user_team_snapshot.displayname IS NULL AND 
                                                    teams.name IS NOT NULL THEN CONCAT(teams.name, ' (Listing the Team Manager)') WHEN assigned_user_team_snapshot.displayname IS NULL 
                                                    THEN teams.name WHEN bp_worker_resume.status = 1 THEN assigned_user_team_snapshot.displayname END AS [Assigned to], CASE WHEN bp_worker_resume.status = 5 AND 
                                                    assigned_user_snapshot.username IS NOT NULL THEN assigned_user_snapshot.username WHEN bp_worker_resume.status = 2 AND assigned_user_snapshot.username IS NOT NULL 
                                                    THEN assigned_user_snapshot.username WHEN assigned_user_team_snapshot.displayname IS NULL AND team_users.username IS NOT NULL 
                                                    THEN team_users.username WHEN bp_worker_resume.status = 1 AND assigned_user_team_snapshot.username IS NOT NULL 
                                                    THEN assigned_user_team_snapshot.username ELSE 'Laserfiche Administrator' END AS [Assigned to (username)], CASE WHEN bp_worker_resume.status = 5 AND 
                                                    assigned_user_snapshot.displayname IS NOT NULL AND assigned_user_snapshot.email IS NOT NULL THEN assigned_user_snapshot.email WHEN bp_worker_resume.status = 5 AND 
                                                    assigned_user_snapshot.displayname IS NULL AND assigned_user_record.email IS NOT NULL THEN assigned_user_record.email WHEN bp_worker_resume.status = 2 AND 
                                                    assigned_user_snapshot.email IS NOT NULL THEN assigned_user_snapshot.email WHEN assigned_user_team_snapshot.displayname IS NULL AND team_users.email IS NOT NULL 
                                                    THEN team_users.email WHEN bp_worker_resume.status = 1 AND assigned_user_team_snapshot.email IS NOT NULL 
                                                    THEN assigned_user_team_snapshot.email ELSE 'ENTER_THE_EMAIL_ADDRESS_OF_YOUR_LASERFICHE_ADMINISTRATOR_HERE' END AS [Assigned to (email)], CASE WHEN bp_worker_resume.step_name IS NOT NULL 
                                                    THEN bp_worker_resume.step_name ELSE bp_steps.name END AS [Current step], CASE WHEN bp_worker_resume.assign_date IS NOT NULL 
                                                    THEN bp_worker_resume.assign_date ELSE bp_worker.update_date END AS [Step start date], instance.start_date AS [Start date], instance.bp_instance_id AS [Instance ID], 
                                                    bp_worker_resume.due_date AS [Step due date]
                          FROM            dbo.cf_bp_main_instances AS instance LEFT OUTER JOIN
                                                    dbo.cf_user_snapshot AS start_user_snapshot ON start_user_snapshot.id = instance.user_snapshot_id LEFT OUTER JOIN
                                                    dbo.cf_users AS start_user ON start_user.user_id = start_user_snapshot.user_id LEFT OUTER JOIN
                                                    dbo.cf_bp_worker_instances AS bp_worker ON bp_worker.bp_instance_id = instance.bp_instance_id LEFT OUTER JOIN
                                                    dbo.cf_bp_steps AS bp_steps ON bp_steps.step_id = bp_worker.current_step_id AND bp_steps.process_id = bp_worker.current_process_id AND (bp_steps.step_type = 'catchEvent' OR
                                                    bp_steps.step_type = 'serviceTask') AND bp_steps.is_deleted = 0 LEFT OUTER JOIN
                                                    dbo.cf_bp_worker_instnc_to_resume AS bp_worker_resume ON bp_worker_resume.worker_instance_id = bp_worker.instance_id LEFT OUTER JOIN
                                                    dbo.cf_bp_worker_instance_history AS bp_worker_history ON bp_worker_history.instance_id = bp_worker.instance_id AND (bp_worker_history.status = 'assigned' OR
                                                    bp_worker_history.status = 'reassigned') AND bp_worker_resume.resume_id = bp_worker_history.assigned_resume_id AND bp_worker_resume.owner_snapshot_id IS NULL LEFT OUTER JOIN
                                                    dbo.cf_user_snapshot AS assigned_user_snapshot ON assigned_user_snapshot.id = bp_worker_resume.owner_snapshot_id LEFT OUTER JOIN
                                                    dbo.cf_users AS assigned_user_record ON assigned_user_record.username = assigned_user_snapshot.username LEFT OUTER JOIN
                                                    dbo.cf_user_snapshot AS assigned_user_team_snapshot ON assigned_user_team_snapshot.id = bp_worker_history.target_snapshot_id LEFT OUTER JOIN
                                                    dbo.teams AS teams ON (teams.id = bp_worker_resume.team_id OR
                                                    bp_worker_resume.team_id IS NULL AND teams.id = bp_worker_history.team_id) AND bp_worker_resume.owner_snapshot_id IS NULL LEFT OUTER JOIN
                                                    dbo.team_members AS team_members ON team_members.team_id = teams.id AND team_members.member_rights = 3 AND team_members.leave_date IS NULL LEFT OUTER JOIN
                                                    dbo.cf_users AS team_users ON team_users.user_id = team_members.user_group_id
                          WHERE        (instance.status = 1) AND (bp_worker.status <> 6) AND (bp_worker_resume.status = 5 OR
                                                    bp_worker_resume.status = 1 OR
                                                    bp_worker_resume.status = 2) AND (bp_worker_resume.assign_date IS NOT NULL) AND (bp_steps.step_type IS NULL OR
                                                    bp_steps.step_type = 'catchEvent') OR
                                                    (instance.status = 1) AND (bp_worker.status <> 6) AND (bp_worker_resume.assign_date IS NOT NULL) AND (bp_steps.step_type IS NULL OR
                                                    bp_steps.step_type = 'catchEvent') AND (bp_steps.step_id IS NOT NULL) OR
                                                    (instance.status = 1) AND (bp_worker.status <> 6) AND (bp_worker_resume.status = 5 OR
                                                    bp_worker_resume.status = 1 OR
                                                    bp_worker_resume.status = 2) AND (bp_steps.step_type IS NULL OR
                                                    bp_steps.step_type = 'catchEvent') AND (bp_steps.step_id IS NOT NULL) OR
                                                    (instance.status = 1) AND (bp_worker.status <> 6) AND (bp_steps.step_type IS NULL OR
                                                    bp_steps.step_type = 'catchEvent') AND (bp_steps.step_id IS NOT NULL) AND (bp_steps.step_id IS NOT NULL) OR
                                                    (instance.status = 1) AND (bp_worker.status = 3) AND (bp_steps.step_type = 'serviceTask')) AS subquery
GROUP BY [Process Name], [Instance name], [Started by], [Last updated], [Assigned to], [Assigned to (username)], [Assigned to (email)], [Current step], [Step start date], [Start date], [Instance ID], [Step due date]

 

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.