Schedule a BI Publisher Report Based on Custom Fast Formula

Introduction

Many a times we may have a requirement that we would like to execute a BI Publisher Report on a custom schedule which does not falls into any of the daily, monthly, annual or any other delivered schedules available under the frequency option of a BI Publisher Report.

One most common requirement is “Running a Report on Last Day of Every Month”. Since the last day of every month will vary with possible values being 28 , 30th and 31st and don’t forget 29th for a leap year.

In such scenarios one would have to manually choose the dates and submit the ESS job using the specific dates.

Imagine you need to do the same for 100 BI Reports. Then this definitely is going to take a lot of time.

You can get through this problem by creating a Custom Payroll Flow Pattern to submit your report. This custom payroll flow pattern can make use of “Custom Flow Schedule” Fast Formula to automate the schedule run time of report.

In this example, we will demonstrate the same. 

Pre-requisite

There are two major pre-requisites to accomplish this task namely:

1. A Ready to use BI-Publisher Report
2. A Custom Payroll Flow Pattern based on the BI-Publisher Report used above

Ready to use BI-Publisher Report

For this example, ,we will use a BI Publisher Report which has following details:

Report Name: EmployeeAbsencePlanBalanceReport

Report Path: /Custom/Practice Samples/EmployeeAbsencePlanBalanceReport.xdo

We should run te report once to ensure it is working fine

Custom Payroll Flow Pattern based on BI-Report

We should also make sure that there is a pre-existing flow pattern based on the BI-Report. 

If you are not aware how to create a Payroll flow pattern for a BI Report please refer this link.

We have “Run Employee Absence Plan Balance Report”

Navigation: My Client Groups->Payroll->Administration->Payroll Flow Patterns(Search for Run Employee Absence Plan Balance Report)

We will quickly run this payroll flow pattern once to check its working fine.

Navigation: My Client Groups->Payroll->Flow Submission and Results->Submit a Flow-> Search for Run Employee Absence Plan Balance Report

Once we submit, we should be able to see that the Report output is generated.

Now all the pre-requisite steps are complete.

We will now create a Custom Fast Formula of “Flow Schedule” Type

Creating Custom Fast Formula 

In this step we will create a custom Fast Formula of “Flow Schedule” type.

Running the Custom Payroll Flow Pattern using Flow Schedule Formula

We would now run the Custom payroll flow pattern using the Flow Schedule Formula we just created.

Details are:

Flow Pattern Name: Run Employee Absence Plan Balance Report

Flow Instance Name: B001

Absence Plan Name: <BLANK>

Person Number: <BLANK>

Threshold Date: 5/28/2020

Schedule: Using a Schedule

Frequency: LAST_DAY_OF_MONTH_FLOW_SCHEDULE

Start Date: 5/28/20 6:21 AM

End Date: 7/15/20 6:21 AM

We can verify the result

Verification

We can verify the details of all job schedule which was created based on Payroll Flow: B001

From the above screenshot that there are two occurrences of Payroll Flow one of which has been schedule to run on current date and its status is Succeeded while the other which is scheduled on 2020-06-30 is in wait state.

One thing to note is that while submitting the flow schedule we chose start date as 5/28/2020 and end date as 7/15/2020 so the last schedule will be 30st June 2020.

Summary

So, this is an example of using a custom flow schedule fast formula to run a BI Report. Hope this was a good read and it was helpful.

Thanks all for your time and have a nice day.

You can download the files from the link below:

ScheduleProcessVerification.sql

LAST_DAY_MONTH_FLOW_SCHEDULE

Ashish Harbhajanka

Add comment