Approval Processes

Housekeeping

Here is the ERD for the Approval Process Objects.  Typically reporting will be done from the Process Instance Step => Process Instance => Process Definition or from the Process Instance => Process Definition.

Process Instance Steps

Process Steps show any pending, completed, or recalled approval process steps.

SOAP API Developer Documentation

Retrieve all Process Steps that are Pending Approval

SELECT Id, ProcessInstance.TargetObjectId, CreatedBy.Name, Actor.Name, OriginalActor.Name, ProcessInstance.Status, ProcessInstance.TargetObject.Type, ElapsedTimeInDays, ElapsedTimeInHours, ElapsedTimeInMinutes
FROM ProcessInstanceWorkitem
WHERE ProcessInstance.Status IN ('Pending', 'Started')

Retrieve all Process Steps that are Pending Approval and apply to a specific object

SELECT Id, ProcessInstance.TargetObjectId, CreatedBy.Name, Actor.Name, OriginalActor.Name, ProcessInstance.Status, ProcessInstance.TargetObject.Type, ElapsedTimeInDays, ElapsedTimeInHours, ElapsedTimeInMinutes
FROM ProcessInstanceWorkitem
WHERE ProcessInstance.Status IN ('Pending', 'Started') AND ProcessInstance.TargetObject.Type = 'Opportunity'

Getting Metrics on Approval Process Completion

Process Instance SOAP API Developer Documentation

Process Definition SOAP API Developer Documentation

SELECT ProcessDefinition.Name,SUM(ElapsedTimeInDays),SUM(ElapsedTimeInHours),SUM(ElapsedTimeInMinutes),COUNT(Id),Status,TargetObject.Type
FROM ProcessInstance
WHERE ProcessDefinition.State = 'Active' AND Status IN ('Approved','Rejected')
GROUP BY ProcessDefinitionId, ProcessDefinition.Name,Status,TargetObject.Type