Home » Developer & Programmer » Reports & Discoverer » bal qty calculation throgh formula (report 6i)
bal qty calculation throgh formula Thu, 03 July 2008 07:04
 mk_sahu Messages: 6Registered: March 2007 Junior Member
I have two queries in my report in separate group, one is for issue and second for recieving:-

1)query
select a.challan_no,a.CHALLAN_DT,a.ITEM_DESC,sum(a.ISS_QTY_NOS),sum(a.ISS_QTY_wt),
b.vend_code,c.vend_name
from jw_issue_det a,jw_issue_mas b,vendor_mas c
where
a.challan_no=b.challan_no and
a.challan_dt=b.challan_dt and
b.vend_code=c.vend_code and a.challan_dt>= : From_Dt and a.challan_dt <= :To_Dt and b.vend_code = :Vendor_Name
group by b.vend_code,c.vend_name,a.challan_no,a.CHALLAN_DT,a.ITEM_DESC
2)query
elect challan_no,CHALLAN_DT,ITEM_DESC,sum(recd_QTY_NOS),sum(recd_QTY_wt)
from jw_recd_det
group by challan_no,CHALLAN_DT,ITEM_DESC

Now I want to know can I calculate balance qty throgh formula coulmn. pl provide code to me for it, right now I am using the following code in formula, but it is not working

function CF_1Formula return Number is
bal_nos number;
begin
select (:sum_a_iss_qty_nos - :sum_recd_qty_nos) into bal_nos from dual;
return(bal_nos);
end;

Thanks!!
Re: bal qty calculation throgh formula [message #331697 is a reply to message #331456] Fri, 04 July 2008 12:17
 dude4084 Messages: 222Registered: March 2005 Location: Mux Senior Member
Hi

Can you show me image of your current report ?

Can you make image of report which you require?

why two seprates groups?

-Dude

Re: bal qty calculation throgh formula [message #331714 is a reply to message #331456] Fri, 04 July 2008 22:36
 mm_kanish05 Messages: 488Registered: January 2007 Location: Chennai Senior Member
Hi

As dude said u can join the group and u can achieve what u are expection.

Better u show your datamodel image.
Run report image.

Kanish
Re: bal qty calculation throgh formula [message #331728 is a reply to message #331697] Sat, 05 July 2008 01:16
 mk_sahu Messages: 6Registered: March 2007 Junior Member
Dear sir,

I am attaching the screen shots of the report, I want to calculate balance qty=Issue_qty-recd-qty,
e.g
Issue qty=100,recd qty=50
then bal_qty=100-50=50
if there is no recieving then
bal_qty=100-0=100

Thanks for the the help
Re: bal qty calculation throgh formula [message #331753 is a reply to message #331456] Sat, 05 July 2008 04:05
 cvs_1984 Messages: 136Registered: August 2007 Location: Punjab, INDIA Senior Member
You should try it by write the query using Union like Below
```select a.challan_no,a.CHALLAN_DT,a.ITEM_DESC,a.recd_QTY_NOS,a.recd_QTY_wt,a.ISS_QTY_NOS,a.ISS_QTY_wt,
b.vend_code,c.vend_name

(
(
select a.challan_no,a.CHALLAN_DT,a.ITEM_DESC,(0) "recd_QTY_NOS",(0) "recd_QTY_wt",
sum(a.ISS_QTY_NOS),sum(a.ISS_QTY_wt),
b.vend_code,c.vend_name
from jw_issue_det a,jw_issue_mas b,vendor_mas c
where
a.challan_no=b.challan_no and
a.challan_dt=b.challan_dt and
b.vend_code=c.vend_code and a.challan_dt>= : From_Dt and a.challan_dt <= :To_Dt and b.vend_code = :Vendor_Name
group by b.vend_code,c.vend_name,a.challan_no,a.CHALLAN_DT,a.ITEM_DESC
)
Union
(
Select challan_no,CHALLAN_DT,ITEM_DESC,sum(recd_QTY_NOS),sum(recd_QTY_wt),(0) "ISS_QTY_NOS",(0) "ISS_QTY_wt"
from jw_recd_det
group by challan_no,CHALLAN_DT,ITEM_DESC
)
)
a```

Then Using the Function by

```function CF_CbnoFormula return Number is
Cf_cbno Number(15,3);

Begin

Cf_cbNo := :recd_QTY_NOS - ISS_QTY_NOS;

Return(Cf_cbno);

End;```
Re: bal qty calculation throgh formula [message #333341 is a reply to message #331456] Fri, 11 July 2008 05:35
 mk_sahu Messages: 6Registered: March 2007 Junior Member
It shows syantax error and union works for the same field of the different queries.
 Previous Topic: How to mail the content of report? Next Topic: assign value run time in reports
Goto Forum:

Current Time: Wed Aug 23 07:02:52 CDT 2017

Total time taken to generate the page: 0.15314 seconds