时间戳

首页 > 解决方案 > SSRS：在同一报告中显示单个数据集和不同参数值的计数函数的结果

sql-server - SSRS：在同一报告中显示单个数据集和不同参数值的计数函数的结果

问题描述

我有一个非常复杂的查询作为具有 5 个参数的数据集。我想在同一个报告中显示整个数据集的计数，其中 3 个参数保持不变，其他 2 个参数 @TYPE_PERMIS 和 @Indicator 采用所有可能的值：

  @PERIODE varchar(18)
        ,@DT_DEB datetime
        ,@DT_FIN datetime
        ,@TYPE_PERMIS VARCHAR(50)
        ,@Indicateur varchar(25)

   @DT_DEB = '20180401'
        ,@DT_FIN = '20210331'--not variable
        ,@PERIODE='Période déterminée' --not variable
       ,@TYPE_PERMIS='GAZ'--Possible values :'GAZ', 'UEP', 'ISP'
       ,@Indicator='Recues'-- possible values:'Recues','acceptees', 'refusees', 'actives', 'Finalisees'

这里是报告的一个例子

在此处输入图像描述

这是对数据集的查询

select  distinct d.GI3_N_DEMN,
d.GI3_C_TYP_DEMN,

convert(varchar(19), d.GI3_D_RECEPTION,121) [Date de réception],
convert(varchar(19),  d.GI3_D_DAT_FERM,121) [Date de fermeture],

sum(convert(numeric(10,2),isnull(ACT.GI3_D_FIN_ACTV,d.GI3_D_DAT_FERM)-isnull(ACT.GI3_D_DEBT,d.GI3_D_DAT_FERM)) )  OVER (PARTITION BY d.GI3_C_ROWD_DEMN_DECL)+
  sum(convert(numeric(10,2),isnull(ACTi.FIN_DT,d.GI3_D_DAT_FERM)-isnull(ACTi.DEB_DT,d.GI3_D_DAT_FERM)) )  OVER (PARTITION BY d.GI3_C_ROWD_DEMN_DECL)
as [Durée attente de documents]
,convert(numeric(10,2),
isnull(d.GI3_D_DAT_FERM, getdate()) 
- d.GI3_D_RECEPTION 
 - sum(convert(numeric(10,2),isnull(ACT.GI3_D_FIN_ACTV,d.GI3_D_DAT_FERM)-isnull(ACT.GI3_D_DEBT,d.GI3_D_DAT_FERM)) )  OVER (PARTITION BY d.GI3_C_ROWD_DEMN_DECL)--)as Délai
 - sum(convert(numeric(10,2),isnull(ACTi.FIN_DT,d.GI3_D_DAT_FERM)-isnull(ACTi.DEB_DT,d.GI3_D_DAT_FERM)) )  OVER (PARTITION BY d.GI3_C_ROWD_DEMN_DECL)--)as Délai
  )as [Délai de traitement]
 
  from GI3_DEMANDE_DECLARATION d
left outer join  dbo.GI3_ACTIVITE ACT on ACT.GI3_C_ROWD_DEMN_DECL = d.GI3_C_ROWD_DEMN_DECL and  ACT.GI3_DE_ACTV like 'Attente%' and    @TYPE_PERMIS in ('UEP','GAZ')

left outer join 
(select v.* from  GI3_VERSION_PERMIS_RBQ  v  inner join
(select v.GI3_C_ROWD_DEMN_DECL,MAX(v.GI3_D_CRET) GI3_D_CRET from GI3_VERSION_PERMIS_RBQ  v group by v.GI3_C_ROWD_DEMN_DECL)  vv 
on v.GI3_C_ROWD_DEMN_DECL=vv.GI3_C_ROWD_DEMN_DECL and vv.GI3_D_CRET=v.GI3_D_CRET)  v on d.GI3_C_ROWD_DEMN_DECL=v.GI3_C_ROWD_DEMN_DECL

left outer join GI3_INTERVENTION i on i.GI3_C_ROWD_DEMN_DECL=d.GI3_C_ROWD_DEMN_DECL and   @TYPE_PERMIS='ISP'
left outer join 

(
select GI3_C_ROWD_INTRVT, POS, MAX(DEB_DT) DEB_DT , MAX(FIN_DT) FIN_DT from
(select GI3_C_ROWD_INTRVT, CASE WHEN TYPERUP='D' then POSITION ELSE POSITION-1 END POS, CASE WHEN TYPERUP='D' then RUPTURE_DT ELSE 0 END DEB_DT , CASE WHEN TYPERUP='F' then RUPTURE_DT ELSE 0 END FIN_DT from
(select  GI3_C_ROWD_INTRVT, ROW_NUMBER() OVER(ORDER BY GI3_C_ROWD_INTRVT,RUPTURE_DT,TYPERUP) AS POSITION ,RUPTURE_DT,TYPERUP from
(Select GI3_C_ROWD_ACTV, GI3_C_ROWD_INTRVT,  ISNULL(GI3_D_FIN_ACTV,GETDATE()) as RUPTURE_DT,  'F' AS TYPERUP from GI3_ACTIVITE T
where GI3_C_ROWD_INTRVT is not null and  GI3_DE_ACTV like 'Attend%' and GI3_D_FIN_ACTV>GI3_D_DEBT
and  T.GI3_C_ROWD_ACTV not in (
select GI3_C_ROWD_ACTV from
(SELECT
T1.GI3_C_ROWD_ACTV, T1.GI3_C_ROWD_INTRVT,   T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV,ROW_NUMBER() OVER(ORDER BY T1.GI3_C_ROWD_INTRVT, T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV) AS POSITION
   FROM 
       dbo.GI3_ACTIVITE T1 

 WHERE 
     T1.GI3_C_ROWD_INTRVT is not null and  GI3_DE_ACTV like 'Attend%' and GI3_D_FIN_ACTV>GI3_D_DEBT) s1
inner join 
     (SELECT  T1.GI3_C_ROWD_INTRVT,   T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV,ROW_NUMBER() OVER(ORDER BY T1.GI3_C_ROWD_INTRVT, T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV) AS POSITION  FROM dbo.GI3_ACTIVITE T1 WHERE  T1.GI3_C_ROWD_INTRVT is not null and  GI3_DE_ACTV like 'Attend%' and GI3_D_FIN_ACTV>GI3_D_DEBT)s2
on s1.GI3_C_ROWD_INTRVT=s2.GI3_C_ROWD_INTRVT
and s1.GI3_D_FIN_ACTV>=s2.GI3_D_DEBT
and s1.GI3_D_FIN_ACTV<=ISNULL(s2.GI3_D_FIN_ACTV, GETDATE())
and s1.POSITION<s2.POSITION
)

and GI3_C_ROWD_ACTV not in 
(


select GI3_C_ROWD_ACTV from
(
select GI3_C_ROWD_ACTV, GI3_C_ROWD_INTRVT, GI3_D_DEBT, GI3_D_FIN_ACTV, POSITION, MAX(FIN) m from
(
select i1.*, i2.GI3_D_FIN_ACTV FIN from 
(
SELECT
T1.GI3_C_ROWD_ACTV, T1.GI3_C_ROWD_INTRVT,   T1.GI3_D_DEBT,ISNULL(GI3_D_FIN_ACTV, GETDATE()) GI3_D_FIN_ACTV,ROW_NUMBER() OVER(ORDER BY T1.GI3_C_ROWD_INTRVT, T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV) AS POSITION
   FROM  dbo.GI3_ACTIVITE T1 
 WHERE T1.GI3_C_ROWD_INTRVT is not null and  GI3_DE_ACTV like 'Attend%' and GI3_D_FIN_ACTV>GI3_D_DEBT
     ) i1
     inner join 
(SELECT
T1.GI3_C_ROWD_INTRVT,   ISNULL(GI3_D_FIN_ACTV, GETDATE()) GI3_D_FIN_ACTV ,ROW_NUMBER() OVER(ORDER BY T1.GI3_C_ROWD_INTRVT, T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV) AS POSITION
   FROM dbo.GI3_ACTIVITE T1 
 WHERE T1.GI3_C_ROWD_INTRVT is not null and  GI3_DE_ACTV like 'Attend%' and GI3_D_FIN_ACTV>GI3_D_DEBT) i2 on 
     
i1.GI3_C_ROWD_INTRVT=i2.GI3_C_ROWD_INTRVT and i1.POSITION>=i2.POSITION   
)i
group by GI3_C_ROWD_ACTV, GI3_C_ROWD_INTRVT, GI3_D_DEBT, GI3_D_FIN_ACTV,POSITION
having GI3_D_FIN_ACTV !=MAX(FIN))s
)
union 
Select  GI3_C_ROWD_ACTV, GI3_C_ROWD_INTRVT,   GI3_D_DEBT as RUPTURE_DT,  'D'  as TYPERUP from GI3_ACTIVITE T
 
where GI3_C_ROWD_INTRVT is not null and  GI3_DE_ACTV like 'Attend%' and GI3_D_FIN_ACTV>GI3_D_DEBT and T.GI3_C_ROWD_ACTV not in (
select GI3_C_ROWD_ACTV from
(SELECT
 T1.GI3_C_ROWD_INTRVT,   T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV,ROW_NUMBER() OVER(ORDER BY T1.GI3_C_ROWD_INTRVT, T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV) AS POSITION
   FROM 
       dbo.GI3_ACTIVITE T1 
 WHERE 
     T1.GI3_C_ROWD_INTRVT is not null and  GI3_DE_ACTV like 'Attend%' and GI3_D_FIN_ACTV>GI3_D_DEBT) s1
inner join 
     (SELECT T1.GI3_C_ROWD_ACTV,  T1.GI3_C_ROWD_INTRVT,   T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV,ROW_NUMBER() OVER(ORDER BY T1.GI3_C_ROWD_INTRVT, T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV) AS POSITION  FROM dbo.GI3_ACTIVITE T1 WHERE  T1.GI3_C_ROWD_INTRVT is not null and  GI3_DE_ACTV like 'Attend%' and GI3_D_FIN_ACTV>GI3_D_DEBT)s2
on s1.GI3_C_ROWD_INTRVT=s2.GI3_C_ROWD_INTRVT
and s1.GI3_D_FIN_ACTV>=s2.GI3_D_DEBT
and s1.GI3_D_FIN_ACTV<=ISNULL(s2.GI3_D_FIN_ACTV, GETDATE())
and s1.POSITION<s2.POSITION
)

and GI3_C_ROWD_ACTV not in 
(


select GI3_C_ROWD_ACTV from
(
select GI3_C_ROWD_ACTV, GI3_C_ROWD_INTRVT, GI3_D_DEBT, GI3_D_FIN_ACTV, POSITION, MAX(FIN) m from
(
select i1.*, i2.GI3_D_FIN_ACTV FIN from 
(
SELECT
T1.GI3_C_ROWD_ACTV, T1.GI3_C_ROWD_INTRVT,   T1.GI3_D_DEBT,ISNULL(GI3_D_FIN_ACTV, GETDATE()) GI3_D_FIN_ACTV,ROW_NUMBER() OVER(ORDER BY T1.GI3_C_ROWD_INTRVT, T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV) AS POSITION
   FROM  dbo.GI3_ACTIVITE T1 
 WHERE T1.GI3_C_ROWD_INTRVT is not null and  GI3_DE_ACTV like 'Attend%' and GI3_D_FIN_ACTV>GI3_D_DEBT
     ) i1
     inner join 
(SELECT
T1.GI3_C_ROWD_INTRVT,   ISNULL(GI3_D_FIN_ACTV, GETDATE()) GI3_D_FIN_ACTV ,ROW_NUMBER() OVER(ORDER BY T1.GI3_C_ROWD_INTRVT, T1.GI3_D_DEBT,T1.GI3_D_FIN_ACTV) AS POSITION
   FROM dbo.GI3_ACTIVITE T1 
 WHERE T1.GI3_C_ROWD_INTRVT is not null and  GI3_DE_ACTV like 'Attend%' and GI3_D_FIN_ACTV>GI3_D_DEBT) i2 on 
     
i1.GI3_C_ROWD_INTRVT=i2.GI3_C_ROWD_INTRVT and i1.POSITION>=i2.POSITION   
)i
group by GI3_C_ROWD_ACTV, GI3_C_ROWD_INTRVT, GI3_D_DEBT, GI3_D_FIN_ACTV,POSITION
having GI3_D_FIN_ACTV !=MAX(FIN))s
)
)l
)v)w
GROUP by GI3_C_ROWD_INTRVT, POS
)
 ACTi on  ACTi.GI3_C_ROWD_INTRVT = i.GI3_C_ROWD_INTRVT 
--and  ACTi.GI3_DE_ACTV like 'Attend%'

inner join
     (select 'TOUTE' as PER, 
case 
    when @PERIODE = 'Période déterminée'  then @DT_DEB 
    when @PERIODE = 'Mensuelle' then dateadd(mm,-1, DATEADD(s,0,DATEADD(mm, DATEDIFF(m,1,getdate()),0)))
    when @PERIODE = 'Cumulatif' then  convert(datetime, substring (convert(char(10),(dateadd(mm,-3, getdate())),120),1,4) +'-04'+'-01',120)
end as DT_DEB,
case 
    when @PERIODE = 'Période déterminée'  then @DT_FIN 
    else dateadd(d,-1, DATEADD(s,0,DATEADD(mm, DATEDIFF(m,1,getdate()),0)))
end as DT_FIN) p
--@Indicateur='Finalisees'--'Recues','acceptees', 'refusees', 'actives' 
on (d.GI3_D_RECEPTION  < DT_FIN + '23:59:59' and isnull(d.GI3_D_DAT_FERM,getdate())> DT_FIN + '23:59:59' and @Indicateur='Actives') --Demandes actives en fin de période
or (isnull(d.GI3_D_DAT_FERM,getdate())between DT_DEB and DT_FIN + '23:59:59' and @Indicateur in ('Finalisees', 'Acceptees','Refusees'))
or (d.GI3_D_RECEPTION between DT_DEB and DT_FIN + '23:59:59' and @Indicateur='Recues')
--or (d.GI3_D_RECEPTION between DT_DEB and DT_FIN + '23:59:59' and @Indicateur='Recues')
 Where 
 ((d.GI3_C_TYP_DEMN = '130' and @TYPE_PERMIS='UEP') or (d.GI3_C_TYP_DEMN = '150' and @TYPE_PERMIS='GAZ')or (d.GI3_C_TYP_DEMN = '13' and @TYPE_PERMIS='ISP')) 

  and d.GI3_C_STAT!='Annulé'
  and d.GI3_C_ROWD_INTRVN!= '1-6GOW3'--intervenant de test
  --and d.GI3_N_DEMN='1-5253985073'
  
 and ((v.GI3_C_ROWD_DEMN_DECL is null and @Indicateur='Refusees') or (v.GI3_C_ROWD_DEMN_DECL is not null and @Indicateur='Acceptees') or (@Indicateur='Recues')or (@Indicateur= 'Actives' ) or (@Indicateur= 'Finalisees'))

标签： sql-serverreporting-servicesparameterscount

解决方案

我会将您的结果放在临时表中，获取计数，然后使用任何过滤查询临时表 - 添加一个包含所需结果的额外列。

SELECT BLAH, BLAH_BLAH
INTO #TEMP_TABLE
FROM <YOUR QUERY>

DECLARE @COUNT INT = (SELECT COUNT(*) FROM #TEMP_TABLE)

SELECT *, @COUNT AS RECORD_COUNT
FROM #TEMP_TABLE

推荐阅读