Home    HRIS    Creating Payroll Variance Reports with Oracle Discoverer

Creating Payroll Variance Reports with Oracle Discoverer

By / February 10, 2010 / / 0 Comments


I have had many clients ask me whether they can create Pay Variation reports in Oracle Discoverer, and how much effort is involved. It is possible and it is quite easy once you know how.

To create Pay Variance reports in Oracle Discoverer you only need access to Discoverer Administrator, and either Discoverer Desktop or Discoverer Plus (this example will use Discoverer Desktop) and a basic knowledge of how the products work.

Pay Variance reports allow users to compare employees pay details over two different pay periods.  Not only will this allow easy reading for users but also allow the creation of calculations, conditional formatting and other Discoverer features that highlight the data users need to see.

The below image is what we will now create;

Var11 - Complete with formatting 2

Setting up the new Folder

To create a Pay Variance report in Oracle Discoverer, the first thing we need to do is setup a new folder in Discoverer Administrator.

Most clients wanting to create a Pay Variance report will already have an existing Pay folder in their End User Layer (EUL) so we will start from this point.  The below image shows the current EUL in Discoverer Administrator with the Pay folder expanded.  Our aim is to duplicate the Pay folder and join it to itself.
Var01 - Initial BA

Duplicate the Pay Folder

It is good practice to create a new business area when creating custom folders, so I have created a business area called “Human Resources – Custom”

Since we want to duplicate the Pay folder, we can simply copy it.  To do this right click on the original Pay folder and click “Copy”

Next, right click on the business area that you want to add the duplicate the folder into and choose “Paste”

Your new folder should now appear in the business area you chose.  Next, change the name of the folder to something a little more meaningful.  I tend to call these kind of folders, the name of the original folder (In this case “Pay”) with the words “Outer Join” after it.  This is because the next step is to create an outer join to the folder.

Var04 - New Folder Created

Joining the Folders

Now we will join the new folder to the original one.  Do this by clicking on the new folder and clicking;

Insert -> Join

Then choose the field you want to join your folders by, in this case Employee ID and click “OK” to go to the join wizard.

Var05 - New Join 1

Step 1 – Choose as many fields as you like to join the two folders together.  The fields to join by are employee identification fields like Employee ID and Job ID.  Don’t join by fields like Pay Period, as the point of this report to compare different pay periods, not join by them.  Once you choose the fields to join by click “Next”.

*Note – The folder on the left hand side of the screen is known as “Master” and the folder on the right hand side of the screen is known as “Detail”, this needs to be known for Step 2.

Var05 - New Join 2

Step 2 – When creating the join, it can be a good idea to create an outer join.

The difference between an outer join and a regular join is;

  • A regular join means that there must be a record in both tables to join by. If a record exists in one table but not the other, neither record will be displayed in the chart
  • An outer join means that when linking the folders together, a record must exist in the first folder, but does not need to exist in the second folder.  For example if an employee got paid in one period, but not in the second period, the employee will still exist in the Discoverer report, but the second folder’s fields will be left blank.

We will create an outer join in this example to show records from the “Pay” folder even if they don’t exist in the “Pay – Outer Join” folder.

Var05 - New Join 3

The join appears under the fields.

Var06 - Join Created

Once the new folder has been created and joined in Discoverer Administrator we can create the Pay Variance report using Discoverer Desktop.

Continue to page 2 to read this step …