SSRS 日期部分季度与所有年度季度

Question

我有一个包含销售记录的表格。此表有一个包含关闭日期的日期时间列。

我正在使用 SSRS 来显示所有季度的销售数据。

为了苏马化宿舍，我的表达是：

="Q" & DatePart(DateInterval.Quarter,
Fields!ActualCloseDate.Value,FirstDayOfWeek.System,
FirstWeekOfYear.System)

但是我只在我的日期时间列中得到季度，我怎样才能获得 0 数量或没有数据的季度？

谢谢！！

Answer 1

对于这种类型的事情，我通常使用日历表，其中包含所有日期的列表以及年，季度，日期，星期几的列。在您的情况下，您可以只有一个基表，其中包含支持数据所需的所有年度/季度组合。然后，查询从此表开始，并联接您的销售数据。 当销售数据为空时，您可以替换为 0。

Answer 2

Jason 完全正确，但我会为您节省一些时间，并将我过去自己成功使用的日历表脚本链接到快速表。

为了死链接的缘故，这是您正在寻找的季度的相关部分，尽管我建议您阅读我链接的页面，因为它是一个很好的教程。

DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 30;
-- prevent set or regional settings from interfering with 
-- interpretation of dates / literals
SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;
DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);
-- this is just a holding table for intermediate calculations:
CREATE TABLE #dim
(
[date]       DATE PRIMARY KEY, 
[day]        AS DATEPART(DAY,      [date]),
[month]      AS DATEPART(MONTH,    [date]),
FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
[MonthName]  AS DATENAME(MONTH,    [date]),
[week]       AS DATEPART(WEEK,     [date]),
[ISOweek]    AS DATEPART(ISO_WEEK, [date]),
[DayOfWeek]  AS DATEPART(WEEKDAY,  [date]),
[quarter]    AS DATEPART(QUARTER,  [date]),
[year]       AS DATEPART(YEAR,     [date]),
FirstOfYear  AS CONVERT(DATE, DATEADD(YEAR,  DATEDIFF(YEAR,  0, [date]), 0)),
Style112     AS CONVERT(CHAR(8),   [date], 112),
Style101     AS CONVERT(CHAR(10),  [date], 101)
);
-- use the catalog views to generate as many rows as we need
INSERT #dim([date]) 
SELECT d
FROM
(
SELECT d = DATEADD(DAY, rn - 1, @StartDate)
FROM 
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) 
rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
-- on my system this would support > 5 million days
ORDER BY s1.[object_id]
) AS x
) AS y;

请注意，这是上面的一个临时表，您可能希望将其具体化为架构中某个位置的实际表。

获得此生成的表后，您可以通过从日历表中选择一组不同的季度并左外部联接到常规查询来开始上一个查询。 这就是你如何获得那些"丢失"的日期。 最后，请注意，我使用合并来显示美元度量的零数字，而不是空值：

SELECT distinct
'Q' + CAST(quarter as varchar(1)) as Quarter,
COALESCE(total, 0) as USD
FROM #dim as d
LEFT OUTER JOIN YourTable yt
ON d.quarter = yt.quarter