sam15
asked on
Crystall_View
I have a Crystal Report based on this SQL Command and uses 3 parameters.
BEcause I want to use dynamic parameters I want to create a VIEW in DB.
The problem is that I SUM quantity requested and quantity shipped for the date range given in the subquery.
If i use a view I would not be able to use this. Any ideas.
I do not want to try a 4 table join because items may not match.
select a.stock_number,a.descripti on,a.categ ory,vendor vendcode,
(select decode(sum(quantity_reques ted),null, 0,sum(quan tity_reque sted))
from stock_requested_item c, stock_request d
where c.request_id=d.request_id and c.stock_number=a.stock_num ber
and d.request_date between {?P_Begin_Date} and {?P_End_Date}
and d.ship_to_org like decode('{?P_Customer}','*A ll Customers*','%','{?P_Custo mer}' )
) Total_Ordered,
(select decode(sum(quantity_shippe d),null,0, sum(quanti ty_shipped )) from
stock_shipped_item e, stock_shipment f
where e.shipment_id=f.shipment_i d and e.stock_number=a.stock_num ber
and f.shipment_date between {?P_Begin_Date} and {?P_End_Date}
and (e.dispcode is not null )
and f.ship_to_org like decode('{?P_Customer}','*A ll Customers*','%','{?P_Custo mer}' )
) Total_Shipped
,
(select name||', '||city||', '||state)
from org where orgcd='{?P_Customer}'
) Customer
from stock_item a, org b
where a.vendor=b.orgcd(+)
and stock_type in ('P','C')
order by 1
BEcause I want to use dynamic parameters I want to create a VIEW in DB.
The problem is that I SUM quantity requested and quantity shipped for the date range given in the subquery.
If i use a view I would not be able to use this. Any ideas.
I do not want to try a 4 table join because items may not match.
select a.stock_number,a.descripti
(select decode(sum(quantity_reques
from stock_requested_item c, stock_request d
where c.request_id=d.request_id and c.stock_number=a.stock_num
and d.request_date between {?P_Begin_Date} and {?P_End_Date}
and d.ship_to_org like decode('{?P_Customer}','*A
) Total_Ordered,
(select decode(sum(quantity_shippe
stock_shipped_item e, stock_shipment f
where e.shipment_id=f.shipment_i
and f.shipment_date between {?P_Begin_Date} and {?P_End_Date}
and (e.dispcode is not null )
and f.ship_to_org like decode('{?P_Customer}','*A
) Total_Shipped
,
(select name||', '||city||', '||state)
from org where orgcd='{?P_Customer}'
) Customer
from stock_item a, org b
where a.vendor=b.orgcd(+)
and stock_type in ('P','C')
order by 1
Create either a VIEW or even better use a Stored Proc as the datasource.
Just create a separate SQL Command to aggregate your SUMS based on your requirement, then link to your main data table. You may need to use the MIN() function to display the SUM amounts,...but if I'm understanding your question, it would not be a difficult workaround...
Hope it helps...
Just create a separate SQL Command to aggregate your SUMS based on your requirement, then link to your main data table. You may need to use the MIN() function to display the SUM amounts,...but if I'm understanding your question, it would not be a difficult workaround...
Hope it helps...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Let me confirm your solution for main report and subreport.
I think i would leave my current report as as is now and just link it to a new report with dynamic parameters?
Does this solution take a long time to implement. I have done a few subreports a few years back and i recall it takes some time. That seems the only way though for dynamic parameters.
If i leave the parameter as static i guess i have to keep refreshing the list manually. right?
I think i would leave my current report as as is now and just link it to a new report with dynamic parameters?
Does this solution take a long time to implement. I have done a few subreports a few years back and i recall it takes some time. That seems the only way though for dynamic parameters.
If i leave the parameter as static i guess i have to keep refreshing the list manually. right?
Yes, if they remain static you have to refresh the list periodically or when the values change.
A subreport is just a report that you embed inside a report.
SInce you have the basic report built you could make a copy of the main report then eliminate everything you don't need in it except the details you want displayed.
mlmcc
A subreport is just a report that you embed inside a report.
SInce you have the basic report built you could make a copy of the main report then eliminate everything you don't need in it except the details you want displayed.
mlmcc
ASKER
Excellent!
Why can't you create and use that sql as a view?