Multiple Totals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Multiple Totals

Author

Topic

Trybbe
Starting Member

27 Posts

Posted - 2008-08-20 : 07:06:53

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 09:31:29

you can do this in matrix. what you want is to use expression of form
SUM(IIF (condition,truevalue,falsevalue))

can you provide a sample of your reqd report and fields involved in which case i will try to give full expression.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources