用这个方法,先到查询分析器执行下面这段代码:
create proc newnew
@begdate datetime,
@enddate datetime
as
set nocount on
create table #aa(
订单号 varchar(255),
订单分录号 int,
物料编码 varchar(255),
物料名称 varchar(255),
规格型号 varchar(255),
订单数量 decimal(18,2),
纳期 datetime,
合计出货量 decimal(18,2),
实际最晚交期 datetime,
满足订单 decimal(18,10)
)
insert into #aa(订单号,订单分录号,物料编码,物料名称,规格型号,订单数量,纳期,合计出货量,实际最晚交期,满足订单)
select distinct t1.fbillno as '订单号',
t2.fentryid as '订单分录号',
t3.fnumber as '物料编码',
t3.fname as '物料名称',
t3.fmodel as '规格型号',
t2.fqty as '订单数量',
t2.fdate as '纳期',
case when t4.ftrantype='21' and t5.forderinterid=t2.finterid and t2.fentryid=t5.forderentryid then sum(t5.fqty) else 0 end as '合计出货量',
case when t4.ftrantype='21' and t5.forderinterid=t2.finterid and t2.fentryid=t5.forderentryid then max(t4.fdate) else null end as '实际最晚交期',
case when sum(t5.fqty)>=t2.fqty then 1.000 else 0.000 end as '满足订单'
FROM seorder t1 INNER JOIN seorderentry t2 on t1.finterid=t2.finterid
INNER JOIN t_icitem t3 ON t3.fitemid=t2.fitemid
LEFT JOIN icstockbillentry t5 ON t5.forderinterid=t2.finterid and t5.forderentryid=t2.fentryid
LEFT JOIN icstockbill t4 ON t4.finterid=t5.finterid
WHERE t1.FDate>=@begdate
and t1.FDate GROUP BY t1.fbillno,t4.ftrantype,t2.fentryid,t2.fitemid,t3.fname,t3.fmodel,t2.fqty,t2.fdate,t3.fnumber,t5.forderentryid,t5.forderentryid,t5.forderinterid,t2.finterid
order by t1.fbillno

insert into #aa (订单号,满足订单)
select '完成百分比',round(convert(decimal(18,10),sum(满足订单))/convert(decimal(18,10),count(*)),3) from
(select distinct t1.fbillno as '订单号',
t2.fentryid as '订单分录号',
t3.fnumber as '物料编码',
t3.fname as '物料名称',
t3.fmodel as '规格型号',
t2.fqty as '订单数量',
t2.fdate as '纳期',
case when t4.ftrantype='21' and t5.forderinterid=t2.finterid and t2.fentryid=t5.forderentryid then sum(t5.fqty) else 0 end as '合计出货量',
case when t4.ftrantype='21' and t5.forderinterid=t2.finterid and t2.fentryid=t5.forderentryid then max(t4.fdate) else null end as '实际最晚交期',
case when sum(t5.fqty)>=t2.fqty then 1 else 0 end as '满足订单'
FROM seorder t1 INNER JOIN seorderentry t2 on t1.finterid=t2.finterid
INNER JOIN t_icitem t3 ON t3.fitemid=t2.fitemid
LEFT JOIN icstockbillentry t5 ON t5.forderinterid=t2.finterid and t5.forderentryid=t2.fentryid
LEFT JOIN icstockbill t4 ON t4.finterid=t5.finterid
WHERE t1.FDate>=@begdate
and t1.FDate GROUP BY t1.fbillno,t4.ftrantype,t2.fentryid,t2.fitemid,t3.fname,t3.fmodel,t2.fqty,t2.fdate,t3.fnumber,t5.forderentryid,t5.forderentryid,t5.forderinterid,t2.finterid ) a

select * from #aa order by 订单号
drop table #aa


命令成功完成后,到查询分析工具或BOS里面新建SQL报表,输入: newnew '********','########'

经过这个动作后应该可以了.
修改时间：2007-9-11 16:26:58

---

稳健发展，追求卓越。