用友UAP报表存储过程开发总结

目录

1

写存储过程，并在

UFData

库执行

................................................................................................

2

2

新建报表

.........................................................................................................................................

3

3

数据权限控制

6

过滤条件支持系统枚举

1

库执行

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =

OBJECT_ID(N'[dbo].[LJYC_SaleOrderReport_Pro]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[LJYC_SaleOrderReport_Pro]

@cStartTime varchar(20) = '1900-01-01', --开始日期

@cStopTime varchar(20) = '1900-01-01',--结束日期

@IsClose varchar(20) = '-1',--是否已关闭,全部,是,否

@cCusCode varchar(2000) = '-1'--客户

as

begin

declare @esql varchar(8000)

declare @iwhere varchar(2000) --过滤条件

declare @dStartTime datetime

declare @dStopTime datetime

set @dStartTime = cast(@cStartTime as datetime)

set @dStopTime = cast(@cStopTime as datetime)

set @dStopTime = DATEADD(hour,24,@dStopTime)

set @dStopTime = DATEADD(Second,-1,@dStopTime)

set @cStartTime = CONVERT(varchar(100), @dStartTime, 20)

set @cStopTime = CONVERT(varchar(100), @dStopTime, 20)

--过滤条件

if @cCusCode!='-1' and @cCusCode!=''

begin

set @iwhere = @iwhere + ' and isnull(A.cCusCode,'''') in ('''+@cCusCode+''')'

begin

set @iwhere = @iwhere + ' and isnull(cCloser,'''')!='''' '

end

if @IsClose = '否'

begin

set @iwhere = @iwhere + ' and isnull(cCloser,'''')='''' '

end

set @esql = 'SELECT A.cSOCode AS 单据号,CONVERT(VARCHAR(100),A.dDate,23) AS 单据日

期,A.cCusCode AS 客户编码,CT.cCusName AS ccusname,B.cInvCode AS 存货编码,I.cInvName AS

存货名称,CONVERT(DECIMAL(18,2),B.iQuantity) AS 数量 FROM dbo.SO_SOMain A INNER JOIN