 Home » Developer & Programmer » Reports & Discoverer » GP Percentage in Total (Windows Server 2003, Oracle 8i, Reports 5.0)
GP Percentage in Total Tue, 10 January 2012 02:06 kumarvk Messages: 214Registered: 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?
• Attachment: SA_INV_GP.pdf

[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: 21562Registered: June 2005 Location: Croatia, Europe Senior MemberAccount 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 #538878 is a reply to message #538877] Tue, 10 January 2012 02:34  ranamirfan Messages: 535Registered: January 2006 Location: Pakistan / Saudi Arabia Senior Member Dear,
Quote:

I want to calculate the GP at the Total column.

Try to put these column in main SQL Query.

``` (( ( Nvl(Acturl,0)-Nvl(Cost,0) ) / Nvl(Actual,0) )*100)GP
```

then sum this column in Report level / Inv_Date.

Regards,
Irfan
Re: GP Percentage in Total [message #539030 is a reply to message #538877] Tue, 10 January 2012 21:23  kumarvk Messages: 214Registered: 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.

Percentage of GP in Total [message #539039 is a reply to message #538875] Wed, 11 January 2012 00:06  kumarvk Messages: 214Registered: 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?
• Attachment: SA_INV_GP.pdf
Re: GP Percentage in Total [message #539047 is a reply to message #539030] Wed, 11 January 2012 02:38   Littlefoot Messages: 21562Registered: June 2005 Location: Croatia, Europe Senior MemberAccount 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:
`WHERE INV_DATE=inv_date;`
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 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 #539125 is a reply to message #539047] Wed, 11 January 2012 08:31  kumarvk Messages: 214Registered: July 2004 Senior Member
Hi,

I used :inv_date but it was giving error when I run the report. That what I was asking you how to use the group date.
Re: GP Percentage in Total [message #539129 is a reply to message #539125] Wed, 11 January 2012 08:46  cookiemonster Messages: 13894Registered: 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: 214Registered: 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 #539205 is a reply to message #539171] Thu, 12 January 2012 03:37  cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
I say you need formula and summary. You say you tried with summary.
Spot the difference?
Re: GP Percentage in Total [message #539436 is a reply to message #539205] Sun, 15 January 2012 23:38  kumarvk Messages: 214Registered: 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: 13894Registered: 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: 214Registered: July 2004 Senior Member
Hi,

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.
• Attachment: SA_GP_TEST.RDF
Re: GP Percentage in Total [message #539467 is a reply to message #539452] Mon, 16 January 2012 03:54  cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
kumarvk wrote on Mon, 16 January 2012 08:05
1) 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.

```  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: 214Registered: 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 #539544 is a reply to message #539537] Tue, 17 January 2012 00:16   Littlefoot Messages: 21562Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
Where EXACTLY did you create a summary column? Please, post a Data Model layout screenshot and mark that column so that we could see it.
Re: GP Percentage in Total [message #539549 is a reply to message #539544] Tue, 17 January 2012 00:41  kumarvk Messages: 214Registered: July 2004 Senior Member
Hi,

As per your request I have the screen dumps in the attached pdf format.
• Attachment: GP_FORM.pdf
Re: GP Percentage in Total [message #539561 is a reply to message #539549] Tue, 17 January 2012 03:01  cookiemonster Messages: 13894Registered: 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.
Re: GP Percentage in Total [message #539689 is a reply to message #539544] Wed, 18 January 2012 00:50  kumarvk Messages: 214Registered: July 2004 Senior Member
Hi Experts,

Any solution for my problem?
Re: GP Percentage in Total [message #539692 is a reply to message #539689] Wed, 18 January 2012 01:20   Littlefoot Messages: 21562Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
Cookiemonster in his last message said exactly the same I meant to say, so ... nothing new from me.
Re: GP Percentage in Total [message #539707 is a reply to message #539689] Wed, 18 January 2012 03:21  cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
kumarvk wrote on Wed, 18 January 2012 06:50
Hi Experts,

Any solution for my problem?

If you don't understand my post just say so.
Re: GP Percentage in Total [message #540040 is a reply to message #539707] Thu, 19 January 2012 20:14  kumarvk Messages: 214Registered: July 2004 Senior Member
Re: GP Percentage in Total [message #540084 is a reply to message #540040] Fri, 20 January 2012 03:32 