Home » Developer & Programmer » Reports & Discoverer » How to use Formula column in my report (report6i)
How to use Formula column in my report [message #408593] Wed, 17 June 2009 00:52 Go to next message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

Hi all,

I have to print a report with 4 columns
1.customer name
2.product
3.Last week
4.This week


I am getting problem in finiding the Last week product sales order and this week sales order. Can i use the formula column for this ?

Kindly suggest me
Thanks and regards
M.A.Aiyaz
Re: How to use Formula column in my report [message #408616 is a reply to message #408593] Wed, 17 June 2009 01:37 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess you can; you'll probably have to reference customer and/or product as well as certain date frame (this or last week) and return calculated value.
Re: How to use Formula column in my report [message #408626 is a reply to message #408616] Wed, 17 June 2009 02:08 Go to previous messageGo to next message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

Thanks for the response

I got some issue here when i tried your way

I have taken the main query as

select a.customer_name,d.description ,c.request_date
from
ra_customers a,
oe_order_headers_all b,
oe_order_lines_all c,
mtl_system_items_b d
where
a.customer_id=b.sold_to_org_id and
b.header_id=c.header_id and
c.inventory_item_id=d.inventory_item_id

and taken a field as LAST_WEEK and given source as formula column
The code for formula column is

function CF_1Formula return Number is
x number;
begin
select sum(c.ordered_quantity) into x
from oe_order_headers_all b,
oe_order_lines_all c
where
to_char(c.request_date) =to_char(to_date('23-JAN-09')-7)
and b.sold_to_org_id =1697
and b.header_id=c.header_id
and c.request_date > '01-JAN-09'
and c.inventory_item_id =65938
group by b.sold_to_org_id,c.ordered_item, c.inventory_item_id, c.request_date;
return x;
end;

But in the report there is coming many rows it suppose to come only single row...

Kindly help me out

and even i have to get the ordered quantity according to the dates with respect to each customers

Re: How to use Formula column in my report [message #408630 is a reply to message #408626] Wed, 17 June 2009 02:24 Go to previous messageGo to next message
alijeyan
Messages: 184
Registered: January 2007
Location: IRAN
Senior Member
Hi
You must delete line :
group by .......
from your select statment in formula column .

ALI


Re: How to use Formula column in my report [message #408631 is a reply to message #408630] Wed, 17 June 2009 02:37 Go to previous messageGo to next message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

Hi Ali,

I am getting the report output with different customer names and product and dates also but can you suggest me for the feild last week which i have to store ordered_quantity for that respective dates....

But i am getting the same value for all the rows in LAST_WEEK

Is my formula column query correct ?

please help me

Thanks
M.A.Aiyaz
Re: How to use Formula column in my report [message #408851 is a reply to message #408630] Thu, 18 June 2009 01:14 Go to previous messageGo to next message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

select x.sold_to_org_id, x.ordered_item,x.inventory_item_id, sum(x.week1) week1, sum(x.week2) week2, sum(x.week3) week3, sum(x.week4) week4, sum(x.week5) week5,
case
when sum(x.week1) is not null and sum(x.week2) is not null and sum(x.week1) > sum(x.week2) or sum(x.week2) > sum(x.week1) then
sum(x.week1)-sum(x.week2)
when sum(x.week2) is not null and sum(x.week3) is not null and sum(x.week2) > sum(x.week3) or sum(x.week3) > sum(x.week2) then
sum(x.week2)-sum(x.week3)
when sum(x.week3) is not null and sum(x.week4) is not null and sum(x.week3) > sum(x.week4) or sum(x.week4) > sum(x.week3) then
sum(x.week3)-sum(x.week4)
when sum(x.week4) is not null and sum(x.week5) is not null and sum(x.week4) > sum(x.week5) or sum(x.week4) > sum(x.week5) then
sum(x.week4)-sum(x.week5)
else null
end as Difference
from
(
select b.sold_to_org_id,c.ordered_item, c.inventory_item_id,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 1 then (c.ordered_quantity)
else null
end as WEEK1,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 2 then (c.ordered_quantity)
else null
end as WEEK2,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 3 then (c.ordered_quantity)
else null
end as WEEK3,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 4 then (c.ordered_quantity)
else null
end as WEEK4,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 5 then (c.ordered_quantity)
else null
end as WEEK5
from oe_order_headers_all b,
oe_order_lines_all c
where
b.sold_to_org_id IN (1697,1723,1714,19099,1714,2431) and
b.header_id=c.header_id
and c.request_date > '01-JAN-09'
and c.inventory_item_id IN (65938,1449,43520,17352,43940,67277)
) x
group by x.sold_to_org_id,x.ordered_item, x.inventory_item_id



This Query is not working properly as per my requirement i need to get the difference from any two particular weeks. How can i assign number to the week columns amd used that in my difference column to get the difference between two columns.


Kindly help me..ASAP it would be better.

Thanks in advance
Aiyaz
Re: How to use Formula column in my report [message #409360 is a reply to message #408630] Mon, 22 June 2009 05:48 Go to previous message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

thank you for all your support
Previous Topic: Cross Tab Report in discoverer
Next Topic: Standard Oracle COGS Report
Goto Forum:
  


Current Time: Fri Mar 29 02:58:40 CDT 2024