我正在 MSvs 中开发一个 SSRS 报告,它允许 2 种不同的日期选择。
一种选择是使用StartDate
和EndDate
参数(选择允许NULL
(选择自定义日期范围。
另一种选择是从下拉列表中列出的一组预定义日期中进行选择,也可以通过选择"选择自定义"来NULL
。
我有 2 个数据集,这两个数据集都是 SQL 服务器中的存储过程。一个是我的主要数据集,另一个是CalendarRanges
,我在其中定义了预定义的日期范围,如下所示:
- 本周
- 上一周
- 当月
- 习惯
我已经完全剥离了我的代码以提供一个示例。
主要数据集存储过程:
ALTER PROCEDURE MainDataset
(
@StartDate DATETIME,
@EndDate DATETIME
)
WITH RECOMPILE
AS
BEGIN
DECLARE @StartDateKey INT, @EndDateKey INT
SELECT
@StartDateKey = MIN(DateKey),
@StartDate = MIN(CalendarDate),
@EndDateKey = MAX(DateKey),
@EndDate = MAX(CalendarDate)
FROM
Dim.Calendar
WHERE
CalendarDate >= @StartDate
AND CalendarDate <= @EndDate
;WITH Sales AS
(
SELECT
Sales.CurrencyKey,
OrderId,
FROM
Fact.Sales
WHERE
Sales.OrderDateKey = @StartDateKey
),
Payments AS
(
SELECT
Payments.PaymentCurrencyKey,
PaymentID
FROM
Payments
WHERE
AND Payment.DateKey >= @StartDateKey
AND Payment.DateKey <= @EndDateKey
)
SELECT
Sales.*,
Payments.*,
@StartDate AS 'StartDate',
@EndDate AS 'EndDate'
FROM
Sales
LEFT JOIN Payments
ON Payments.PaymentCurrencyKey = Sales.CurrencyKey
开始日期和结束日期与 SSRS 中的@StartDate
和@EndDate
参数相关联。
数据集 2:
ALTER PROCEDURE CalendarRanges
AS
SELECT
1 AS [Order],
Value,
Label
FROM
(SELECT
CurrentWeekOfYear AS Value,
'CurrentWeek' AS Label
FROM Calendar
WHERE CalendarDate = CONVERT(varchar, getdate(), 23)) CurrentWeek
UNION
-- Previous Week
SELECT
2 AS [Order],
Value,
Label
FROM
(SELECT
DISTINCT CurrentWeekOfYear -1 AS Value,
'PreviousCurrentWeek' AS Label
FROM Calendar
WHERE CalendarDate = CONVERT(varchar, getdate(), 23)) PreviousWeek
UNION
-- Current Month
SELECT
4 AS [Order],
Value,
Label
FROM
(SELECT
DISTINCT CurrentMonth AS Value,
'CurrentMonth' AS Label
FROM Calendar
WHERE CurrentMonth = MONTH(GETDATE())) CurrentMonth
UNION
-- Custom
SELECT
5 AS [Order],
value,
Label
FROM (
SELECT
NULL AS Value,
'Select Custom' AS Label
) Custom
如果我从下拉列表中选择"选择自定义"选项,然后选择自定义的开始和结束日期,则报告将按预期运行。
但是,如果我保留自定义开始日期和结束日期NULL
并从下拉列表中选择一个预定义的日期,则报告不会返回任何数据。我已经通过在选择自定义时预定义显示数据的日期进行了测试,因此我知道数据在那里。
我想操纵报告,以便如果自定义开始和结束日期参数NULL
,则改用下拉参数,但我正在努力了解如何实现这一点?
您可以在程序中更改一些逻辑...它可能会帮助你...
ALTER PROCEDURE MainDataset
(
@StartDate VARCHAR(20)=NULL,
@EndDate VARCHAR(20)=NULL
)
WITH RECOMPILE
AS
BEGIN
DECLARE @StartDateKey INT, @EndDateKey INT
SELECT
@StartDateKey = MIN(DateKey),
--@StartDate = MIN(CalendarDate),
@EndDateKey = MAX(DateKey)
-- @EndDate = MAX(CalendarDate)
FROM
Dim.Calendar
WHERE
convert(DATETIME,CalendarDate)
BETWEEN Convert(DATETIME,CASE WHEN isnull(@StartDate,'')='' THEN CalendarDate
ELSE isnull(@StartDate,'') END)
AND Convert(DATETIME, CASE WHEN isnull(@EndDate,'')='' THEN CalendarDate
ELSE isnull(@EndDate,'') END)
/* this Line is commented....
CalendarDate >= @StartDate
AND CalendarDate <= @EndDate
*/
;WITH Sales AS
(
SELECT
Sales.CurrencyKey,
OrderId,
FROM
Fact.Sales
WHERE
Sales.OrderDateKey = @StartDateKey
),
Payments AS
(
SELECT
Payments.PaymentCurrencyKey,
PaymentID
FROM
Payments
WHERE
AND Payment.DateKey >= @StartDateKey
AND Payment.DateKey <= @EndDateKey
)
SELECT
Sales.*,
Payments.*,
@StartDate AS 'StartDate',
@EndDate AS 'EndDate'
FROM
Sales
LEFT JOIN Payments
ON Payments.PaymentCurrencyKey = Sales.CurrencyKey
在您的第一个程序中...不要分配默认值。例如,如果 您只输入@FromDate然后它才会自动选择最后一个 你桌上的日期....
前任。您的表包含从 10 到 30 ...然后如果你输入 15 然后..它将自动选择 15 到 30 个日期范围数据...和 反之亦然 对于@ToDate也...
注意:--您可以尝试所有查询...空部分也是自定义日期范围内的句柄.....