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;
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.
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.
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.
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.
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;
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.
The join appears under the fields.
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 …