Help with SSRS Matrix

Question

Help with SSRS Matrix

mattsynco

SSC Veteran

Points: 225
More actions
May 19, 2015 at 11:39 pm

#304120

I have this matrix which displays the problem type for each month of the given year.
As you can see March and April of 2014 do not show up. How do I get it so that it doesn't suppress those months because no data is found?
I need the months missing to show even if they have no data returned? Anyone know how I can accomplish this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

Nothing to it. you need a calendar table for all the dates in your range. (Search around for articles, they're all over the place on here).

Once you have a calendar table, you can outer join to it... for example:

SELECT c.CalendarDate, o.OrderID

FROM Calendar c LEFT JOIN Orders o ON c.CalendarDate = o.OrderDate;

that will return ALL the values in CalendarDate, whether it has corresponding Orders or not. Then you can just filter the Calendar table for whatever dates you want. You'll likely have to cast the nulls to zeroes, but that's trivial.

mattsynco SSC Veteran Points: 225 More actions · Answer 2

Thanks for the quick reply. I did create a Table for the calender months but I was having some issues. If you could help me further I would greatly appreciate it.

This is my query

SELECT ProblemType, IssueMonth, IssueYear, SUM(IssueCount) AS MonthlyIssueCount, CInt(IssueMonthNumber) AS IssueMonthNumber

FROM qryIssueGroupedByYearAndMonth

GROUP BY ProblemType, IssueMonth, IssueYear, IssueMonthNumber

The table I created for the months is called tblMonths could you lay it out for me?

pietlinden SSC Guru Points: 63428 More actions · Answer 3

Not like this...

SELECT ProblemType, IssueMonth, IssueYear, SUM(IssueCount) AS MonthlyIssueCount, CInt(IssueMonthNumber) AS IssueMonthNumber

FROM qryIssueGroupedByYearAndMonth

GROUP BY ProblemType, IssueMonth, IssueYear, IssueMonthNumber

like this:

SELECT ProblemType, IssueMonth, IssueYear, IssueCount, CInt(IssueMonthNumber) AS IssueMonthNumber

FROM Calendar c LEFT JOIN Problem p ON c.CalendarDate = p.Problem

All the grouping can will be done in SSRS inside the Matrix. The only thing you're really doing in the query is outer joining the Calendar table to your Issues table, and showing the columns you want to appear in your matrix. then the matrix will do the counting for you. (It requires some kind of aggregate function in the intersection of rows and columns anyway, so might as well put COUNT([somecolumn]) there.

And if you want to filter *every* time by something, you'd add a parameter to your stored procedure, and it would create the Report parameter for you.