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?
Best Answer
If I may suggest, why not create a SQL resultset with same data using UNION in the query?
e.g.
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.