Home » Developer & Programmer » Reports & Discoverer » running total problem
running total problem [message #171203] |
Tue, 09 May 2006 01:08 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
samit_gandhi
Messages: 226 Registered: July 2005 Location: Hong Kong
|
Senior Member |
![samit_gandhi](/forum/theme/orafaq/images/yahoo.png)
|
|
Dear All,
I want the report like this :
Group Name : A
Ref. No. In Qty. Out Qty. Bal. Qty
1 2.00 0.00 2.00
2 2.00 0.00 4.00
3 0.00 1.00 3.00
Group Name : B
Ref. No. In Qty. Out Qty. Bal. Qty
4 2.00 0.00 2.00
5 2.00 0.00 4.00
6 0.00 1.00 3.00
I putted the formula column in the balance quantity but i am getting the output like this :
Group Name : A
Ref. No. In Qty. Out Qty. Bal. Qty
1 2.00 0.00 2.00
2 2.00 0.00 4.00
3 0.00 1.00 3.00
Group Name : B
Ref. No. In Qty. Out Qty. Bal. Qty
4 2.00 0.00 5.00
5 2.00 0.00 7.00
6 0.00 1.00 6.00
what to do to sort out the problem.
pls help it is urgent.
samit
[mod-edit]applied tags [CODE]and [/CODE]. Next time, do it yourself so we can understand the desired output
[Updated on: Tue, 09 May 2006 01:50] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: running total problem [message #171352 is a reply to message #171338] |
Tue, 09 May 2006 08:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43055.jpg) |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Why don't you just calculate it in your query.
I've used this script:
CREATE TABLE mhe_foo(grpno VARCHAR2(1)
,refno NUMBER
,inqty NUMBER
,outqty NUMBER
)
/
INSERT INTO mhe_foo VALUES('A', 1, 2.00, 0.00)-- 2.00
/
INSERT INTO mhe_foo VALUES('A', 2, 2.00, 0.00)-- 4.00
/
INSERT INTO mhe_foo VALUES('A', 3, 0.00, 1.00)-- 3.00
/
INSERT INTO mhe_foo VALUES('B', 4, 2.00, 0.00)-- 2.00
/
INSERT INTO mhe_foo VALUES('B', 5, 2.00, 0.00)-- 4.00
/
INSERT INTO mhe_foo VALUES('B', 6, 0.00, 1.00)-- 3.00
/
COMMIT
/
In the report I used this query:
SELECT grpno
, refno
, inqty
, outqty
, SUM(inqty - outqty) OVER ( PARTITION BY grpno ORDER BY grpno, refno) balqty
FROM mhe_foo
ORDER BY grpno, refno
I dragged the grpno out of the query (upwards) so I had a group.
Now you can easily create the report you desired.
MHE
|
|
|
Re: running total problem [message #171440 is a reply to message #171203] |
Wed, 10 May 2006 01:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
samit_gandhi
Messages: 226 Registered: July 2005 Location: Hong Kong
|
Senior Member |
![samit_gandhi](/forum/theme/orafaq/images/yahoo.png)
|
|
Sorry but this is not solving my problem as i have the query like this :
SELECT (' ') SR_NO,
to_date('01-01-06','dd-mm-yy') VOUCHER_DATE ,('USD') CURRENCY, (SELECT EXCHANGE_RATE
FROM CURRENCY_MASTER WHERE CURRENCY_CODE='USD') EX_RATE,
INITCAP(('OPENING BALANCE')) AC_NAME ,
round((REF_MASTER.OPENING_RATE_USD*CURRENCY_MASTER.EXCHANGE_RATE),2) net_usd,0,
REF_MASTER.OPENING_STOCK_QTY, 0, REF_MASTER.DESCRIPTION,
' ', REF_MASTER.REF_NO,
'OPENING BALANCE' FINAL_DESC
FROM CURRENCY_MASTER, REF_MASTER
WHERE CURRENCY_MASTER.CURRENCY_CODE=:FCURRENCY AND REF_MASTER.COMPANY_ID=:FCOMP_ID &P_QUERY
GROUP BY REF_MASTER.REF_NO, REF_MASTER.OPENING_STOCK_QTY, REF_MASTER.DESCRIPTION,
REF_MASTER.OPENING_RATE_USD, CURRENCY_MASTER.EXCHANGE_RATE
UNION ALL
SELECT ALL TO_CHAR(PURCHASE_MASTER.SR_NO) SR_NO,
PURCHASE_MASTER.VOUCHER_DATE,PURCHASE_MASTER.CURRENCY, PURCHASE_MASTER.EX_RATE,
INITCAP(AC_MASTER.AC_NAME) AC_NAME,
round((PURCHASE_DETAIL.RATE_USD*CURRENCY_MASTER.EXCHANGE_RATE/PURCHASE_MASTER.EX_RATE),2) net_usd,0,
PURCHASE_DETAIL.QUANTITY, 0, REF_MASTER.DESCRIPTION, 'PU', PURCHASE_DETAIL.STOCK,
(AC_MASTER.AC_NAME||' / '||PURCHASE_MASTER.INVOICE_NO||' / '||REF_MASTER.DESCRIPTION) FINAL_DESC
FROM PURCHASE_MASTER, AC_MASTER, ANNEXURE_MASTER, PURCHASE_DETAIL, CURRENCY_MASTER, REF_MASTER
WHERE REF_MASTER.REF_NO=PURCHASE_DETAIL.STOCK AND REF_MASTER.REF_GROUP=PURCHASE_DETAIL.STOCK_GROUP AND REF_MASTER.COMPANY_ID=PURCHASE_MASTER.COMPANY_ID
AND AC_MASTER.AC_CODE=PURCHASE_MASTER.SUPPLIER_ID AND ANNEXURE_MASTER.CODE=PURCHASE_MASTER.ANNEXURE_CODE AND ANNEXURE_MASTER.CODE=AC_MASTER.ANNEXURE_CODE AND AC_MASTER.COMPANY_ID=PURCHASE_MASTER.COMPANY_ID
AND PURCHASE_MASTER.SR_NO=PURCHASE_DETAIL.SR_NO AND PURCHASE_MASTER.COMPANY_ID=PURCHASE_DETAIL.COMPANY_ID
AND VOUCHER_DATE BETWEEN :FSTART_DATE AND :FEND_DATE AND CURRENCY_MASTER.CURRENCY_CODE=:FCURRENCY AND PURCHASE_MASTER.COMPANY_ID=:FCOMP_ID
&P_QUERY
GROUP BY PURCHASE_DETAIL.STOCK, PURCHASE_MASTER.SR_NO, PURCHASE_MASTER.VOUCHER_DATE, PURCHASE_MASTER.CURRENCY, PURCHASE_MASTER.EX_RATE,
PURCHASE_MASTER.SUPPLIER_ID, AC_MASTER.AC_NAME, PURCHASE_DETAIL.RATE_USD, CURRENCY_MASTER.EXCHANGE_RATE,PURCHASE_MASTER.INVOICE_NO,
REF_MASTER.DESCRIPTION, PURCHASE_DETAIL.QUANTITY
UNION ALL
SELECT ALL TO_CHAR(SALES_MASTER.SR_NO) SR_NO,
SALES_MASTER.VOUCHER_DATE,SALES_MASTER.CURRENCY, SALES_MASTER.EX_RATE,
INITCAP(AC_MASTER.AC_NAME) AC_NAME,
0,round((SALES_DETAIL.RATE_USD*CURRENCY_MASTER.EXCHANGE_RATE/SALES_MASTER.EX_RATE),2) net_usd,
0,SALES_DETAIL.QUANTITY, REF_MASTER.DESCRIPTION, 'SL' , SALES_DETAIL.STOCK,
(AC_MASTER.AC_NAME||' / '||SALES_MASTER.INVOICE_NO||' / '||REF_MASTER.DESCRIPTION) FINAL_DESC
FROM SALES_MASTER, SALES_DETAIL, SALESMAN_MASTER,STOCK_TYPE, AC_MASTER,
ANNEXURE_MASTER, CURRENCY_MASTER, REF_MASTER
WHERE REF_MASTER.REF_NO=SALES_DETAIL.STOCK AND REF_MASTER.REF_GROUP=SALES_DETAIL.STOCK_GROUP
AND SALES_MASTER.COMPANY_ID=REF_MASTER.COMPANY_ID AND
SALES_MASTER.SR_NO=SALES_DETAIL.SR_NO AND SALES_MASTER.COMPANY_ID=SALES_DETAIL.COMPANY_ID
AND AC_MASTER.AC_CODE=SALES_MASTER.CUSTOMER_ID AND ANNEXURE_MASTER.CODE=SALES_MASTER.ANNEXURE_CODE AND AC_MASTER.COMPANY_ID=SALES_MASTER.COMPANY_ID
AND ANNEXURE_MASTER.CODE=AC_MASTER.ANNEXURE_CODE
AND SALES_MASTER.VOUCHER_DATE BETWEEN :FSTART_DATE AND :FEND_DATE AND CURRENCY_MASTER.CURRENCY_CODE=:FCURRENCY AND SALES_MASTER.COMPANY_ID=:FCOMP_ID
&P_QUERY
GROUP BY SALES_DETAIL.STOCK, SALES_MASTER.SR_NO, SALES_MASTER.VOUCHER_DATE, SALES_MASTER.CURRENCY, SALES_MASTER.EX_RATE, AC_MASTER.AC_NAME,
SALES_MASTER.INVOICE_NO, REF_MASTER.DESCRIPTION, SALES_DETAIL.RATE_USD, CURRENCY_MASTER.EXCHANGE_RATE, SALES_DETAIL.QUANTITY
samit
|
|
|
|
Re: running total problem [message #171450 is a reply to message #171441] |
Wed, 10 May 2006 01:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43055.jpg) |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Your query (formatted):
SELECT (' ') sr_no,
To_date('01-01-06',
'dd-mm-yy') voucher_date,
('USD') currency,
(SELECT exchange_rate
FROM currency_master
WHERE currency_code = 'USD') ex_rate,
Initcap(('OPENING BALANCE')) ac_name,
Round((ref_master.opening_rate_usd * currency_master.exchange_rate),
2) net_usd,
0,
ref_master.opening_stock_qty,
0,
ref_master.description,
' ',
ref_master.ref_no,
'OPENING BALANCE' final_desc
FROM currency_master,
ref_master
WHERE currency_master.currency_code = :fcurrency
AND ref_master.company_id = :fcomp_id & p_query
GROUP BY ref_master.ref_no,
ref_master.opening_stock_qty,
ref_master.description,
ref_master.opening_rate_usd,
currency_master.exchange_rate
UNION ALL
SELECT ALL To_char(purchase_master.sr_no) sr_no,
purchase_master.voucher_date,
purchase_master.currency,
purchase_master.ex_rate,
Initcap(ac_master.ac_name) ac_name,
Round((purchase_detail.rate_usd * currency_master.exchange_rate / purchase_master.ex_rate),
2) net_usd,
0,
purchase_detail.quantity,
0,
ref_master.description,
'PU',
purchase_detail.stock,
(ac_master.ac_name || ' / ' || purchase_master.invoice_no || ' / ' || ref_master.description) final_desc
FROM purchase_master,
ac_master,
annexure_master,
purchase_detail,
currency_master,
ref_master
WHERE ref_master.ref_no = purchase_detail.stock
AND ref_master.ref_group = purchase_detail.stock_group
AND ref_master.company_id = purchase_master.company_id
AND ac_master.ac_code = purchase_master.supplier_id
AND annexure_master.code = purchase_master.annexure_code
AND annexure_master.code = ac_master.annexure_code
AND ac_master.company_id = purchase_master.company_id
AND purchase_master.sr_no = purchase_detail.sr_no
AND purchase_master.company_id = purchase_detail.company_id
AND voucher_date BETWEEN :fstart_date
AND :fend_date
AND currency_master.currency_code = :fcurrency
AND purchase_master.company_id = :fcomp_id & p_query
GROUP BY purchase_detail.stock,
purchase_master.sr_no,
purchase_master.voucher_date,
purchase_master.currency,
purchase_master.ex_rate,
purchase_master.supplier_id,
ac_master.ac_name,
purchase_detail.rate_usd,
currency_master.exchange_rate,
purchase_master.invoice_no,
ref_master.description,
purchase_detail.quantity
UNION ALL
SELECT ALL To_char(sales_master.sr_no) sr_no,
sales_master.voucher_date,
sales_master.currency,
sales_master.ex_rate,
Initcap(ac_master.ac_name) ac_name,
0,
Round((sales_detail.rate_usd * currency_master.exchange_rate / sales_master.ex_rate),
2) net_usd,
0,
sales_detail.quantity,
ref_master.description,
'SL',
sales_detail.stock,
(ac_master.ac_name || ' / ' || sales_master.invoice_no || ' / ' || ref_master.description) final_desc
FROM sales_master,
sales_detail,
salesman_master,
stock_type,
ac_master,
annexure_master,
currency_master,
ref_master
WHERE ref_master.ref_no = sales_detail.stock
AND ref_master.ref_group = sales_detail.stock_group
AND sales_master.company_id = ref_master.company_id
AND sales_master.sr_no = sales_detail.sr_no
AND sales_master.company_id = sales_detail.company_id
AND ac_master.ac_code = sales_master.customer_id
AND annexure_master.code = sales_master.annexure_code
AND ac_master.company_id = sales_master.company_id
AND annexure_master.code = ac_master.annexure_code
AND sales_master.voucher_date BETWEEN :fstart_date
AND :fend_date
AND currency_master.currency_code = :fcurrency
AND sales_master.company_id = :fcomp_id & p_query
GROUP BY sales_detail.stock,
sales_master.sr_no,
sales_master.voucher_date,
sales_master.currency,
sales_master.ex_rate,
ac_master.ac_name,
sales_master.invoice_no,
ref_master.description,
sales_detail.rate_usd,
currency_master.exchange_rate,
sales_detail.quantity
Next time format yourself. It doesn't hurt to use code tags. In the Newbie board there's a sticky concerning formatting. You might want to check it out. I'll see whether I can come up with something. It shouldn't be that hard.
MHE
[Updated on: Wed, 10 May 2006 01:42] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: running total problem [message #171505 is a reply to message #171203] |
Wed, 10 May 2006 05:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gladiator
Messages: 21 Registered: April 2006 Location: Lahore, Pakistan
|
Junior Member |
|
|
Hi samit,
i have developed a report on ur given dummy data, and it is working fine. and it uses simple formula columns to hold the value/name of the group and to reset the value of placeholder column to zero when group name is changed. i m attaching the report, that is build in 9i.ask me any thing ab this report/logic if u have any confusion to understand it. do tell me as soon as possible that do u solved ur problem?
Regards,
Usman.
|
|
|
Re: running total problem [message #171507 is a reply to message #171497] |
Wed, 10 May 2006 05:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43055.jpg) |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Fine, let me walk you through what I did:
- Open reports builder
- Connect to the HR schema (or SCOTT, but then you have to use the EMP table instead of EMPLOYEES)
- Create a new report, select "Build a new report manually"
- In the Data Model create a new query:
SELECT last_name
, first_name
, department_id
, salary
FROM employees Note: for EMP use a similar query (select ename,deptno, sal from emp)
- Drag department_id/deptno above to create a group
- Add a formula field to the lowest group (CF_SALARY). Make sure it is numeric and add some function in the PL/SQL formula. I used the following:
function CF_SALARYFormula return Number is
begin
RETURN :SALARY-120;
end;
- Add a summary field to the lowest group (CS_SALARY). The source is CF_SALARY, the function is SUM and the field should reset at the highest group.
- Start the report wizard
- Leave the report type you want (I selected "Create paper layout only")->next
- Select "Group above" and give the report a title->
- Leave the data source to "SQL Query"->next
- Leave the Data SQL text->next
- Leave the Groups (verify though that department_id/deptno is the group field)->next
- Add all fields except the formula column CF_SALARY to the "Displayed fields"->next
- Leave the totals blank->next
- Change the labels you want->next
- Select the template you want->Finish
You should be able to create something similar.
MHE
|
|
|
|
Re: running total problem [message #171523 is a reply to message #171203] |
Wed, 10 May 2006 05:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gladiator
Messages: 21 Registered: April 2006 Location: Lahore, Pakistan
|
Junior Member |
|
|
Hi,
just check out the screen shots of my previously attached report. i think u want this kinda results. just chk it out.
Regards,
Usman.
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 10:54:10 CDT 2024
|