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: 21562 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: 21562 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 BOSTON But no, you should have done this:SQL> 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 likeWHERE 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: 13894 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: 13894 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: 13894 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;
This is equivalent to the gp column from the query.
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;
This looks like it should be actual column from the query.
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: 13894 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: Sat May 30 14:23:30 CDT 2020
