1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to get opening balance from a report

Discussion in 'Oracle Forms and Reports' started by ksjj, Dec 17, 2014.

  1. ksjj

    ksjj Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    I have a table with field Inward_date, clearance_date for all the letters received. In Inward_typ field, "P_UN" means that the letter is uncleared. Using this i can get the number of letters which are still pending. I need to create a report to check on the pendency of letters received at the end of every month.

    I did create a report adding a CF column for OB, ADDITIONS, CLEARANCE AND FOR CB.

    Created a parameter with from_date and to_date to get the additions for the month. From the clearance_date field i could get the clearance for the month. Created a formula OB+ADDITIONS-CLEARANCE to get the CB.

    My only problem is to get the OB. For the initial value took it as count (*) where Inward_typ < '01-jan-2014'. I am not able to get the CB as OB for the subsequent months


    I have written the CF used for OB, ADD, CLS AND CB. Please guide me how to get the CB of the previous month as OB for the next month.

    Thanks in advance

    Code (Text):
     
    For OB

    SELECT COUNT(*) FROM T_INWARD_SM
    WHERE INWARD_TYP='P_UN'
    AND INWARD_DT< :FROM_DT

    FOR ADDITIONS

    SELECT COUNT(*) FROM T_INWARD_SM
    WHERE INWARD_DT BETWEEN :FROM_DT AND :TO_DT

    FOR CLEARANCE

    SELECT COUNT(*) FROM T_INWARD_SM
    WHERE CLS_DT BETWEEN :FROMCLS_DT AND :TOCLS_DT

    FOR CB

    CB:=:CF_OB=:CF_ADD-:CF_CLS

    Code (Text):