Home » Developer & Programmer » Reports & Discoverer » GP Percentage in Total (Windows Server 2003, Oracle 8i, Reports 5.0)
| GP Percentage in Total [message #538875] |
Tue, 10 January 2012 02:06  |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
[MERGED by LF]
Hi Experts,
Please see the attached pdf the output of my report. I want to calculate the GP at the Total column.
My GP formula is
trunc((profit) / decode(ACTUAL,0,1,ACTUAL)*100,2).
I tried using Placeholder Column & Formula Column but it is not working. I am using grouping in INV_DATE and summing all the columns. Can anyone help?
[Updated on: Wed, 11 January 2012 02:39] by Moderator Report message to a moderator |
|
|
|
| Re: GP Percentage in Total [message #538877 is a reply to message #538875] |
Tue, 10 January 2012 02:20  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You should reference real columns (what are "profit" and "ACTUAL" in a formula you posted?), and they are preceded with a colon sign. These *could* be, for example, trunc((:sum_profit_per_inv_date) / decode(:sum_actual_per_inv_date, 0, 1, :sum_actual_per_inv_date) * 100, 2)
If it still doesn't work, perhaps you could select both "profit" and "ACTUAL" values in a formula column, such as
l_profit number;
l_actual number;
begin
select sum(profit)
into l_profit
from your_table
where ...;
select sum (actual)
into l_actual
from ...
return (trunc((l_profit) / decode(l_actual, 0, 1, l_actual) * 100, 2));
end; |
|
|
|
|
|
| Re: GP Percentage in Total [message #539030 is a reply to message #538877] |
Tue, 10 January 2012 21:23 |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
Hi Littlefoot,
I tried using in formula column like this
function GP_PRFormula return Number is
l_profit number;
l_actual number;
gp_pr number;
begin
select sum(total)-(sum(DECODE(po_pur,'',0,PO_PUR))+sum(DECODE(pl_pur,'',0,PL_PUR))+SUM(parts))
into l_profit
from mss_inv_detl
WHERE INV_DATE=inv_date;
select
NVL(SUM(decode(invoice,'CASH',LBR))+SUM(decode(invoice,'CASH',MTL))+SUM(decode(invoice,'CASH',PARTS)),0)+
NVL(SUM(decode(invoice,'CREDIT',LBR))+SUM(decode(invoice,'CREDIT',MTL))+SUM(decode(invoice,'CREDIT',PARTS)),0)+
NVL(SUM(decode(invoice,'INTERNAL',LBR))+SUM(decode(invoice,'INTERNAL',MTL))+SUM(decode(invoice,'INTERNAL',PARTS)),0)+
NVL(SUM(decode(invoice,'WARRANTY',LBR))+SUM(decode(invoice,'WARRANTY',MTL))+SUM(decode(invoice,'WARRANTY',PARTS)),0)
into l_actual
from mss_inv_detl
WHERE INV_DATE=inv_date;
gp_pr := (l_profit/l_actual)*100;
return gp_pr;
end;
If I use like this the GP is showing the same value for all inv_date grouping.
I am not sure what to use in where actually it should be inv_date = :inv_date but it is showing error bind variable cannot be used. I tried using group by inv_date also it doesn't work.
Can you please help?
|
|
|
|
| Percentage of GP in Total [message #539039 is a reply to message #538875] |
Wed, 11 January 2012 00:06 |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
Dear Experts,
Please check the attached output in PDF. I need your experts help in calculating the GP at the Total level (group by inv_date). I think the result is wrong as it shows the same value for all the dates.
I have the following formula column in
function GP_PRFormula return Number is
l_profit number;
l_actual number;
gp_pr number;
begin
select sum(total)-(sum(DECODE(po_pur,'',0,PO_PUR))+sum(DECODE(pl_pur,'',0,PL_PUR))+SUM(parts))
into l_profit
from mss_inv_detl
WHERE INV_DATE=inv_date;
select
NVL(SUM(decode(invoice,'CASH',LBR))+SUM(decode(invoice,'CASH',MTL))+SUM(decode(invoice,'CASH',PARTS)),0)+
NVL(SUM(decode(invoice,'CREDIT',LBR))+SUM(decode(invoice,'CREDIT',MTL))+SUM(decode(invoice,'CREDIT',PARTS)),0)+
NVL(SUM(decode(invoice,'INTERNAL',LBR))+SUM(decode(invoice,'INTERNAL',MTL))+SUM(decode(invoice,'INTERNAL',PARTS)),0)+
NVL(SUM(decode(invoice,'WARRANTY',LBR))+SUM(decode(invoice,'WARRANTY',MTL))+SUM(decode(invoice,'WARRANTY',PARTS)),0)
into l_actual
from mss_inv_detl
WHERE INV_DATE=inv_date;
gp_pr := (l_profit/l_actual)*100;
-- gp_pr := (:SumgpPerINV_DATE / :SumACTUALPerINV_DATE);
return gp_pr;
end;
I think there is wrong in the where condition. How can I use the variable which is in the grouping. If I use :inv_date it shows error invalid frequency. Can anyone help?
|
|
|
|
| Re: GP Percentage in Total [message #539047 is a reply to message #539030] |
Wed, 11 January 2012 02:38 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Please, have a look at How to use [code] tags and make your code easier to read? guidelines - it will take only a few seconds, but will significantly improve readability of your future messages.
This:might be a culprit. This is what you did:SQL> select * from dept
2 WHERE DEPTNO = deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSQL> select * from dept
2 WHERE DEPTNO = &deptno;
Enter value for deptno: 10
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
See the difference? In reports, you'd precede it with a colon sign as I already told you in my previous message; didn't you see it? It means that your WHERE clause would look like
WHERE INV_DATE = :inv_date
P.S. After topics have being merged, I noticed that you discovered [code] tags; thank you for using them!
[Updated on: Wed, 11 January 2012 02:40] Report message to a moderator |
|
|
|
|
|
| Re: GP Percentage in Total [message #539129 is a reply to message #539125] |
Wed, 11 January 2012 08:46 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I think you need a formula column and a summary column.
A formula column to work out the GP per inv_date - which would go in the same group as inv date.
A summary column that would sum the formula column - this would go in the totals group.
|
|
|
|
| Re: GP Percentage in Total [message #539171 is a reply to message #539129] |
Wed, 11 January 2012 21:39 |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
Hi.
I tried variious methods even by using summary column but it did not work. In summary column I am not able to reset at inv_date level it is defaulted to Report and Page only.
|
|
|
|
|
|
| Re: GP Percentage in Total [message #539436 is a reply to message #539205] |
Sun, 15 January 2012 23:38 |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
Hi,
I have the following code in my formula. When I used this GP_PR to be printed it gives error messge
Column GP_PR references column 'SumgpPerINV_DATE',which has incompatible frequency.
Column GP_PR references column 'SumACTUALPerINV_DATE',which has incompatible frequency.
function GP_PRFormula return Number is
l_profit number;
l_actual number;
gp_pr number;
begin
gp_pr := (:sumgpperinv_date / :sumactualperinv_date)*100;
return gp_pr;
end;
I tried using Summary column but it askes me reset at Page or Report.
|
|
|
|
| Re: GP Percentage in Total [message #539448 is a reply to message #539436] |
Mon, 16 January 2012 01:32 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Instead of us constantly having to guess what you have and what you've done why don't you explain it in detail:
1) What groups do you have?
2) What columns are involved in this calculation?
3) What groups are they in?
4) What are sumgpperinv_date and sumactualperinv_date?
|
|
|
|
| Re: GP Percentage in Total [message #539452 is a reply to message #539448] |
Mon, 16 January 2012 02:05 |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
Hi,
I have uploaded the RDF file for your reference.
My Query Script.
SELECT INV_DATE,
SA,
NVL (
SUM (DECODE (invoice, 'CASH', LBR))
+ SUM (DECODE (invoice, 'CASH', MTL))
+ SUM (DECODE (invoice, 'CASH', PARTS)),
0
)
AS "CASH",
NVL (
SUM (DECODE (invoice, 'CREDIT', LBR))
+ SUM (DECODE (invoice, 'CREDIT', MTL))
+ SUM (DECODE (invoice, 'CREDIT', PARTS)),
0
)
AS "CREDIT",
NVL (
SUM (DECODE (invoice, 'INTERNAL', LBR))
+ SUM (DECODE (invoice, 'INTERNAL', MTL))
+ SUM (DECODE (invoice, 'INTERNAL', PARTS)),
0
)
AS "INT",
NVL (
SUM (DECODE (invoice, 'WARRANTY', LBR))
+ SUM (DECODE (invoice, 'WARRANTY', MTL))
+ SUM (DECODE (invoice, 'WARRANTY', PARTS)),
0
)
AS "WARR",
NVL (
SUM (DECODE (invoice, 'CASH', LBR))
+ SUM (DECODE (invoice, 'CASH', MTL))
+ SUM (DECODE (invoice, 'CASH', PARTS)),
0
)
+ NVL (
SUM (DECODE (invoice, 'CREDIT', LBR))
+ SUM (DECODE (invoice, 'CREDIT', MTL))
+ SUM (DECODE (invoice, 'CREDIT', PARTS)),
0
)
+ NVL (
SUM (DECODE (invoice, 'INTERNAL', LBR))
+ SUM (DECODE (invoice, 'INTERNAL', MTL))
+ SUM (DECODE (invoice, 'INTERNAL', PARTS)),
0
)
+ NVL (
SUM (DECODE (invoice, 'WARRANTY', LBR))
+ SUM (DECODE (invoice, 'WARRANTY', MTL))
+ SUM (DECODE (invoice, 'WARRANTY', PARTS)),
0
)
AS "ACTUAL",
NVL (SUM (PO_PUR), 0) + NVL (SUM (PL_PUR), 0) + NVL (SUM (parts), 0)
AS "COST",
SUM (total)
- ( SUM (DECODE (po_pur, '', 0, PO_PUR))
+ SUM (DECODE (pl_pur, '', 0, PL_PUR))
+ SUM (parts))
AS "GP",
DECODE (
SIGN(TRUNC (
(SUM (total)
- ( SUM (DECODE (po_pur, '', 0, PO_PUR))
+ SUM (DECODE (pl_pur, '', 0, PL_PUR))
+ SUM (parts)))
/ SUM (DECODE (TOTAL, 0, 1, TOTAL))
* 100,
2
)),
-1,
0,
TRUNC (
(SUM (total)
- ( SUM (DECODE (po_pur, '', 0, PO_PUR))
+ SUM (DECODE (pl_pur, '', 0, PL_PUR))
+ SUM (parts)))
/ SUM (DECODE (TOTAL, 0, 1, TOTAL))
* 100,
2
)
)
"GP_P"
FROM MSS_INV_DETL
GROUP BY INV_DATE, SA;
1) What groups do you have?
only Inv_date.
2) What columns are involved in this calculation?
All the columns
3) What groups are they in?
INV_DATE
4) What are sumgpperinv_date and sumactualperinv_date?
Please check the sql script above.
Sorry for any trouble caused in this manner.
|
|
|
|
| Re: GP Percentage in Total [message #539467 is a reply to message #539452] |
Mon, 16 January 2012 03:54 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
kumarvk wrote on Mon, 16 January 2012 08:051) What groups do you have?
only Inv_date.
You apparently don't understand what groups are. You have two, not one. G_INV_DATE and G_SA.
kumarvk wrote on Mon, 16 January 2012 08:05
4) What are sumgpperinv_date and sumactualperinv_date?
Please check the sql script above.
I see nothing above that answers that question.
Looking at your current formulae:
select sum(total)-(sum(DECODE(po_pur,'',0,PO_PUR))+sum(DECODE(pl_pur,'',0,PL_PUR))+SUM(parts))
into l_profit
from mss_inv_detl
WHERE INV_DATE=inv_date;
select
NVL(SUM(TOTAL),0)
-- NVL(SUM(decode(invoice,'CASH',LBR))+SUM(decode(invoice,'CASH',MTL))+SUM(decode(invoice,'CASH',PARTS)),0)+
-- NVL(SUM(decode(invoice,'CREDIT',LBR))+SUM(decode(invoice,'CREDIT',MTL))+SUM(decode(invoice,'CREDIT',PARTS)),0)+
-- NVL(SUM(decode(invoice,'INTERNAL',LBR))+SUM(decode(invoice,'INTERNAL',MTL))+SUM(decode(invoice,'INTERNAL',PARTS)),0)+
-- NVL(SUM(decode(invoice,'WARRANTY',LBR))+SUM(decode(invoice,'WARRANTY',MTL))+SUM(decode(invoice,'WARRANTY',PARTS)),0)
into l_actual
from mss_inv_detl
WHERE INV_DATE=inv_date;
So Add a new group above G_INV_DATE (just drag one of the items in the group above the group. Then move the item back to it's orginal group). Two a summary columns - one to summarise SumACTUALPerINV_DATE and one to summarise SumgpPerINV_DATE.
Then add a formula column to use those two new summary columns to work out the total gp..
|
|
|
|
| Re: GP Percentage in Total [message #539537 is a reply to message #539467] |
Mon, 16 January 2012 21:53 |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
Hi,
I did as I you said but the problem is the summary column I am not able to reset at date group level it has only to option Report & Page. I am able to reset at date group level then my problem is solved.
I don't why not able to reset at date level.
|
|
|
|
|
|
|
|
| Re: GP Percentage in Total [message #539561 is a reply to message #539549] |
Tue, 17 January 2012 03:01 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If you want a summary column to reset with the date it needs to be in the date group.
Summary columns that aren't in any group can only reset at page or report.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Nov 27 01:26:35 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
