SSRS Matrix Bespoke Headers (Still from datasource!!)

Question

I have to create a matrix in SSRS to detail the number uses leaving an organisation.

The columns will all represent spaces of time spanning 1 week and the rows will all represent departements in the organisation. The detail portion will be a count of people who have left that area in that week.

I have a leaving date field in the DB but nothing that flags the specific intevals I have been told to use. That means that as the matrix is, it counts each of users that have left a specific department however the date range columns is 1 day, not 1 week. Is there a way to force the column headers to respect the week intervals I want given that they are currently coming from the dataset and are not hard coded?

Is it possible to revert to a tablix, hard code the interval headers for the columns and then apple some sort of WHERE to the data on a column by column or cell by cell basis but still keep the department grouping? — Powell21
Firstly try to manage your data in sql itself by using Group By with date and making each group as one week period. That way you can manage to get all data in your required format — Mahesh
@CoderofCode - How though can I do that given that the date intervals are arbitrary and are not stored directly nor as flags anywhere at all? — Powell21
Can you post some random data of yours with table structure ? — Mahesh

Mahesh Mahesh · Accepted Answer · 2015-01-14T14:05:32

Firstly try to manage your data in sql itself by using Group By with date and making each group as one week period. That way you can manage to get all data in your required format

I don't know what is your columns so I am just showing a way to get the week groups from table and get the count of the people

 SELECT  DATEPART(wk, datevaluecolumn)     weekno
    ,   SUM(peopleleavingcolumn)           totalvalue
 FROM        yourTable
 GROUP BY    DATEPART(wk, datevalue)

SSRS Matrix Bespoke Headers (Still from datasource!!)

1 Answers