<< Click to Display Table of Contents >> Navigation:  Low-code Process Automation > Automation - Test and Production environments > ODS Operational Data Store > Navigate and extract information from ODS > Business scenario queries > Other useful queries

Overview

This document presents a series of queries for extracting valuable information from your automation project.

 

These queries utilize default Views (vwBA_ODS_*), individual tables, or combinations thereof. They demonstrate how to construct queries to analyze real-life business scenarios comprehensively. While these queries aid in the construction process, it is still necessary for you to develop your own queries tailored to your specific needs.

 

Queries

Similar to Business scenario queries, each of the useful queries presented below lists the required attributes along with their definitions. Finally, the constructed query is provided for your use, and the extracted information is presented as well.

 

Load Analysis

The Load Analysis query enumerates the number of Cases per Process along with their respective Case statuses.

 

The attributes obtained when executing this query include:

•Workflow ID: The identifier of the Process Workflow.

•Process: The display name of the Process.

•Version: The version of the Process.

•Status: The current state for the Case.

 

Result example

Upon execution, the Load Analysis query produces a result in the form of a table resembling the following:

 

 

Query

The query is constructed using the attributes mentioned earlier, along with the vwBA_ODS_WFE_CASE View, which is one of the default Cases Views provided by the ODS service. The resulting query is as follows:

 

select v.idWorkflow [Workflow ID]
  , v.wfClsDisplayName [Process]
  , v.wfVersion [Version]
  , v.csName [Status]
  , count(1) [Number of cases]
from vwBA_ODS_WFE_CASE v
group by v.idWorkflow, v.wfClsDisplayName, v.wfVersion, v.csName;

 

If you wish to view the details, you can use the following query:

 

select v.idWorkflow [Workflow ID]
  , v.wfClsDisplayName [Process]
  , v.wfVersion [Version]
  , v.csName [Status]
  , v.radNumber [Case Number]
from vwBA_ODS_WFE_CASE v;

 

Cases going overdue

The Cases going overdue query lists the number of Cases that will become overdue in the upcoming days.

 

The attributes obtained when executing this query include:

•Process: The display name of the Process.

•Version: The version of the Process.

•Status: The current status of the Case.

•Case Number: The number of the Case.

•Solution Date: The exact Case solution date and time, saved in the server's date-time format.

•Days: The Case solution date calculated by Bizagi when the case was created.

 

Result example

Upon execution, the Cases going overdue query produces a result in the form of a table resembling the following:

 

 

Query

The query is constructed using the previously mentioned attributes, in conjunction with the vwBA_ODS_WFE_CASE View. The formulated query is as follows:

 

select v.wfClsDisplayName [Process]
  , v.wfVersion [Version]
  , v.csName [Status]
  , v.radNumber [Case Number]
  , v.casSolutionDate [Solution Date]-- v.casEstimatedSolutionDate
  , datediff (day, getdate(), v.casSolutionDate) [Days]
from vwBA_ODS_WFE_CASE v
where v.CaseClosed = 0
and (v.casSolutionDate <= getdate()
  or datediff(day, getdate(), v.casSolutionDate) between 0 and 15);

 

Activities status

The Activities status query displays the count of Activities categorized by their status and Process.

 

The attributes obtained when executing this query include:

•Workflow ID: The identifier of the Process Workflow.

•Process: The display name of the Process.

•Version: The version of the Process.

•Task ID: The identifier of the Task.

•Task: The display name of the Task.

•Activity Status: The current state of the Workitem.

 

Result example

Upon execution, the Activities status query produces a result in the form of a table resembling the following:

 

 

Query

The query is constructed using the aforementioned attributes, along with the vwBA_ODS_WFE_WORKITEM View, which is another of the default Cases Views provided by the ODS service. Presented below is the resulting query:

 

select vw.idWorkflow [Workflow ID]
  , vw.wfClsDisplayName [Process]
  , vw.wfVersion [Version]
  , vw.idTask [Task ID]
  , vw.tskDisplayName [Task]
  , vw.wiName [Activity status]
  , count(1) [Number of activities]
from vwBA_ODS_WFE_WORKITEM vw
where vw.wiClosed = 0
group by vw.idWorkflow, vw.wfClsDisplayName, vw.wfVersion, vw.csName, vw.tskDisplayName, vw.wiName;

 

Average duration

The Average duration query calculates and lists the average duration of closed Activities for each Process.

 

The attributes obtained when executing this query include:

•Workflow ID: The identifier of the Process Workflow.

•Process: The display name of the Process.

•Version: The version of the Process.

•Task ID: The identifier of the Task.

•Task: The display name of the Task.

•Activity Status: The current state of the Workitem.

 

Result example

Upon execution, the Average duration query produces a result in the form of a table resembling the following:

 

 

Query

The query is structured with the specified attributes, along with the vwBA_ODS_WFE_WORKITEM View. Provided below is the formulated query:

 

select vw.idWorkflow [Workflow ID]
  , vw.wfClsDisplayName [Process]
  , vw.wfVersion [Version]
  , vw.idTask [Task ID]
  , vw.tskDisplayName [Task]
  , vw.wiName [Activity status]
  , avg(datediff(hour, vw.wiEntryDate, vw.wiSolutionDate)) [Duration]
from vwBA_ODS_WFE_WORKITEM vw
where vw.wiClosed = 1
and vw.idTaskType in (2, 12, 21, 39, 43)
group by vw.idWorkflow, vw.wfClsDisplayName, vw.wfVersion, vw.idTask, vw.tskDisplayName, vw.wiName;

 

 

Work in progress

The Work in progress query displays the status of Users assigned to Activities, organized by Activity and Process.

 

The attributes obtained when executing this query include:

•User: The complete name of the User assigned to the Workitem.

•Process: The display name of the Process.

•Version: The version of the Process.

•radNumber: The number of the Case.

•Task: The display name of the Task.

•Activity status: The current state of the Workitem.

 

Result example

Upon execution, the Work in progress query produces a result in the form of a table resembling the following:

 

 

Query

The query is crafted with the specified attributes, in conjunction with the vwBA_ODS_WFE_WORKITEM View. Below is the formulated query:

 

select u.fullName [User]
  , vw.wfClsDisplayName [Process]
  , vw.wfVersion [Version]
  , vw.radNumber
  , vw.tskDisplayName [Task]
  , vw.wiName [Activity status]
from vwBA_ODS_WFE_WORKITEM vw
join CURRENTASSIGNEE ca on ca.idWorkItem = vw.idWorkitem
join WFUSER u on u.idUser = ca.idUser
where vw.idTaskType in (2, 12, 21, 39, 43)
and ca.Deleted = 0
and vw.wiClosed = 0
order by u.fullName, vw.wfClsDisplayName, vw.wfVersion, vw.radNumber, vw.tskDisplayName;

Last Updated 6/28/2024 11:29:44 AM