Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Group a column into different subtotals Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, November 14, 2013 12:33 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, April 11, 2014 2:02 AM Points: 173, Visits: 469
 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 NumberOfstaffSnr Mngmnt 6Jnr Mngmnt 13Non Mngmnt 30Non Pmnt 10Exclusions 5I need to display it like this:Snr Mngmnt 6Jnr Mngmnt 13Non Mngmnt 30Sub Total : 49 ----- Sum of all three on topNon Pmnt 10Exclusions 5Grant Total : 64 ----- Sum of subtotal + the two after it.Please assist
Post #1514157
 Posted Thursday, November 14, 2013 6:54 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 9:49 AM Points: 6,798, Visits: 6,272
 `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 Group: General Forum Members Last Login: Today @ 6:23 AM Points: 511, Visits: 417
 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 EmployeeTypeand then create a group in your report, using the EmployeeType field.
Post #1514256
 Posted Thursday, November 14, 2013 10:15 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, April 11, 2014 2:02 AM Points: 173, Visits: 469
 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

 Permissions