Building Workflow Analytical Report

This blog post helps you to create an analytical report based on your chosen parameters . Additionally, this blog also shows you how to generate workflow reports based on the average time taken by each approver to approve a workflow item.

Users with role: Analytics Specialist can create such analytical reports.

Following is a broad overview of the major steps involved :

1. Create Custom CDS Views (App: Custom CDS Views)

◉ CDS-Views provide the necessary data for the analytical queries

2. Create Custom Analytical Query (App: Custom Analytical Query)

◉ Analytical queries define the design of the actual application

3. Create Custom Application (App: View Browser)

◉ Enables the release of analytical queries as applications

4. Assign Custom Application to Catalog (App: Custom Catalog Extension)

◉ The assignment embeds the newly created application in the general role and catalog concept

Here is a step-by-step tutorial to assist you through the steps:

Creating Custom CDS Views

CDS 1: YY1_PR_WORKFLOW_REP

First we will be creating a custom CDS view in-order to fetch the workflow recipient details and workflow status details for each workflow task.

◉ Create a custom CDS view with a name. In this use case we have used YY1_PR_WORKFLOW_REP.

Primary Data Source is : I_WORKFLOWSTATUSDETAILS

Secondary Data Source is : I_WORKFLOWRECIEPIENTS

Joining primary and secondary CDS views using the field WORKFLOWTASKINTERNALID.

Join condition is WORKFLOWTASKINTERNALID.

◉ Add input date parameters inorder to filter the data based on a workflow creation date. We have given it as “From_Date” and “To_Date” (In the element properties we can change the label for these input fields).

Note:

As per your requirement you can filter the data based on any fields as well eg: purchase requisition creation date, workflow completion date etc.

◉ Under the “Elements” tab using the ADD button we can add the required fields to be displayed. Add a calculated field to convert the parameter date values to the UTC timestamp field since workflow creation date field is UTC.

◉ Calculated fieldsa) FromDate(Converting the From date input parameter into this calculated field):

Use the below code to convert the input parameter field “From_date” to the UTC field

“dats_tims_to_tstmp( $parameters.From_date,’000001′,abap_system_timezone( $session.client,’NULL’ ),$session.client,’NULL’ )”

b) ToDate

Use the below code to convert the input parameter field “ To_date” to the UTC field

dats_tims_to_tstmp( $parameters.To_date,’235959′,abap_system_timezone( $session.client,’NULL’ ),$session.client,’NULL’ )

Under the element properties tab please mark the aggregation properties fields as below:

Now check and publish the newly created custom CDS view.

CDS 2: YY1_PR_WORKFLOW_STATUS

This CDS is used to filter the workflow instances based on the input parameter(dates) you have entered, and also to join with the instances Purchase requisition numbers.

◉ Primary Data source: YY1_PR_WORKFLOW_REP ( previously created)

Secondary Data Source: I_WORKFLOWSTATUSOVERVIEW

And these 2 are joined with the condition WorkflowInternalId

By default, this new CDS will have parameters which is inherited from the primary data source CDS view

◉ Under the elements tab add the required fields and make sure you have added the fields:

SONT partial key 1 which has the value of Purchase requisition number and SONT partial key 2 which represents Purchase requisition item number. With this we have the details of workflow instances and the respective purchase requisition numbers.

There is nothing that needs to be done under the “Element Properties” since by default it will be inherited from the source CDS properties.

Moving to the filter condition here we are adding a filter condition. We are filtering the WRKFLWTSKCREATIONUTCDATETIME field with the user input “From” and “To” date.

Below condition is used:

WRKFLWTSKCREATIONUTCDATETIME <= From Date(UTC converted date)

AND

WRKFLWTSKCREATIONUTCDATETIME >= To Date(UTC converted date).

Once this step is done please Publish the same.

CDS 3 : YY1_PR_WORKFLOW_HEADEREP

So far, we have the details of workflow instances and its purchase requisition, item number now In this new CDS views we will be joining with purchase requisition master data and its accounting lines CDS views in order to have more details of purchase requisition data.

◉ Primary Data Source: YY1_PR_WORKFLOW_STATUS

Secondary Data Sources : I_PURCHASEREQUISITIONAPI01 and I_PURREQNACCTASSGMTAPI01

And the Join conditions will be on Purchase requisition number of secondary data sources (I_PURCHASEREQUISITIONAPI01 &  I_PURREQNACCTASSGMTAPI01) with SONT PARTIAL Key one field on primary data source(YY1_PR_WORKFLOW_STATUS) CDS.

I_PURCHASEREQUISITIONAPI01:

I_PURREQNACCTASSGMTAPI01:

Under the parameters section nothing needs to be done since it will be inherited from the primary data source

◉ Under the elements tab add the required fields from the CDS as per your requirement. Here we will be adding accounting details like Cost Center, GLAccount, Network from the I_PURREQNACCTASSGMTAPI01 CDS and Purchase requisition, deletion flag, material group from the  I_PURCHASEREQUISITIONAPI01.

Now you can publish this CDS also you have the option to add any further filters based on your requirement.

Create Custom Analytical Query

Creation of Analytical Query: YY1_PR_WORKFLOW

Now we will be creating Analytical query using the app: Custom Analytical Query. This analytical query is used to generate analytical report with different dimensions and measures using the analytical CDS we have created.

Here we are creating a new custom analytical query: YY1_PR_WORKFLOW. Using the custom CDS YY1_PR_WORKFLOW_HEADEREP as the data source

Under the field selection we have the option to choose the fields required to display in the report.

Now under the display tab we have the option to specify which field need to be as Row and Column

Here we are adding a counter field and naming it as “Total no. of Purchase requisitions” which will display the total number of purchase requisitions .. Keep the dimension as “Purchase Requisition” for this field.

Note:

As per your requirement you can give any field as counter field eg: WorkflowInternalTaskID(this will give total number of workflows, Document type(this will give result based on document type)

We have the option to set filter as well, This query will show only for the workflow instances which are in “Ready State”. If this is not given then report will be based on all the workflows instances irrespective of its status. As of now I need only purchase requisitions which are in “In Approval” state

Now we can publish the query so that it is ready to consume in the application.

Create Custom Application

Using the View Browser app, we can create application using this analytical query and can add it to any business catalog

Once you create the application an Application Id is generated:

Assign Custom Application to Catalog

Now in the Custom Catalog Extension app search with the created Application id: YY1_1601031094179_AQUERY or with name of the analytical query: YY1_PR_WORKFLOW. Please refer the below screen shot:

Once you select the application then navigate inside and click on the “Add” button in order to select the catalog id

Once you select the Catalog id publish it . This will take some time to reflect in the UI.

Now the newly created application “YY1_PR_WORKFLOW” will be displayed under the Analytics-KPI Design catalog as a separate tile and we can execute this report with any date range.

In the report we have the options to drag and drop the required dimension into columns and rows accordingly report data will get auto adjusted.

With this workflow analytical report is created which shows purchase requisitions which are in ready state for approval. As you can see in the above screen shot user Manager Procurement is having 2 Purchase requisitions with the cost center 10101101 and 3 are there without any cost center assigned. You can filter the report based on any dimension listed, as of now I am using only “Last Name” and “Cost Center” to filter the data, you can alter the report based on any fields just drag and drop the fields listed from “Dimension” column to “ Rows” Column.

Note:

In Addition, to generate a report which shows the average time taken by each approver. We can achieve this by adding a parameter which calculates difference between fields workflow task completion (WRKFLWTSKCOMPLETIONUTCDATETIME) and creation date (WRKFLWTSKCREATIONUTCDATETIME).

Use the below formula to achieve this:

“division( TSTMP_SECONDS_BETWEEN(I_WorkflowStatusDetails.WrkflwTskCreationUTCDateTime,I_WorkflowStatusDetails.WrkflwTskCompletionUTCDateTime,’INITIAL’),  86400, 6 )”