Calculate Opening Balance for GL for Reporting

[Former Member]

Hi,

I have the requirement to report opening balances against GL. The report would like somewhat as shown below:

a) The user will execute the report for a certain month .eg: Feb-2016 and the columns "Opening Balance", "Adjustment" and "Closing Balance" will be populated all the way till Sep-2012.

b) The base data is coming from a view that combines BSEG+BKPF tables in SAP HANA.

c) Closing Balance for a given month = Opening Balance + Adjustment.

d) Adjustment and Closing Balance are two columns in an underlying Calculation View that is used as the base for reporting.

e) Formula of opening balance for a given month = Closing balance for the previous month.

With the information provided above, could you please suggest a few ways how the column "Opening Balance" could be designed in SAP HANA? Please let me know if any additional information is required.

Thank You,

Anjana

[muthuram_shanmugavel2]

Hi Anjana,

This can be achievable b y Scripted Calculation Views using Windows Function in SQL scripts.

Assume Rs. 50,000  is Opening Balance for the Month Sep-12.

Adjustments for the Month Sep-12 is Rs.100,

Adjustments for the Month Oct-12 is Rs.110,

It is going on like this.

Table:

Create column Table GL_OP_BAL_APR26

(ID Integer,

Month Varchar(10),

Opening_Balance Integer,

Adjustments Integer)

Data:

Insert into GL_OP_BAL_APR26 values (1, 'Sep-12', 500000, 100);

Insert into GL_OP_BAL_APR26 values (2, 'Oct-12', 500000, 110);

Insert into GL_OP_BAL_APR26 values (3, 'Nov-12', 500000, 120);

Insert into GL_OP_BAL_APR26 values (4, 'Dec-12', 500000, 130);

Insert into GL_OP_BAL_APR26 values (5, 'Jan-13', 500000, 140);

Insert into GL_OP_BAL_APR26 values (6, 'Feb-13', 500000, 150);

Insert into GL_OP_BAL_APR26 values (7, 'Mar-13', 500000, 160);

Insert into GL_OP_BAL_APR26 values (8, 'Apr-13', 500000, 170);

Insert into GL_OP_BAL_APR26 values (9, 'May-13', 500000, 180);

Insert into GL_OP_BAL_APR26 values (10, 'Jun-13', 500000, 190);

Insert into GL_OP_BAL_APR26 values (11, 'Jul-13', 500000, 200);

Insert into GL_OP_BAL_APR26 values (12, 'Aug-13', 500000, 210);

Insert into GL_OP_BAL_APR26 values (13, 'Sep-13', 500000, 220);

Insert into GL_OP_BAL_APR26 values (14, 'Oct-13', 500000, 230);

Insert into GL_OP_BAL_APR26 values (15, 'Nov-13', 500000, 240);

Insert into GL_OP_BAL_APR26 values (16, 'Dec-13', 500000, 250);

Insert into GL_OP_BAL_APR26 values (17, 'Jan-14', 500000, 260);

Insert into GL_OP_BAL_APR26 values (18, 'Feb-14', 500000, 270);

Insert into GL_OP_BAL_APR26 values (19, 'Mar-14', 500000, 280);

Insert into GL_OP_BAL_APR26 values (20, 'Apr-14', 500000, 290);

Insert into GL_OP_BAL_APR26 values (21, 'May-14', 500000, 300);

Insert into GL_OP_BAL_APR26 values (22, 'Jun-14', 500000, 310);

Insert into GL_OP_BAL_APR26 values (23, 'Jul-14', 500000, 320);

Insert into GL_OP_BAL_APR26 values (24, 'Aug-14', 500000, 330);

Insert into GL_OP_BAL_APR26 values (25, 'Sep-14', 500000, 340);

Insert into GL_OP_BAL_APR26 values (26, 'Oct-14', 500000, 350);

Insert into GL_OP_BAL_APR26 values (27, 'Nov-14', 500000, 360);

Insert into GL_OP_BAL_APR26 values (28, 'Dec-14', 500000, 370);

Insert into GL_OP_BAL_APR26 values (29, 'Jan-15', 500000, 380);

Insert into GL_OP_BAL_APR26 values (30, 'Feb-15', 500000, 390);

Insert into GL_OP_BAL_APR26 values (31, 'Mar-15', 500000, 400);

Insert into GL_OP_BAL_APR26 values (32, 'Apr-15', 500000, 410);

Insert into GL_OP_BAL_APR26 values (33, 'May-15', 500000, 420);

Insert into GL_OP_BAL_APR26 values (34, 'Jun-15', 500000, 430);

Insert into GL_OP_BAL_APR26 values (35, 'Jul-15', 500000, 440);

Insert into GL_OP_BAL_APR26 values (36, 'Aug-15', 500000, 450);

Insert into GL_OP_BAL_APR26 values (37, 'Sep-15', 500000, 460);

Insert into GL_OP_BAL_APR26 values (38, 'Oct-15', 500000, 470);

Insert into GL_OP_BAL_APR26 values (39, 'Nov-15', 500000, 480);

Insert into GL_OP_BAL_APR26 values (40, 'Dec-15', 500000, 490);

Insert into GL_OP_BAL_APR26 values (41, 'Jan-16', 500000, 500);

Insert into GL_OP_BAL_APR26 values (42, 'Feb-16', 500000, 510);

Now use this SQL Logic with Windows Function:

Select

ID, Month, 

Opening_Balance + Adjustments - Sum(Adjustments)  OVER (ORDER BY ID) As Opening_Balance_By_Month,

Adjustments,

Opening_Balance - Sum(Adjustments) OVER (ORDER BY ID) As Closing_Balance

From GL_OP_BAL_APR26

It will give you the required output.

Hope It would resolve your Issue.

Regards,

Muthuram