需求说明
客户有一个用于需求管理的项目,用于管理和跟踪公司各个业务系统的新增需求。在此项目中,定义了一个“产品”字段(多项选择类型),用于描述一个需求所对应的产品(一个需求可能对应多个产品)。现在,客户希望统计出来每个产品所提交的需求有多少,跟踪中、已关闭,以及超期未关闭的事务有多少。
实现方案
对于客户在字段定义中所描述的每一个可选值,统计其事务数量、未关闭的事务数量、已关闭的事务数量、超期未关闭的事务数量,写入一个临时表或表变量中,然后从此表中取出即可。
首先,需要在数据库中创建两个辅助函数,用来将可选值字符串拆开成一个个可选值。
-- 取得某个长字符串中所含项的个数
create function [dbo].[Get_StrArrayLength]
(
@str nvarchar(4000), --要分割的字符串
@split nvarchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
--取得某个长字符串中的第n项
create function [dbo].[Get_StrArrayStrOfIndex]
(
@str nvarchar(4000), --要分割的字符串
@split nvarchar(10), --分隔符号
@index int --取第几个元素
)
returns nvarchar(4000)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1
--这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。
return substring(@str,@start,@location-@start)
end
然后,创建报表。选择报表类型为“单个查询”,输入如下的查询语句:
-- 声明临时表,用于存储查询出来的数据
declare @tb table(project nvarchar(50), total int, completed int, notcompleted int, overtime int)
declare @str varchar(5000)
-- 定义所有可能的值
set @str='ACRM系统;CC系统;ERP系统;ETF套利系统;OA系统;OCRM系统'
-- 对每个可能的可选值循环统计,并插入表变量中
declare @next int
set @next=1
while @next<=dbo.Get_StrArrayLength(@str,';')
begin
declare @project nvarchar(10)
set @project= dbo.Get_StrArrayStrOfIndex(@str,';',@next)
declare @total int
declare @completed int
declare @notcompleted int
declare @overtime int
select @total=sum(1),
@completed = sum(case when IsClosed=1 then 1 else 0 end),
@notcompleted = sum(case when IsClosed=0 then 1 else 0 end),
@overtime = sum(case when IsClosed=0 and Deadline<GetDate() then 1 else 0 end)
FROM v_Pts_Problems where ProjectID=项目ID and MultiSelect1 like '%' + @project + '%'
insert into @tb values(@project, @total, @completed, @notcompleted, @overtime)
set @next=@next+1
end
-- 从表变量中查询
select project as [项目名称],
total as [总数],
completed as [跟踪中],
notcompleted as [已关闭],
overtime as [超期未关闭] from @tb
报表运行后的结果:
项目名称 |
总数 |
跟踪中 |
已关闭 |
超期未关闭 |
ACRM系统 |
|
|
|
|
CC系统 |
|
|
|
|
ERP系统 |
|
|
|
|
OA系统 |
|
|
|
|
OCRM系统 |
1 |
0 |
1 |
0 |
公司网站 |
3 |
1 |
2 |
0 |