Reporting from WEBCON BPS Database

Facebooktwitterpinterestlinkedinmail

Applies to version: 8.x; Author: Bartłomiej Spyrka

1. Introduction

1.1.        Content

This document contains piece of information about data kept in workflow database.

Document is focused on two primary SQL tables:

1)       WFElements

Table contains information about data provided by uses due to filling the forms (process).

2)      WFElementDetails

Table contains data provided by users in Field ‘Item List’. Because of Item list ability to storage multiple rows, this data has to be saved in dedicated SQL Table.

All of information contained in this document bases on sample process containing different type of data (text, numbers, choice fields etc.). In the example we will show how to find database columns according to Webcon Designer steps, fields’ workflows configuration.

2. Process – browser view

2.1 General

To make some things simple, we have prepared sample process and to make things clear it will be used and screened in next sections.

1
WFElements and WFElementDetails marked in color

3. Process – BPS Designer view

3.1 General

Screen below shows view of process configuration in BPS Designer Studio.

2
Overall process view

3.2 Steps

According to any process created in BPS, each includes some stages like:

1)      Creating order

2)      Manager approval

3)      Ordered

4)      Delivered

Steps listed above comes from single workflow. To get information about current step we need to know ‘Step ID’.

3
Workflow steps and IDs

3.3 Document types

4

Document types are used to filter data from databases when we want to report only a part of database, for e.g. only orders without Invoices or HR things. Thanks to that we may focus on essentials.

3.4 Fields

Each field is represented in two ways:

1)      On the view side – this is the view that user sees at browser (text fields, choice fields, numbers etc.)

2)      Second side is data kept in database. Each field has its own representation in SQL database tables.

  • Fields are kept in WFElements table
  • Item lists elements are kept in WFElementDetails table

3.5 BPS Designer view

Now we have first simple division made on two tables from two sides – user view and studio view. It will help us to define more detailed information.

To find out where are Requestor or Manager information, we need to use Studio interface. Simply set focus on field – Requestor:

5
Field definitions

At the screen above we have three tags:

1)      The field which is currently focused

2)      Name of the database column in WFElements table

3)      ID – this is the unique value of field definition

It is worth to mention that ‘Item list’ field (only definition) is kept in WFElements table, but its columns are kept in WFElementDetails – see below.

When we talk about Item lists we need to use WFELementDetails table

6
Definition of Item list in Studio view

1)      Item list – field definition

2)      ID – field ID – it is needed when multiple item lists at one document to separate data.

To get information about data kept in ‘Item lists’ column we need to set focus on field ( column for e.g. Category)

7
Item list – Studio view

1)      Name of the column in Item list

2)      Database column In WFElementDetails SQL table

3.6 Common view

Ok, as we now know both views – user and configuration, it is good to see pieces together

8

4. Database queries

4.1 General

Once we know where the data is we can write some simple queries in Microsoft Management Studio to get them and use for reporting.

4.2 Database

Since 8.0 version is released Workflow database is separated between three databases:

1)      Configurations

2)      Attachments

3)      Content (business data)

To get business data, for reporting we need to use last database listed below. Database name is set in workflow installation process.

4.3 Extracting data

4.3.1 WFElements table

When we want to get simple data like Requestor, ID (document number unique for whole database represented by WFD_ID column), document Signature or Order time we should write following Query:

9
WFElements query
10
WFElements query – part 2

Second part contains data that user provided in form fields (without Item lists). As you can see, at the Requestor, Manager or CCPerson we have field construction

  1. ID – unique for data source
  2. # – workflow separator between a and c
  3. DisplayName – value displayed in user view

To make data look better we can use some standard database functions:

1)      Dbo.clearwfelemid – returns data located between first encountered # – sign in provided string as a parameter

2)      Dbo.clearwfelem – returns data located after first encountered # – sign in provided string as a parameter

Below you can see an example of function usage.

11
Database function example

4.3.2 WFElementsDetails table

To get data from ‘Item lists’ we need to write query according to WFElementDetails table

12
Simple WFElementDetails query

As you can see query above is similar to query written in WFElements table .The difference is on:

1)      Other table in WFElementDetails

2)      WHERE clause – because, WFElementDetails is related to WFElements. Both tables are connected on keys

  1. WFD_ID In WFElements is located in DET_WFDID (like parent) in WFElementDetails table
  2. DET_WFCONID – located in WFElementdetails – it is the item list ID (please see Item list – definition in Studio View)

To get data from item list we can write following query

13
WFElementDetails query – part 2

To get data from both tables at once we can write SQL query with simple join formula, which looks as follow:

14
Data from both tables gathered together

4.3.3 WFSteps table

To get information about current status of order we can use WFD_STPID (please see at Figure 3. Workflow steps and IDs) column from WFSteps and join to WFSteps SQL table as follows:

15
Join with WFSteps table

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *