A Custom Data Sets for Unifier Data
Created and Completed BP Overtime Dataset
Subject Area: Unifier – Business Process History
Table: Created and Completed BP Overtime Dataset Fields
| Dimension | Dimension / Metrics | Attribute | Expression |
|---|---|---|---|
| Calendar | Week Name | N/A | |
| Calendar | Calendar Date | N/A | |
| Dates - (BP) | Completion Date | N/A | |
| Dates - (BP) | Creation Date | N/A | |
| Dates - (BP) | Due Date | N/A | |
| Dates - (BP) | Send Date | N/A | |
| General - (BP) | Business Process Name | N/A | |
| General - (BP) | Status | N/A | |
| General - (BP) | Business Process Type | N/A | |
| General - (BP) | Record Number | N/A | |
| General - (BP) | Title | N/A | |
| Metrics | # of BP Records | N/A | |
| Metrics | # OF BP Line Items | N/A | |
| Metrics | Item Quantity | N/A | |
| Project Portfolio | Project Portfolio Name | N/A | |
| Project Portfolio | Project Portfolio Status | N/A | |
| General - (Project) | Project Object Id | N/A | |
| General - (Project) | Project ID | N/A | |
| General - (Project) | Project Name | N/A | |
| General - (Project) | Project Phase | N/A | |
| General - (Project) | Project Type | N/A | |
| General - (Project) | Project Status | N/A | |
| General - (Project) | Project Owner | N/A | |
| Location - (Project) | Country Name | N/A | |
| Project Hierarchy | Project Name (Root Level) | N/A | |
| Project Hierarchy | Project Hierarchy Name Level 1 | N/A | |
| Derived from Week Name | Week Start Date | TO_DATETIME(XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Week Name",'yyyy-mm-dd hh:mi:ss')
|
|
| Derived from Week Name | Week End Date | TIMESTAMPADD(SQL_TSI_DAY,6,TO_DATETIME(XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Week Name",'yyyy-mm-dd hh:mi:ss'))
|
|
| # of Completed BP Records | CASE WHEN XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Completion Date" >= XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Week Start Date" AND XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Completion Date" <= XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Week End Date" THEN 1 ELSE 0 END |
||
| # of Created BP Records | CASE WHEN XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Creation Date" >= XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Week Start Date" AND XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Creation Date" <= XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Week End Date" THEN 1 ELSE 0 END |
||
| Open Cases Tag | CASE WHEN XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Business Process Name" = 'Corrective Work Orders' AND XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Status" IN ('In_Progress','Pending_Estimate','Technician_assigned','Final_WO_Review') THEN 1WHEN XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Business Process Name" = 'Preventive Work Orders' AND XSA('demouser'.'Created and Completed BP Over Time Dataset')."Columns"."Status" IN ('In_Progress','Material_Requested') THEN 1ELSE 0END |