I am new to SQL Server and SSRS…and am trying to get a matrix report formatted correctly.
My query is:
select e.name, ea.element,
case when ea.element = 'projected_status' then 'Projected Status'
when ea.element = 'projected_spstatus' then 'Projected Sp Status'
when ea.element = 'day1_status' then 'Day 1 Status'
when ea.element = 'day1_spstatus' then 'Day 1 Sp Status'
end as FIELD,
el.value
from employeelocation el
join employee e on e.employeeid = ea.employeeid
join employeeattribute ea on ea.attributeid = el.attributeid
where ea.attributeid in (888,899);
and I am getting this information returned:
+--------------+-----------+-----------+-----+
| Barry Green | Projected | Status | 484 |
| Barry Green | Projected | Sp Status | 29 |
| Barry Green | Day 1 | Status | 589 |
| Barry Green | Day 1 | Sp Status | 2 |
| George Cart | Projected | Status | 324 |
| George Cart | Projected | Sp Status | 12 |
| George Cart | Day 1 | Status | 299 |
| George Cart | Day 1 | Sp Status | 0 |
+--------------+-----------+-----------+-----+
What I want to create is an output like this:
I can create the basic matrix report in SSRS, but I don't know how to format the groupings. I don't know if this is something that I need to do in my query or if there's a way to achieve this through format options in SSRS.
I just need to be pointed in the right direction. Any assistance is greatly appreciated.
Thank you!
Best Answer
I have put your results into one table and then adapted your query. I've taken out the joins and used GROUP BY and PIVOT you will need to put the joins back in to make it work for you.
SQL Fiddle Demo