Hi Matthew, 

Currently it’s not possible on the version we use either. We are doing something similar with a view where we can see tasks for users (active) and some info, but not at a point where it looks like the reports with forms process variables. That would really be much appreciated if you can!

This is copied directly from SQL Server, and it has a specific way to format Views, which I think is not ideal for readability, but you should be able to just paste directly to SQL Server and run it to test...

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 'administrator email address goes 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' 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_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]

Thanks Matthew, I will give this a try. I think Laserfiche will release something in the future to help with more flexible reporting ☺️

Hey Matthew, 

Thank you for this, It really works great! Wanted to know, is there a possibility to change the code to see not only active, but completed tasks as well?

That is possible, but I don't have a query like that ready to go.

I would recommend finding a process and task in your system that you want to identify, and then gradually tweak the query until you can get it to show what you need.

Perhaps take everything in the subquery (so it isn't grouping), and replace SELECT {values} with SELECT * so you can see everything connected to those tables that meets the WHERE {crieria} - then tweak the WHERE {criteria} one part at a time.