Skip to Content
author's profile photo Former Member
Former Member

Calculate Opening Balance for GL for Reporting

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

Capture.PNG (4.1 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Apr 26, 2016 at 06:07 AM

    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


    temp.PNG (35.0 kB)
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.