How do I create an SSRS Tablix that has blank rows for each day of the year?-CodePudding

I need to start with a blank table that has 365 rows with the first column showing the date (starting with today) and then incrementing with one row for each day. The Tablix will only have 4 columns - date, inbound, outbound and total. Im using a LOOKUP function that will check the date of an inbound order and drop that number in the inbound column of the row with the corresponding date. Same thing with the outbound values but that looks at a different dataset. The last column will just calculate the remaining balance.

CodePudding user response：

There's no functionality in SSRS to generate rows in a table other than using a query.

Here's a query to generate the dates. It would be better if you could add the logic for the LOOKUP in the query rather than in SSRS - the LOOKUP function is somewhat slow.

DECLARE @START_DATE DATE = GETDATE() 
DECLARE @END_DATE  DATE  = DATEADD(YEAR, 1, GETDATE() -1)

;WITH GETDATES AS  
    (  
        SELECT @START_DATE AS THEDATE
        UNION ALL  
        SELECT DATEADD(DAY,1, THEDATE) FROM GETDATES  
        WHERE THEDATE < @END_DATE  
)

SELECT * FROM GETDATES 
OPTION (maxrecursion 0)