Vytvoření datumové řady pro reporty s pohyblivou časovou osou

Pokud potřebujeme vytvořit report, který nám bude ukazovat například počet objednávek za posledních třicet dnů zpět, potřebujeme získat datový set, který bude obsahovat řádek pro každý den a k němu odpovídající počet objednávek. V následujícím tutoriálu si ukážeme jednoduchou implementaci pomocí rekurzivní definice tabulky  a také příklad použití výsledných dat v grafu.

Nejprve si vytvoříme testovací data. Tabulku dbo.Orders naplníme několika objednávkami, které mají OrderDate v uplynulých třiceti dnech:

CREATE TABLE [dbo].[Orders]
(
    [OrderId] INT IDENTITY(1,1) PRIMARY KEY,
    [OrderDate] DATE NOT NULL,
	[OrderDesc] NVARCHAR(100)
)
GO

INSERT INTO [dbo].[Orders]
        ( [OrderDate], [OrderDesc] )
	SELECT DATEADD(DAY, -25, GETDATE()), 'Order1' UNION ALL
	SELECT DATEADD(DAY, -25, GETDATE()), 'Order2' UNION ALL
	SELECT DATEADD(DAY, -23, GETDATE()), 'Order3' UNION ALL
	SELECT DATEADD(DAY, -23, GETDATE()), 'Order4' UNION ALL
	SELECT DATEADD(DAY, -23, GETDATE()), 'Order5' UNION ALL
	SELECT DATEADD(DAY, -10, GETDATE()), 'Order6' UNION ALL
	SELECT DATEADD(DAY, -10, GETDATE()), 'Order7' UNION ALL
	SELECT DATEADD(DAY, -5, GETDATE()), 'Order8' UNION ALL
	SELECT DATEADD(DAY, -5, GETDATE()), 'Order9' UNION ALL
	SELECT DATEADD(DAY, -2, GETDATE()), 'Order10'
GO

Agregovaná data objednávek podle OrderDate vypadají takto:

SELECT OrderDate, COUNT(*) NrOfOrders
FROM [dbo].[Orders]
GROUP BY [OrderDate]
GO

Pro datový set použitelný v grafu však potřebujeme vygenerovat i dny, ve kterých nejsou žádné objednávky. Toho dosáhneme následujícím postupem:

;WITH Dates AS
( 
	SELECT CAST(DATEADD(DAY, -30, GETDATE()) AS DATE) AS [DT] 
	UNION ALL
	SELECT DATEADD(DAY, 1, [DT]) AS [DT]
	FROM [Dates] 
	WHERE DT <= CAST(GETDATE() AS DATE)
) 
SELECT 
	[DT], sc.[OrdersCount]
FROM Dates d
	OUTER APPLY (
			SELECT COUNT(*) OrdersCount
			FROM [dbo].[Orders]
			WHERE [OrderDate] = d.[DT]
	) sc
OPTION (MAXRECURSION 0)
GO

Nejprve vytvoříme definici tabulky Dates a jako kotvu určíme datum, který je o 30 dnů menší než aktuální datum, získané pomocí funkce GETDATE(). Poté ke kotvě přidáme rekurzivní člen, který bude tabulku Dates procházet tak dlouho, dokud v ní nebude sloupec DT obsahovat datum, který se rovná datu dnešnímu. Z připravené Dates tabulky provedeme SELECT všech řádků a pomocí OUTER APPLY operátoru dohledáme počet objednávek pro každý datum. Získáváme tak datový set, který ve sloupci DT obsahuje datum pro každý z minulých třiceti dnů a ve sloupci OrderCount počet objednávek pro každý den, případně 0, pokud pro daný den žádné objednávky nejsou.

V Reporting Services datový set přiřadíme jako datový zdroj grafu následujícím způsobem:

A výsledný graf může vypadat například takto (vertikální axis je třeba naformátovat pro “Days” formát vstupních dat):