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 Go to next message
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?
  • Attachment: SA_INV_GP.pdf
    (Size: 8.67KB, Downloaded 1573 times)

[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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 #538878 is a reply to message #538877] Tue, 10 January 2012 02:34 Go to previous messageGo to next message
ranamirfan
Messages: 535
Registered: 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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?
  • Attachment: SA_INV_GP.pdf
    (Size: 9.15KB, Downloaded 1358 times)
Re: GP Percentage in Total [message #539047 is a reply to message #539030] Wed, 11 January 2012 02:38 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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:
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 Go to previous messageGo to next message
kumarvk
Messages: 214
Registered: 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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 #539205 is a reply to message #539171] Thu, 12 January 2012 03:37 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: 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.

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 Go to previous messageGo to next message
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 #539544 is a reply to message #539537] Tue, 17 January 2012 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account 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 Go to previous messageGo to next message
kumarvk
Messages: 214
Registered: July 2004
Senior Member
Hi,

As per your request I have the screen dumps in the attached pdf format.
  • Attachment: GP_FORM.pdf
    (Size: 131.73KB, Downloaded 1616 times)
Re: GP Percentage in Total [message #539561 is a reply to message #539549] Tue, 17 January 2012 03:01 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: GP Percentage in Total [message #539689 is a reply to message #539544] Wed, 18 January 2012 00:50 Go to previous messageGo to next message
kumarvk
Messages: 214
Registered: 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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account 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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: 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 Go to previous messageGo to next message
kumarvk
Messages: 214
Registered: July 2004
Senior Member
Hi Mr.Cookiemonster,

Sorry to say I could not understand your post. Could you please explain me in detail.

Re: GP Percentage in Total [message #540084 is a reply to message #540040] Fri, 20 January 2012 03:32 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What do you not understand? Create the summary column inside the group g_inv_date.
Previous Topic: Oracle Reports Version Conflict
Next Topic: Error While running report
Goto Forum:
  


Current Time: Fri Mar 29 06:55:50 CDT 2024