Overview
This document provides example queries constructed from default Views (vwBA_ODS_*) and tables to extract valuable information for common business scenarios.
While they assist in query construction, they do not replace the need for you to develop your own queries due to uncertainties in specific definitions. These uncertainties may require clarification on your behalf regarding aspects like including closed/open Cases, Task types, or desired order. Despite these uncertainties, the queries explained in the document should be relatively straightforward to address.
Queries
Before presenting the queries, it is important to briefly explain the outline of the query construction process.
Each business scenario query presents a list of required attributes, along with each attribute's definition. For example, if the query needs to extract the work item identifier, the attribute to ponder is idWorkItem, and the description (which you can also find in the corresponding entities section of the ODS documentation) is The identifier of the Workitem.
Additionally, the constructed query is provided for your use, and the result of information extraction is presented as well.
Open Tasks
The Open Tasks query presents tasks belonging to a particular case that are presently open and assigned to a user.
The attributes obtained when executing this query include:
•idWorkItem: The identifier of the Workitem.
•idCase: The identifier of the Case instance.
•idTask: The identifier of the Task.
•idTaskType: The identifier of the Task type.
•idWorkItemState: The identifier of the Workitem state.
•idWorkflow: The identifier of the Process Workflow.
•idWFClass: The identifier of the Process.
•idUser: The unique identifier of the User.
•CaseNumber: The number of the Case.
•TaskName: The display name of the Task.
•ProcessName: The name of the Process.
•ProcessVersion: The version of the Process.
•EntryDate: The date and time when the Workitem was created into the system.
•EstimatedSolutionDate: The estimated date and time for resolving the Workitem.
•SLA: The SLA for the Task, which represents the defined duration expected to complete the Task.
•ElapsedTime: The elapsed duration of an open Task up to the current moment.
•ElapsedTimeWTS: The duration elapsed thus far for an open Task, considering the Working Time Schema.
•AssignedUserName: The complete name of the User assigned to the Workitem.
Result example
Upon execution, the Open Tasks query produces a result in the form of a table resembling the following:
idWorkitem |
idCase |
idTask |
idTaskType |
idWorkItemState |
idWorkflow |
idWfClass |
idUser |
CaseNumber |
TaskName |
ProcessName |
ProcessVersion |
|---|---|---|---|---|---|---|---|---|---|---|---|
20129298 |
3792775 |
11543 |
2 |
1 |
175 |
131 |
956964 |
3 |
# repeticiones |
BizTest |
1.0 |
20142465 |
3798798 |
11500 |
2 |
3 |
175 |
131 |
956964 |
6 |
GetWork |
BizTest |
1.0 |
20140506 |
3798266 |
5991 |
21 |
1 |
129 |
96 |
1038457 |
CEA23113 |
Queue: Work |
CustomerEngagementAdHoc |
1.0 |
EntryDate |
EstimatedSolutionDate |
SLA |
ElapsedTime |
ElapsedTimeWTS |
AssignedUserName |
|---|---|---|---|---|---|
2023-11-13 17:21:56.997 |
2023-11-13 17:21:56.000 |
00:00:00.0000000 |
03:31:00.4233333 |
0 |
Jorge Escobar |
2023-11-15 11:10:19.863 |
2023-11-15 11:10:19.000 |
00:00:00.0000000 |
09:42:37.5566667 |
0 |
Jorge Escobar |
2023-11-14 13:54:44.137 |
2023-11-14 13:54:44.000 |
00:00:00.0000000 |
06:58:13.2833333 |
0 |
System Account - Bizagi Queue |
Query
The query is constructed using the aforementioned attributes, along with the vwBA_ODS_WFE_WORKITEM View, which is one of the default Cases Views provided by the ODS service.
The formulated query is as follows:
select v.idWorkitem
, v.idCase
, v.idTask
, v.idTaskType
, v.idWorkItemState
, v.idWorkflow
, v.idWfClass
, u.idUser
, v.radNumber [CaseNumber]
, v.tskDisplayName [TaskName]
, v.wfClsName
, v.wfClsDisplayName [ProcessName]
, v.wfVersion [ProcessVersion]
, v.wiEntryDate [EntryDate]
, v.wiEstimatedSolutionDate [EstimatedSolutionDate]
, convert(time, dateadd(minute, t.tskDuration, 0)) [SLA]
, convert(time, getdate() - v.wiEntryDate) [ElapsedTime]
--, 0 [ElapsedTimeWTS]
, u.fullName [AssignedUserName]
from vwBA_ODS_WFE_WORKITEM v
join TASK t on t.idTask = v.idTask
join CURRENTASSIGNEE ca on ca.idWorkItem = v.idWorkitem
join WFUSER u on u.idUser = ca.idUser
where v.idTaskType in (2, 12, 21, 39, 43)
and v.wiClosed = 0
and ca.Deleted = 0
order by [ProcessName], [ProcessVersion], [TaskName], [CaseNumber]
Activities
The Activities query lists the Activities that pertain to a specific Case and assigned to a specific User, indicating the status of the each Activity (e.g., Completed, Inactive, etc.).
The attributes obtained when executing this query include:
•Activity ID: The unique identifier for the Activity.
•Case ID: The identifier for the Case to which the Activity belongs.
•Case Number: The number of the Case.
•Task Name: The display name of the Task (Activity).
•Task Type: Name of the Task's (Activity) type.
•Activity Status: The current state for the Activity.
•Closed: Boolean that identifies whether the Workitem is open or closed, where 1 represents Closed and 0 represents Open.
•Process Name: The name of the Process.
•Process Version: The version of the Process.
•Entry Date: The date and time when the Workitem was created into the system.
•Estimated Solution Date: The estimated date and time expected for resolving the Workitem.
•Solution Date: Date and time indicating when the Workitem was actually closed.
•SLA: The SLA for the Task, which represents the defined duration expected to complete the Task.
•Duration: The time elapsed between the Entry Date and the Solution Date.
•AssignedUserName: The complete name of the User assigned to the Workitem.
Result example
Upon execution, the Activities query produces a result in the form of a table resembling the following:
Activity ID |
Case ID |
Case Number |
Task Name |
Task Type |
Activity Status |
Closed |
Process Name |
Process Version |
Entry Date |
Estimated Solution Date |
Solution Date |
SLA |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
20166833 |
3816275 |
CEB23111 |
Intervene: Cancel Abort Get 360 Timer |
Event |
Completed |
1 |
Get 360 View |
1.0 |
2023-11-30 09:28:44.380 |
9999-12-31 23:59:59.997 |
2023-11-30 09:28:49.493 |
00:00:00.0000000 |
20166865 |
3816276 |
CEB23111 |
Intervene: Cancel Timer Abort Suppl Serv |
Event |
Completed |
1 |
Supplementary Services CE |
1.0 |
2023-11-30 09:28:50.963 |
9999-12-31 23:59:59.997 |
2023-11-30 09:28:59.773 |
00:00:00.0000000 |
20166889 |
3817276 |
CEB23111 |
Start work on case |
ManualState |
Completed |
1 |
Customer Engagement Branch |
1.0 |
2023-11-30 09:29:01.307 |
2023-11-30 19:29:01.000 |
2023-11-30 09:33:31.377 |
10:00:00.0000000 |
20166941 |
3816277 |
CET23121 |
Intervene: Cancel Timer Abort Suppl Serv |
Event |
Completed |
1 |
Supplementary Services CE |
1.0 |
2023-12-04 12:30:18.417 |
9999-12-31 23:59:59.997 |
2023-12-04 12:30:24.380 |
00:00:00.0000000 |
20166989 |
3816278 |
CET23121 |
Integration: SP. Transaction Closed |
Event |
Inactive |
0 |
Service Package |
1.0 |
2023-12-04 12:42:53.037 |
9999-12-31 23:59:59.997 |
NULL |
00:00:00.0000000 |
Duration |
AssignedUserName |
|---|---|
00:00:05.1133333 |
Bizagi Admon account |
00:00:08.8100000 |
Bizagi Admon account |
00:04:30.0700000 |
Jorge Escobar |
00:00:05.9633333 |
Bizagi Admon account |
NULL |
System account - CARMA |
Query
The query is constructed using the attributes mentioned above, alongside the vwBA_ODS_WFE_WORKITEM View, as follows:
select v.idWorkitem [Activity ID]
, v.idCase [Case ID]
, v.radNumber [Case Number]
, v.tskDisplayName [Task Name]
, v.tskTpName [Task Type]
, v.wiName [Activity Status]
, v.wiClosed [Closed]
, v.wfClsDisplayName [Process Name]
, v.wfVersion [Process Version]
, v.wiEntryDate [Entry Date]
, v.wiEstimatedSolutionDate [Estimated Solution Date]
, v.wiSolutionDate [Solution Date]
, convert(time, dateadd(minute, v.tskDuration, 0)) [SLA]
, convert(time, v.wiSolutionDate - v.wiEntryDate) [Duration]
, u.fullName [AssignedUserName]
from vwBA_ODS_WFE_WORKITEM v
join WISTATELOG al on al.idWorkItem = v.idWorkitem
join WFUSER u on u.idUser = al.idUser
where v.idTaskType in (2, 12, 21, 39, 43)
and al.idState = 4
and v.wiClosed = 1
union
select v.idWorkitem [Activity ID]
, v.idCase [Case ID]
, v.radNumber [Case Number]
, v.tskDisplayName [Task Name]
, v.tskTpName [Task Type]
, v.wiName [Activity Status]
, v.wiClosed [Closed]
, v.wfClsDisplayName [Process Name]
, v.wfVersion [Process Version]
, v.wiEntryDate [Entry Date]
, v.wiEstimatedSolutionDate [Estimated Solution Date]
, v.wiSolutionDate [Solution Date]
, convert(time, dateadd(minute, v.tskDuration, 0)) [SLA]
, convert(time, v.wiSolutionDate - v.wiEntryDate) [Duration]
, u.fullName [AssignedUserName]
from vwBA_ODS_WFE_WORKITEM v
join CURRENTASSIGNEE ca on ca.idWorkItem = v.idWorkitem
join WFUSER u on u.idUser = ca.idUser
where v.idTaskType in (2, 12, 21, 39, 43)
and ca.Deleted = 0
and v.wiClosed = 0
Cases
The Cases query retrieves Cases assigned to a specific User within a particular Process. It includes the Case status (Initiated, Running, Aborted, Completed) along with their creation and solution dates.
The attributes obtained when executing this query include:
•Case ID: The identifier for the Case.
•Case Number: The number of the Case.
•idWorkflow: The Workflow identifier to which the Workitem is associated.
•Creator User: The complete name of the User who created the Case.
•Parent Case ID: Identifier for the Parent Case, enabling the identification of whether a Process is a Parent and the Sub-Processes it includes.
•Creation Date: The exact Case creation date and time, saved in the server's date-time format.
•Solution Date: The exact Case solution date and time, saved in the server's date-time format.
•Estimated Solution Date: The Case solution date calculated by Bizagi when the Case was created.
•SLA: The duration expected to complete the Task.
•Process Name: The display name of the Process.
•Process Version: The version of the Process.
•Case status: The current state for the Case.
Result example
Upon execution, the Cases query produces a result in the form of a table resembling the following:
Case ID |
Case Number |
Workflow ID |
Creator User |
Parent Case ID |
Creation Date |
Solution Date |
Estimated Solution Date |
Duration |
SLA |
Process Name |
Process Version |
Case Status |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
3819822 |
CEW231226 |
74 |
Bizagi Admon account |
3819342 |
2023-12-07 11:53:40.447 |
2023-12-07 11:53:40.000 |
NULL |
23:59:59.5533333 |
00:00:00.0000000 |
Service Package |
1.0 |
Initiated |
3819823 |
CEW231227 |
74 |
Bizagi Admon account |
3819344 |
2023-12-07 12:05:47.050 |
2023-12-07 12:05:47.000 |
NULL |
23:59:59.9500000 |
00:00:00.0000000 |
Service Package |
1.0 |
Initiated |
3817826 |
22 |
175 |
Jorge Escobar |
NULL |
2023-12-05 12:16:33.513 |
2023-12-05 12:16:33.000 |
NULL |
23:59:59.4866667 |
00:00:00.0000000 |
BizTest |
1.0 |
Running |
3817837 |
23 |
175 |
Luis Camargo |
NULL |
2023-12-05 15:44:57.820 |
2023-12-05 15:44:57.000 |
NULL |
23:59:59.1800000 |
00:00:00.0000000 |
BizTest |
1.0 |
Running |
3817277 |
CEB23111 |
4 |
Jorge Escobar |
3817276 |
2023-11-30 09:33:31.437 |
2023-12-10 20:00:20.943 |
2023-11-30 09:43:31.000 |
10:26:49.5066667 |
10:27:00.0000000 |
Authenticate |
1.0 |
Aborted |
3817830 |
CET23122 |
86 |
Paul Meeser |
NULL |
2023-12-05 13:30:14.663 |
2023-12-07 14:20:42.043 |
2023-12-05 13:30:14.000 |
00:50:27.3800000 |
00:50:00.0000000 |
Customer Engagement Telephony |
1.0 |
Aborted |
3817279 |
1 |
175 |
Jorge Escobar |
NULL |
2023-11-30 12:54:47.607 |
2023-12-14 11:11:48.240 |
2023-11-30 12:54:47.000 |
22:17:00.6333333 |
22:17:00.0000000 |
BizTest |
1.0 |
Completed |
3817280 |
CEW23111 |
147 |
IBC Wealth Development |
NULL |
2023-11-30 13:12:47.210 |
2023-12-07 13:12:52.250 |
2023-11-30 13:12:47.000 |
00:00:05.0400000 |
00:00:00.0000000 |
Customer Engagement WRM |
1.0 |
Completed |
Query
The query is constructed using the using the specified attributes, alongside the vwBA_ODS_WFE_CASE View, which is another of the default Cases Views provided by the ODS service. The formulated query is as follows:
select v.idCase
, v.radNumber
, v.idWorkflow
, u.fullName
, v.idParentCase
, v.casCreationDate
, v.casSolutionDate
, v.casEstimatedSolutionDate
, convert(time, v.casSolutionDate - v.casCreationDate) [Duration]
, convert(time, dateadd(minute, v.caseDuration, 0)) [SLA]
, v.wfClsDisplayName
, v.wfVersion
, v.csName
from vwBA_ODS_WFE_CASE v
join WFUSER u on u.idUser = v.idCreatorUser
Users
The Users query fetches the list of Users from your automation project, including their personal work information such as email, location, cellphone number, and work area. Additionally, it retrieves organization-level information such as the User's boss, skills, roles, and whether they are configured as a Persona within the project.
The attributes obtained when executing this query include:
•User: The User assigned to the Workitem.
•Username: Internal user name.
•Full Name: The complete name of the User assigned to the Workitem.
•Email: The email of the User.
•Location: The name of the location of the User.
•Phone: The cellphone number of the User.
•Area: The working area of the User.
•Boss User: The complete name of the User's boss.
•Roles: Display name of the multiple roles associated with the User.
•Skills: Display name of the multiple skills associated with the User.
•Personas: Display name of the multiple Personas associated with the User.
Result example
Upon execution, the Users query produces a result in the form of a table resembling the following:
User ID |
Username |
Full Name |
Location |
Phone |
Area |
Boss User |
Roles |
Skills |
Persona |
|
|---|---|---|---|---|---|---|---|---|---|---|
7 |
X702360 |
Paul Meeser |
PMe111eser@bizagi.com |
East London |
112345 |
OM-Hub |
NULL |
Bizagi System Administrators, OM-Case Creator CEBranch, OM-Team Leader, OM-Case Creator CEEmail, OM-CE Quality Assessor, OM-Case Creator CEOMIA, OM-Case Creator CEFacilitation, OM-Case Creator Sales Fulfilment, OM-Case Creator CESRM, OM-Case Creator Report Fraud, OM-Case Creator CE Ad hoc, OM-Case Creator ManageParty, OM-Case Creator Medical Coding, OM-Case Creator FEN, OM-BizagiDeveloper, OM-Case Creator CEDigital, OM-Case Creator CEWRM |
OM-Intervene, OM-ReassignCases, OM-AbortCases, OM-Escalate, CCTier1Concierge, UWOMPMCOHIVAdmin, Digi. Robot. Money Out. NGP |
NULL |
Query
The query is crafted using the previously mentioned attributes, in conjunction with the Users (vwBA_ODS_USER) and Personas (vwBA_ODS_PERSONA) Views. The resulting query is presented below:
select v.idUser [User ID]
, v.userName [Username]
, v.fullName [Full Name]
, v.contactEmail [Email]
, v.locDisplayName [Location]
, v.contactCell [Phone]
, v.areaDisplayName [Area]
, vb.fullName [Boss User]
, stuff(
( select ', ' + r.roleDisplayName
from USERROLE ur
left outer join ROLE r on r.idRole = ur.idRole
where ur.idUser = v.idUser
for xml path('')
)
, 1, 2, ''
) AS [Roles]
, stuff(
( select ', ' + s.skillDisplayName
from USERSKILL us
left outer join SKILL s on s.idSkill = us.idSkill
where us.idUser = v.idUser
for xml path('')
)
, 1, 2, ''
) AS [Skills]
, stuff(
( select ', ' + vp.entDisplayName
from vwBA_ODS_PERSONA vp
where vp.idUser = v.idUser
for xml path('')
)
, 1, 2, ''
) AS [Persona]
from vwBA_ODS_USER v
left outer join vwBA_ODS_USER vb on vb.idUser = v.idBossUser
|
It is possible that the vwBA_ODS_PERSONA View does not exist if you have not configured Personas in your project. This View is dynamic, and its availability depends on the presence of configured Personas. Therefore, if the View is unavailable, you should use the following query instead:
Declare @CRLF varchar(4) = char(13)+char(10); declare @sSql nvarchar(max) = N''; declare @tPersona table ( idEnt integer , entName varchar(50) , entDisplayName nvarchar(50) , entSrc varchar(26) , SurrogateKey bigint , disabled bit , idUser int ); select @sSql = @sSql + N'union all select e.idEnt, e.entName, e.entDisplayName, e.entSrc, t.id' + e.entSrc + ' [SurrogateKey], su.disabled, u.idUser from BASTAKEHOLDERUSER su (nolock) join WFUSER u (nolock) on u.idUser = su.idUser join ENTITY e (nolock) on e.idEnt = su.idEnt join ' + quotename(e.entSrc) + ' t (nolock) on t.' + quotename(e.entSurrogateKey) + ' = su.idStakeholder ' + @CRLF from ENTITY e where e.entType = 4; -- Personas
if (@sSql <> N'') begin select @sSql = stuff(@sSql, 1, 10, '') select @sSql = N' exec (''insert into @tPersona ' + @sSql + ' '')'; exec (@sSql); end;
select v.idUser [User ID] , v.userName [Username] , v.fullName [Full Name] , v.contactEmail [Email] , v.locDisplayName [Location] , v.contactCell [Phone] , v.areaDisplayName [Area] , vb.fullName [Boss User] , stuff( ( select ', ' + r.roleDisplayName from USERROLE ur left outer join ROLE r on r.idRole = ur.idRole where ur.idUser = v.idUser for xml path('') ) , 1, 2, '' ) AS [Roles] , stuff( ( select ', ' + s.skillDisplayName from USERSKILL us left outer join SKILL s on s.idSkill = us.idSkill where us.idUser = v.idUser for xml path('') ) , 1, 2, '' ) AS [Skills] , stuff( ( select ', ' + tp.entDisplayName from @tPersona tp where tp.idUser = v.idUser for xml path('') ) , 1, 2, '' ) AS [Persona] from vwBA_ODS_USER v left outer join vwBA_ODS_USER vb on vb.idUser = v.idBossUser |
Last Updated 6/28/2024 11:29:25 AM