SQL Server Analysis Services: Add Column to provide Sub-Total of row's group

Question

Hi There,

Please find below my current SQL Query. What I'm trying to accomplish is add a column to the resultset that for each row provides the sub-total of the group that it belongs to. Can anyone provide me with an elegant solution to do this?

SELECT     costs.cost_des, SUM(costs_periods.actual) AS actual, SUM(costs.commitment) AS commitment, SUM(costs_periods.curr_bud) AS curr_bud, 
                      costs.exp_budget_limit, v_costs_by_group.cost_id, cost_groups.description AS groupDes, cost_groups.cost_group_id, 
                      cost_groups.parent_group_id
FROM         costs INNER JOIN
                      costs_periods ON costs.cost_id = costs_periods.cost_id INNER JOIN
                      v_costs_by_group ON costs.cost_id = v_costs_by_group.cost_id INNER JOIN
                      cost_groups ON v_costs_by_group.cost_group_id = cost_groups.cost_group_id
WHERE     (costs.year_id =
                          (SELECT     year_id
                            FROM          financial_years
                            WHERE      (year_des = @year))) AND (costs_periods.period <= @currentPeriod) AND (costs_periods.period <> 0) AND (costs_periods.period <> 13)
GROUP BY costs.cost_des, costs.exp_budget_limit, v_costs_by_group.cost_id, cost_groups.cost_group_id, cost_groups.description, 
                      cost_groups.parent_group_id
HAVING      (cost_groups.cost_group_id = @CostGroupID5) OR
                      (cost_groups.parent_group_id = @CostGroupID5)
ORDER BY groupDes, costs.cost_des

Many thanks

Andy

Answer 1

1 You should do this in the Reporting tool

2 Read about ROLLUP and CUBE operator in SQL Server help file

Answer 2

You need to put the WITH ROLLUP right after the GROUP BY:

GROUP BY 
    costs.cost_des, costs.exp_budget_limit, v_costs_by_group.cost_id, 
    cost_groups.cost_group_id, cost_groups.description, cost_groups.parent_group_id
    WITH ROLLUP