CODEWITHSUNDEEP
sql-serverreporting-services
jpavelw
jpavelw

Reputation: 37

How to add columns with no matching in reporting services (SSRS)

I am trying to create a report and the view considers monthly ranges. The report has different views (daily, weekly, monthly). I have a procedure that brings the records but I am having issues trying to build the report with dates in which I have no records.

For instance, if I run the report with the daily view on Dec 19th, I will only see columns until the 19th. I want to see columns until the 31th (end of month) even if there are no records.

Current view of the days in the report

I attached a pic of how it currently looks. I was also trying to do a FULL JOIN with a variable table that I have that contains all the day of the month. I also use a similar approach with the weekly view but with the week range. It still doesn't work with this view, and I'd like it to work.

Daily variable table Weekely table

With the weekly report it happens something similar: if I run the report today, the last week of the month won't show on the report (because there are no records) and I'd like it to be shown.

I hope it's clear enough.

Upvotes: 1

Views: 67

Answers (1)

ViKiNG
ViKiNG

Reputation: 1334

As Sean suggested in his comment, you can use a calendar type table and then can use left join form that table to your data table to display all rows including one with no data etc.

For calendar table, you can also use following code with your choice of date range:

DECLARE @Date1 DATE= '20171201';
DECLARE @Date2 DATE= '20171215';

SELECT 
    DATEADD(DAY, number, @Date1) AS [Date],
    DATEPART(DW, DATEADD(DAY, number, @Date1)) DayOfWeek,
    DATEPART(DY, DATEADD(DAY, number, @Date1)) DayOfYear,
    DATEPART(day, DATEDIFF(day, 0, DATEADD(DAY, number, @Date1)) / 7 * 7) / 7 + 1 AS WeekOfMonth,
    DATEPART(WEEK, DATEADD(DAY, number, @Date1)) WeekOfYear
FROM master..spt_values
WHERE type = 'P'
      AND DATEADD(DAY, number, @Date1) <= @Date2;

Resulting:

enter image description here

Hope this help?

Good luck.

Upvotes: 1

Related Questions