Creating Custom Filter in PRS

This article explains how to create a custom filter for an existing filter entity in PRS (Process Reporting Services).  PRS is used to create reports from the data stored in BAM (Business Activity Monitor) database. This article explains the steps to create custom filter for the existing Filter Entity ‘Activity Execution’.

    Requirement:

            In this article we are creating a filter condition to retrieve the latest activity instance from the workflows.

          For example: Consider we have completed the activities ‘Activity-1’ and ‘Activity-2’ in a workflow.  BAM will have entries for both the activities. When we generate a report to display activity details for a particular process, result will contain details about both the activity instances of the particular process.  In this example we are creating a filter to return only ‘Activity-2’ as the result.

    Steps to create custom filter:

          In this example we are creating a filter for Filter Entity ‘Activity Execution’.  We need to create the following 4 objects using DQL

·         Create filter tab definition (bami_filter_definition)

·         Create filer token item (bami_flt_filter_token_items)

·         Create filter entity relationship (bami_flt_filter_entity)

·         Create filter tree item (bami_filter_tree_item)

Create Filter Tab Definition:

In our example we are creating a separate tab definition. It is not mandatory. We can use any existing tab definitions also.

The following is the query to create Filter Tab

create bami_filter_definition object

set object_name='Example Filter Tab',

set acl_name='BAM Admin ACL',

set acl_domain='dm_dbo',

link '/System/BAM/Custom Entities'

object_name : must be Unique in the BAM

All objects should have acl_name = ‘BAM Admin ACL’

all 4 objects must be linked to  '/System/BAM/Custom Entities'

Create filter token item:

create bami_flt_filter_token_items object

set object_name='1',

set filter_id=(select r_object_id from bami_filter_definition where

object_name='Example Filter Tab'),

set filter_item_expr_name='INCLUDE-EXAMPLE-FILTER',

set acl_name='BAM Admin ACL',

set acl_domain='dm_dbo',

link '/System/BAM/Custom Entities'

object_name               : unique identifier for this filter item

filter_id                      : r_object_id of the bami_flt_filter_definition object.

filter_item_expr_name   : This string is used as filter expression. Value should be in capital and without space

Create filter entity relationship :

create bami_flt_filter_entity object

set entity_type='Activity Execution',

set filter_id=(select r_object_id from bami_filter_definition where object_name='Example Filter Tab'),

set filter_item_id=(select fti.r_object_id from bami_flt_filter_token_items

fti, bami_filter_definition fd where fti.filter_id=fd.r_object_id and

fd.object_name='Example Filter Tab' and fti.object_name='1'),

set filter_entity_token_name='EXA',

set filter_entity_from_clause='EXEC_ACTIVITY_INSTANCE e',

append flt_where_clause_oracle='EntityTable0.ACTIVITY_ID = e.ACTIVITY_ID and e.ACTIVITY_ID in (select max(ACTIVITY_ID) from EXEC_ACTIVITY_INSTANCE  group by INSTANCEID)',

append flt_where_clause_mssql='EntityTable0.ACTIVITY_ID = e.ACTIVITY_ID and e.ACTIVITY_ID in (select max(ACTIVITY_ID) from EXEC_ACTIVITY_INSTANCE  group by INSTANCEID)',

append flt_where_clause_db2='EntityTable0.ACTIVITY_ID = e.ACTIVITY_ID and e.ACTIVITY_ID in (select max(ACTIVITY_ID) from EXEC_ACTIVITY_INSTANCE  group by INSTANCEID)',

append flt_where_clause_sybase='EntityTable0.ACTIVITY_ID = e.ACTIVITY_ID and e.ACTIVITY_ID in (select max(ACTIVITY_ID) from EXEC_ACTIVITY_INSTANCE  group by INSTANCEID)',

set flt_sql_column_name_oracle='''True''',

set flt_sql_column_name_mssql='''True''',

set flt_sql_column_name_db2='''True''',

set flt_sql_column_name_sybase='''True''',

set filentity_token_long_name='EXAMPLE-FILTER',

set filter_entity_order_by=20,

set acl_name='BAM Admin ACL',

set acl_domain='dm_dbo',

link '/System/BAM/Custom Entities'

entity_type                        : The filter entity name

filter_id                             : r_object_id of the bami_flt_filter_definition object.

Filter_item_id                     : r_object_id of the bami_flt_filter_token_items object.

filter_entity_token_name     : Token name used in filter expression.

filter_entity_from_clause     : tables names that are part of the query

flt_sql_column_name_oracle,

flt_sql_column_name_mssql, :  filter conditions defined by user

flt_sql_column_name_db2,

flt_sql_column_name_sybase

flt_where_clause_oracle,

flt_where_clause_mssql,        :  where clause of filter expression.

flt_where_clause_db2,

flt_where_clause_sybase

filentity_token_long_name      : This field is shown in the label of the filter tab within PRS.

Create filter tree item:

create bami_filter_tree_item object

set object_name='Include Example Filter',

set filter_id=(select r_object_id from bami_filter_definition where

object_name='Example Filter Tab'),

set tree_item_last_level=0,

set tree_item_type_select='N',

set data_type='Boolean',

set for_web=0,

set expression[0]=':FilterTokenName.INCLUDE-EXAMPLE-FILTER = ''True''',

set acl_name='BAM Admin ACL',

set acl_domain='dm_dbo',

set internal_code ='example_filter',

link '/System/BAM/Custom Entities'

object_name            : This object name is the label shown in filter tree

filter_id                   : r_object_id of the bami_flt_filter_definition object.

tree_item_last_level : index level of tree item

expression[0]           : expression displayed if this filter is selected in PRS

Refresh entities in BAM database:

We need to restart the BAM database or trigger refresh entity task to get our filer in PRS.

Refresh entity task can be triggered by running the following sql in BAM database

Update I_BAM_SERVER_CONFIG set BLOCK=0 WHERE SERVERNAME='RefreshEntities'

    Output :

The following is the query generated for the report:

select EntityTable0.INSTANCEID as "myid0", EntityTable0.ACTIVITY_ID as "myid1", EntityTable0.ID as "myid2", EntityTable0.NAME as "field1", EntityTable0.ACTIVITY_ID as "field2"   from  V_ACT_EXECUTION EntityTable0   where 1=1 and EntityTable0.INSTANCEID in 

( select e.INSTANCEID  from EXEC_ACTIVITY_INSTANCE e where EntityTable0.ACTIVITY_ID=e.ACTIVITY_ID and e.ACTIVITY_ID in (select max(ACTIVITY_ID) from EXEC_ACTIVITY_INSTANCE  group by INSTANCEID) and 'True' = 'True' )   

The above query generated returns only the last activity instances of workflows.

    How the query is formed :