我有一个SSRS报告,该报告总结了指定日期范围内的数据集。
我想使用report参数为查询中的聚合函数提供日期范围。这有可能吗?
我有使用变量的聚合函数,但是我想更改此设置,以便用户可以在运行报表之前选择日期范围。
以下是原始的工作汇总函数(在整个查询的上下文中-我删除了查询的不相关部分以免造成混淆)。
请注意,@ MonthNum和@ReportYear变量用作COUNT()函数的条件。我想更改这些变量以报告参数值,但是无法解决。
DECLARE @MonthNum int;
DECLARE @ReportYear int;
SET @MonthNum = CASE WHEN (MONTH(GETDATE())=1) THEN 12 ELSE MONTH(GETDATE()) - 1 END;
SET @ReportYear = CASE WHEN (MONTH(GETDATE())=1) THEN
YEAR(GETDATE())-1 ELSE YEAR(GETDATE()) END;
SELECT
COUNT(CASE WHEN (MONTH(tblInvoices.fldDeliveredDate) = @MonthNum AND
(YEAR(tblInvoices.fldDeliveredDate)) = @ReportYear) THEN tblSessions.fldStudioNo END)
AS MonthVolume
FROM tblInvoices INNER JOIN
tblSessions ON tblInvoices.fldSesID = tblSessions.fldSesID INNER JOIN
tblSessionCustom ON tblSessions.fldSesID = tblSessionCustom.fldSessionCustomID
INNER JOIN
tblCompInfo ON tblSessions.fldStudioNo = tblCompInfo.fldStudioNo
GROUP BY tblSessions.fldStudioNo, tblCompInfo.fldCompName
HAVING (NOT (tblSessions.fldStudioNo LIKE '999'))
在此先感谢您提供的任何帮助或指导。非常感激。
戴夫
我相信您将需要以下类似的东西,您需要在SSRS中创建参数@StartDate和@EndDate,以便它们输入到查询中:
DECLARE @MonthNum int;
DECLARE @ReportYear int,
@StartDate date,
@EndDate date
SET @MonthNum = CASE WHEN (MONTH(GETDATE())=1) THEN 12 ELSE MONTH(GETDATE()) - 1 END;
SET @ReportYear = CASE WHEN (MONTH(GETDATE())=1) THEN
YEAR(GETDATE())-1 ELSE YEAR(GETDATE()) END;
SELECT
COUNT(CASE WHEN (MONTH(tblInvoices.fldDeliveredDate) = @MonthNum AND
(YEAR(tblInvoices.fldDeliveredDate)) = @ReportYear) THEN tblSessions.fldStudioNo END)
AS MonthVolume
FROM tblInvoices INNER JOIN
tblSessions ON tblInvoices.fldSesID = tblSessions.fldSesID INNER JOIN
tblSessionCustom ON tblSessions.fldSesID = tblSessionCustom.fldSessionCustomID
INNER JOIN
tblCompInfo ON tblSessions.fldStudioNo = tblCompInfo.fldStudioNo
Where tblInvoices.fldDeliveredDate between @StartDate and @EndDate
GROUP BY tblSessions.fldStudioNo, tblCompInfo.fldCompName
HAVING (NOT (tblSessions.fldStudioNo LIKE '999'))
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句