Home » Developer & Programmer » Reports & Discoverer » How to get Opening balance in a report (oracle 10g , oracle devsuite forms and reports)
How to get Opening balance in a report [message #629881] |
Tue, 16 December 2014 21:47  |
|
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.
Thansk in advane
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
[EDITED by LF: fixed [code] tags]
[Updated on: Tue, 16 December 2014 23:53] by Moderator Report message to a moderator
|
|
|
|
Re: How to get Opening balance in a report [message #629904 is a reply to message #629886] |
Wed, 17 December 2014 03:56   |
|
HI,
Sorry for the confusion. OB Opening balance and CB is Closing Balance.
Is it enough if I can give you the values in the table and the required output of the data. If so the table script is
Create table Inward_M
(Inout_date date,
Inout_doc_typ varchar2 (20 byte),
Inout_secn_id varchar2 (20 byte),
Inout_charge_no varchar2 (5 byte),
Inout_stat varchar2 (20 byte),
Inout_cls_date date)
The values in this table is as follows
Inout_date Inout_doc_type Inout_secn_id Inout_charge_No Inout_stat Inout_cls_date
01-jan-2014 Letter GE1 A P_UN
10-jan-2014 Leave GE1 A Cleared 15-jan-2014
20-Jan-2014 Salary GE1 B P_Un
22-jan-2014 Salary GE1 A P_UN
25-jan-2014 Stationery GE1 C P_UN
01-Feb-2014 Stationery GE1 A Cleared 10-feb-2014
05-feb-2014 Letter GE1 C P_UN
10-Feb-2014 Salary GE1 B P_UN
The report will be as mentioned below for the month of January
Opening balance Additions Clearance Closing balance
223 05 01 227
The report will be as mentioned below for the month of February
Opening balance Additions Clearance Closing balance
227 03 01 229
Sorry i am not able to explain it any way better. Hope it explains the requirement
Thanks a lot
|
|
|
Re: How to get Opening balance in a report [message #629905 is a reply to message #629904] |
Wed, 17 December 2014 04:00   |
|
sorry i did not know it comes this way when i wrote it. could any one help me a better way of posting this. I tried to write everything in a table of word document and paste it . but it does not gets pasted in a table format.
Please dont mistake me. I am not very comfortable in this.
Sorry once again. See if the above post is able to be read
|
|
|
|
|
|
Re: How to get Opening balance in a report [message #629910 is a reply to message #629908] |
Wed, 17 December 2014 05:58   |
|
sir,
giving the table scripts and insert statement for better reading.
Create table Inward_M
(Inout_date date,
Inout_doc_typ varchar2 (20 byte),
Inout_secn_id varchar2 (20 byte),
Inout_charge_no varchar2 (5 byte),
Inout_stat varchar2 (20 byte),
Inout_cls_date date)
Insert into inward_m
(Inout_date date,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(01-jan-2014,Letter,GE1,A,P_UN,'');
(10-jan-2014,Leave,GE1,A,Cleared,15-jan-2014);
(20-Feb-2014,Salary,GE1,B,P_Un,'');
Just gave some 3 records as a test case
the report out put for january and february should give Opening balance say for testing take a fixed amount as 100
Opening balance 100
additions 02
clearence 01
Closing balance 101
and for february report the opening balance has to be 101
I hope I have explained it better than before.
Pl guide me to make it better and a solution to my report output
|
|
|
|
|
|
|
|
Re: How to get Opening balance in a report [message #630153 is a reply to message #630151] |
Sun, 21 December 2014 10:05   |
|
Hi michel
script of the table mentioned is actually from the existing table which i use.
I am not sure where i went wrong in giving information. sorry couldnt understand how to give a test case.
any how since it was mentioned that the insert statements should be given to be used without editing i have given it this way. please let me know where i am going wrong.
Now i think i need to know how to provide a test case also
Create table Inward_M
(Inout_date date,
Inout_doc_typ varchar2 (20 byte),
Inout_secn_id varchar2 (20 byte),
Inout_charge_no varchar2 (5 byte),
Inout_stat varchar2 (20 byte),
Inout_cls_date date)
Insert into inward_m
(Inout_date date,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(01-jan-2014,Letter,GE1,A,P_UN,'');
Insert into inward_m
(Inout_date date,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(11-jan-2014,salary,GE1,A,P_UN,'');
Insert into inward_m
(Inout_date date,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(21-jan-2014,Letter,GE1,C,cleared,'22-jan-2014');
Insert into inward_m
(Inout_date date,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(25-jan-2014,stationery,GE1,A,P_UN,'');
Insert into inward_m
(Inout_date date,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(01-feb-2014,Letter,GE1,A,P_UN,'');
Insert into inward_m
(Inout_date date,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(05-feb-2014,library,GE1,B,P_UN,'');
Insert into inward_m
(Inout_date date,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(11-feb-2014,Medical,GE1,B,cleared,'15-feb-2014');
Hope this is appears a correct way of giving a test case. Like to get it corrected
Thanks in advance
|
|
|
Re: How to get Opening balance in a report [message #630154 is a reply to message #630153] |
Sun, 21 December 2014 10:09   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> Insert into inward_m
2 (Inout_date date,
3 Inout_doc_typ,
4 Inout_secn_id ,
5 Inout_charge_no ,
6 Inout_stat ,
7 Inout_cls_date )
8 values
9 (01-jan-2014,Letter,GE1,A,P_UN,'');
(Inout_date date,
*
ERROR at line 2:
ORA-00917: missing comma
Please test your scrip before posting it.
|
|
|
Re: How to get Opening balance in a report [message #630163 is a reply to message #630154] |
Sun, 21 December 2014 21:48   |
|
HI,
sorry for the error
Create table Inward_M
(Inout_date date,
Inout_doc_typ varchar2 (20 byte),
Inout_secn_id varchar2 (20 byte),
Inout_charge_no varchar2 (5 byte),
Inout_stat varchar2 (20 byte),
Inout_cls_date date)
Insert into inward_m
(Inout_date ,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(01-jan-2014,Letter,GE1,A,P_UN,'');
Insert into inward_m
(Inout_date ,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(11-jan-2014,salary,GE1,A,P_UN,'');
Insert into inward_m
(Inout_date ,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(21-jan-2014,Letter,GE1,C,cleared,'22-jan-2014');
Insert into inward_m
(Inout_date ,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(25-jan-2014,stationery,GE1,A,P_UN,'');
Insert into inward_m
(Inout_date ,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(01-feb-2014,Letter,GE1,A,P_UN,'');
Insert into inward_m
(Inout_date ,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(05-feb-2014,library,GE1,B,P_UN,'');
Insert into inward_m
(Inout_date ,
Inout_doc_typ,
Inout_secn_id ,
Inout_charge_no ,
Inout_stat ,
Inout_cls_date )
values
(11-feb-2014,Medical,GE1,B,cleared,'15-feb-2014');
|
|
|
Re: How to get Opening balance in a report [message #630168 is a reply to message #630163] |
Mon, 22 December 2014 00:43  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> Insert into inward_m
2 (Inout_date ,
3 Inout_doc_typ,
4 Inout_secn_id ,
5 Inout_charge_no ,
6 Inout_stat ,
7 Inout_cls_date )
8 values
9 (01-jan-2014,Letter,GE1,A,P_UN,'');
(01-jan-2014,Letter,GE1,A,P_UN,'')
*
ERROR at line 9:
ORA-00984: column not allowed here
|
|
|
Goto Forum:
Current Time: Thu Feb 20 20:57:04 CST 2025
|