While developing reports in Oracle General Ledger using FSG (Financial Statement Generator) you may have to develop a report in which you have to display Debit and Credit in a separate column. We can set Dr/Cr of two columns but if any account will have values in both Dr and Cr then both values will be displayed instead of net Dr or Cr. It is not as simple as it seems to be. We will use a trick to get the result.
For this demonstration I have used user operations/welcome of 11.5.10.2 vision instance. After login switch to General Ledger responsibility,
<b>Row Set</b>
1 - First of all we will create a Row Set using form,
GL: Reports > Define > Rowset
- FSG01.JPG (25.27 KiB) Viewed 110 times
Click on Defines Rows after providing the required information.
2 - Now we will define a Row Set,
- FSG01.JPG (25.27 KiB) Viewed 110 times
3 - Enter required information and then click on Account Assignments (B),
- FSG01.JPG (25.27 KiB) Viewed 110 times
4 - Enter relevant accounts, save and close the form.
<b>Column Set</b>
5 - Now we will define Column Set and will use the trick,
GL: Reports > Define > Column Set
- FSG01.JPG (25.27 KiB) Viewed 110 times
6 - Click on Define Columns after entering the required information. We will define three columns. The first will have the Net balance and we will hide this column as this column will have both Debit & Credit amount in a single column.
- FSG01.JPG (25.27 KiB) Viewed 110 times
7 - Our 2nd column will be the Debit column.
- FSG01.JPG (25.27 KiB) Viewed 110 times
8 - Click on Calculations (B) and here we will use the following formula,
=ABS (Column1 + Column1 / 2) -- ABS = Absolute
- FSG01.JPG (25.27 KiB) Viewed 110 times
9 - Create another column for Credit amount,
- FSG01.JPG (25.27 KiB) Viewed 110 times
10 - Enter the required information and click on Calculations (B) and enter the following formula,
=ABS (Column1 - Column1 / 2) -- Here we have used minus
- FSG01.JPG (25.27 KiB) Viewed 110 times
Save and close the form and come back to main form.
11 - Click on Build Column Set (B),
- FSG01.JPG (25.27 KiB) Viewed 110 times
scroll to the right and click on Create Default Heading.
12 - Update the column titles as shown on the following screen.
- FSG01.JPG (25.27 KiB) Viewed 110 times
Save and close all the forms.
13 - Now submit the report by defining a report or you can submit an Adhoc report and here is the result,
- FSG01.JPG (25.27 KiB) Viewed 110 times
I hope you will have enjoyed this demonstration. Thanks
