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 Go to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

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 #629886 is a reply to message #629881] Wed, 17 December 2014 00:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you provide test case (CREATE TABLE and several INSERT INTO statements), as well as the way desired output should look like based on sample data you'll provide? Moreover, I believe you (as well as some other people) know what abbreviations you use mean, but I have no idea what OB or CB is.
Re: How to get Opening balance in a report [message #629904 is a reply to message #629886] Wed, 17 December 2014 03:56 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

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 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

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 #629906 is a reply to message #629905] Wed, 17 December 2014 04:02 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Code tags would help - I know you know how to use them.
Re: How to get Opening balance in a report [message #629907 is a reply to message #629906] Wed, 17 December 2014 04:13 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

yes sir, code tags i could use it and i have used it before . But the data of the table and report out put to put it in table form for easy reading did not work and has not come out properly.
Re: How to get Opening balance in a report [message #629908 is a reply to message #629907] Wed, 17 December 2014 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Of course not. The reply windows only work with plain text. If you can't do it in notepad you can't do it here.
So tab seperate the columns in the output table and wrap the whole thing in code tags and it'll be nice and readable.

That said, if you want to give us the contents of a table so we can see for ourselves what's going on insert statements work a lot better (and they should be in code tags to, as well as the create table).
Re: How to get Opening balance in a report [message #629910 is a reply to message #629908] Wed, 17 December 2014 05:58 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

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 #629911 is a reply to message #629910] Wed, 17 December 2014 06:25 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) the end code tag [/code] needs to go after the text you want included in the tags - there's a preview button, use it.
2) If you're going to post insert statements, post ones that we can actually run without having to edit them.
Re: How to get Opening balance in a report [message #629915 is a reply to message #629911] Wed, 17 December 2014 07:55 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

Got it sir. Thanks a lot. Do i have to change the insert statement and send it or does it now explains my problem in the report
Re: How to get Opening balance in a report [message #629940 is a reply to message #629915] Thu, 18 December 2014 00:30 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

Any suggestion to solve the report problem which i had mentioned please
Re: How to get Opening balance in a report [message #630150 is a reply to message #629881] Sun, 21 December 2014 08:16 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

Hi,

Requesting for some help for solving my report problem please. Is there any more information required from my side. Please let me know.
Re: How to get Opening balance in a report [message #630151 is a reply to message #630150] Sun, 21 December 2014 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to provide a working and complete test case, if you want we help you as otherwise we cannot make research and try what we think before posting.

Re: How to get Opening balance in a report [message #630153 is a reply to message #630151] Sun, 21 December 2014 10:05 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

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 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: REP-3002: Error initializing printer
Next Topic: I need help to make a report in excel format
Goto Forum:
  


Current Time: Thu Mar 28 12:20:15 CDT 2024