SSRS: How to add Calendar Dates in Columns (tsql)

Question

I've got the needed query to extract what data I need, but I'm having issues on how to manipulate them in Visual Studio 2008 and/or Report Builder 3.0.

I want to add the calendar days of the month as a column horizontally in SSRS and I've tried many different ways but still unable to accomplish what I need.

Here's basically what I'm trying to accomplish. Any help would be greatly appreciated:

Add a matrix and create a Column group with the following expression. `=Day(Fields!Date.Value)` — alejandro zuleta, Jun 24 '16 at 17:43
That doesn't work because Date.value isn't part of this query. Even if I included the task completion date, that won't show calendar dates of 1-31 — user3571153, Jun 24 '16 at 17:58

score 1 · Accepted Answer · answered Jul 04 '16 at 15:29

Modify your original dataset query to include an right outer join to a dimension table, linking your completion date to a date field within your dimension table, for example your sql query should include something like:

FROM tbl_referrals RIGHT OUTER JOIN Dim ON tbl_referrals.AdmitDate = Dim.Date

and then use dim.date as column group.

If your dimension table also includes years,months these can further filtered accordingly to the days of the months you require.

SSRS: How to add Calendar Dates in Columns (tsql)

1 Answers1