Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Group a column into different subtotals Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 12:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 4, 2014 11:24 PM
Points: 176, Visits: 482
Hi

I know this might be a pretty obvious thing to many. I need help to split data in a column into two categories with subtotals before grouping them into one grand total.

I have data like this :

EmpLevel NumberOfstaff

Snr Mngmnt 6
Jnr Mngmnt 13
Non Mngmnt 30
Non Pmnt 10
Exclusions 5

I need to display it like this:

Snr Mngmnt 6
Jnr Mngmnt 13
Non Mngmnt 30

Sub Total : 49 ----- Sum of all three on top

Non Pmnt 10
Exclusions 5

Grant Total : 64 ----- Sum of subtotal + the two after it.

Please assist
Post #1514157
Posted Thursday, November 14, 2013 6:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 7,056, Visits: 7,279
WITH cte (MainGroup,EmpLevel,NumberOfstaff) AS (SELECT CASE WHEN EmpLevel LIKE '%Mngmnt' THEN 1 ELSE 2 END,EmpLevel,NumberOfstaff FROM [table]) 
SELECT COALESCE(EmpLevel,CASE WHEN GROUPING(MainGroup) = 0 THEN 'Sub Total' ELSE 'Grand Total' END),SUM(NumberOfstaff) AS [NumberOfstaff]
FROM cte
GROUP BY MainGroup,EmpLevel WITH ROLLUP
HAVING NOT (GROUPING(MainGroup) = 0 AND GROUPING(EmpLevel) = 1 AND MainGroup = 2)




Far away is close at hand in the images of elsewhere.

Anon.

Post #1514254
Posted Thursday, November 14, 2013 6:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 12, 2014 6:22 AM
Points: 531, Visits: 449
I would do this with a CASE statment in the SQL query, something like:
CASE WHEN EmpLevel IN ('Non Pmnt', 'Exclusions') THEN 'Temp' ELSE 'Regular' END AS EmployeeType
and then create a group in your report, using the EmployeeType field.
Post #1514256
Posted Thursday, November 14, 2013 10:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 4, 2014 11:24 PM
Points: 176, Visits: 482
Thanks a mil guys for your help. both your answers helped me a lot both on reporting services and on query side as well. I can even use this method on other reporting tools.
Post #1514580
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse