Displaying Aggregates Across Overlapping Ranges in a Reporting Services Matrix

Question

I'm having a problem displaying the values for a range of dates in a matrix row. I would like to have a matrix display the following data:

            Trial Existing  Total
Yesterday      3    1       4
This Week      5    3       8
Last Week     18    5      23
Month to Date 26    9      35
Last Month    32   20      52

I thought the solution would be to create a calculated field with the text values for the cancel range ('Yesterday', 'This Week', etc.). The problem is that those date ranges overlap and the field can only hold one text value.

Is there a way to create a matrix table like this in Reporting Services?

Answer 1

If I may suggest, why not create a SQL resultset with same data using UNION in the query?

e.g.

SELECT 'Yesterday' AS When, 
SUM(CASE WHEN Status = 'Trial' Then 1 Else 0 End) AS Trial,
SUM(CASE WHEN Status = 'Other' Then 1 Else 0 End) AS Other
FROM myTable
WHERE TheDate >= givenDate AND TheDate < (givenDate + 1)
GROUP BY When
UNION
SELECT 'This Week' AS When, 
SUM(CASE WHEN Status = 'Trial' Then 1 Else 0 End) AS Trial,
SUM(CASE WHEN Status = 'Other' Then 1 Else 0 End) AS Other
FROM myTable
WHERE TheDate >= (givenDate - 7) AND TheDate < (givenDate + 1)
GROUP BY When

Note: I have written this query to give you an example of how it can be done in SQL. So as to not rely on Reporting Services to do the work.

And you will have to do some juggling to get the dates of "This week" using DATE related functions in tsql.

The Total field can be a calculated field in the report.