August 20, 2008 at 5:03 am
Hi! How do I Create a report that has multiple aggregates using a matrix data region?
In my table I have a column called HC Cat1 which contains employee status. eg. Permanent, Temp, Contract etc. Now in my current report I have Clusters on my column and HC cat1 on my row then I do a count of HC Cat1. It gives me a lovely breakdown of how many permanent/temp employees are in each cluster.
Now my client wants to change the view and group them. She want's to see:
Total headcount for payroll --- Permanent staff + temps + contractors + Learneships
Total Headcount Non - Payroll
and so foth untill we get to Grand total
Is there a way I can do that in SSRS? if not how do I code it in SQL. I've attached a view of image of how the report should look like
Here's the example of my code :
SELECT Positions.Position, Positions.BranchID, Positions.PosID, Positions.Period, headcountdec.headcount, headcountdec.StaffNo,
Case When [Grade Grouping].[HC CAT1] IS NULL Then 'Vacancy'
When [Grade Grouping].[HC CAT1] like 'Payroll%' Then 'Payroll Temps & Contractors'
When [Grade Grouping].[HC CAT1] IN ('Pensioner', 'International Secondee', 'Exclusions', 'Flexi Temps', 'International', 'External', 'Unknown') Then 'Other'
Else [Grade Grouping].[HC CAT1] End AS [HC CAT1]
FROM Positions LEFT OUTER JOIN headcountdec
LEFT OUTER JOIN [Grade Grouping] ON headcountdec.GradeCode = [Grade Grouping].GradeID ON Positions.Period = headcountdec.Period
AND Positions.PosID = headcountdec.PosID
WHERE (Positions.Period = 200807) AND (Positions.Deleted = 0)
AND (headcountdec.headcount = 'headcount') AND (headcountdec.Cluster = 'Retail')
ORDER BY [HC CAT1], Positions.Period
January 12, 2011 at 1:31 pm
Right-click the bottom right cell of the Matrix in Layout mode and add column.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply