One thing however on business queries: the examples mostly start with a BPM process and then query from the Process of Activity Data Object as created on deployment of the particular process. How-ever, often you'll find that you want to filter on a certain date range, for instance process started less than a day or a week ago. Or activities running less than an hour, or between an hour and two hours, two and three hours or longer. But then you'll find that you can't filter in the Business Queries on a date function. For instance you can't filter on something like '{process start date} < now() - 7'.
To solve that you can add extra Calculated Fields that return yes or no or 1 or 0 if a certain date calculation condition is met. To do so go to the administration tab of the BAM Composer (eg. http://darlin-vce-db:7006/bam/composer):
Click for instance on the CustomerSurvey Activity, then on the tab 'Calculated Fields' and then on 'Add Calculated Field':
You need to provide a name that has no spaces but only lowercase or uppercase letters and underscores. Then you can provide a name that is shown in the designer and in flat queries. The column type can be measure, dimension or attribute, but in this case you'll want attribute, to be able to filter on it. In this case I returned 'J' or 'N' for 'Ja' (Yes) or 'Nee' (No). This is sufficient for filtering. But if you want to count/summarize instances that are running less than one hour, or between one or two hours, etc., then you might want to return 1 or 0.
Click on OK and then save:
By clicking on the pencil-icon you can edit the field.
I'll provide some other examples that I found helpfull for the activity dataobject:
| Field Name | Display Name | Column Type | Expression | description |
|---|---|---|---|---|
| activity_started_lt_week_ago | Activity started less than week ago | Attribute | IF(DATEDIFF(SQL_TSI_DAY,{Activity Start Time},now())<=7)THEN("J")ELSE("N") | Is the activity started at most 7 days ago? (J/N) |
| activity_started_lt_day_ago | Activity started less than day ago | Attribute | IF(DATEDIFF(SQL_TSI_HOUR,{Activity Start Time},now())<=24)THEN("J")ELSE("N") | Is the activity started at most 24 hours ago? (J/N) |
| Activiteit_Looptijd_min | Activiteit Loop tijd (min) | Attribute | IF({Activity Instance Status}=="ACTIVE")THEN(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now()))ELSE(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},{Activity End Time})) | Actual running time of the activity instance. If the instance is active, than the result is the difference between the start time and the current time (NOW()), otherwise it is the difference between de start time and the end time. The "Activity Running Time" is aparently different from the predefined runningtime field, because of the sampling moments. Sometimes the Running time is near to zero, while the instance is still active. |
| Activiteit_Looptijd_lt_1hr | Activiteit Looptijd < 1 uur | Attribute | IF({Activity Instance Status}=="ACTIVE")&&(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())<60 td=""> | Is Activity Running less than an hour? |
| Activiteit_Looptijd_lt_2hr | Activiteit Looptijd < 2 uur | Attribute | IF({Activity Instance Status}=="ACTIVE")&&(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())>=60&&DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())<120 td=""> | Is Activity Running more than one but less than two hours? |
| Activiteit_Looptijd_lt_3hr | Activiteit Looptijd< 3 uur | Attribute | IF({Activity Instance Status}=="ACTIVE")&&(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())>=120&&DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())<180 td=""> | Is Activity Running more than two but less than three hours? |
| Activiteit_Looptijd_gt_max | Activiteit Looptijd > max | Attribute | IF({Activity Instance Status}=="ACTIVE")&&(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())>180)THEN(1)ELSE(0) | Is Activity Running 3 hours or longer? |
| Activiteit_is_open | Activiteit is open? | Attribute | IF({Activity Instance Status}=="ACTIVE")THEN("J")ELSE("N") | Is the activity still Open? |
For the process Data Objects these are a good starting point:
| Field Name | Display Name | Column Type | Expression | description |
|---|---|---|---|---|
| Process_Running_Time_Min_attr | Process Running Time (Min) Attr | Attribute | {Process Running Time (millisecs)}/600000 | Number of minutes a process is executed. There is another comparable field already defined, but that is of type 'Measurement'. You can't use that for analytid functions as AVG, MIN, MAX, etc. |
| process_started_lt_week_ago | Process started less than week ago | Attribute | IF(DATEDIFF(SQL_TSI_DAY,{Process Start Time},now())<=7)THEN("J")ELSE("N") | Is the process instantie started at most 7 days ago? (J/N) |
| process_started_lt_day_ago | Process started less than day ago | Attribute | IF(DATEDIFF(SQL_TSI_HOUR,{Process Start Time},now())<=24)THEN("J")ELSE("N") | Is the process instance started at most 24 hours ago? (J/N) |
| Process_Looptijd_in_min | Process Looptijd (min) | Attribute | IF({Process Instance Status}=="ACTIVE")THEN(DATEDIFF(SQL_TSI_MINUTE,{Process Start Time},now()))ELSE(DATEDIFF(SQL_TSI_MINUTE,{Process Start Time},{Process End Time})) | Actual running time of the process instance. If the instance is active, than the result is the difference between the start time and the current time (NOW()), otherwise it is the difference between de start time and the end time. The "Process Running Time" is aparently different from the predefined runningtime field, because of the sampling moments. Sometimes the Running time is near to zero, while the instance is still active. |
The expressions are based on info I got from the user guide. You can find the 12.2.1 user guide over here. The 12.1.3 can be found here. Look for chapter 13.8 (in the 12.2.1 user guide) or 14.8 (in the 12.1.3 user guide).
No comments:
Post a Comment