How can I create a sub-group in SSRS which sums the values of specific rows from other groups?

Question

I have an SSRS report in Microsoft Visual Studio 2008 driven by a large SQL query. There is one main group which includes two sections and each of those sections contains a few rows of data. I want to create a third section (Z below) within that group which sums the values of specific rows from a different section of the same group. Example:

Answer 1

Personally I would do this in your dataset, something like..

SELECT Category, Location, Results INTO #t FROM ([existing query here]) q

SELECT * FROM #t
UNION ALL
SELECT 'Z', '1 & 3', SUM(Results) FROM #t WHERE Category='X' and Location IN (1,3)
UNION ALL
SELECT 'Z', '2 & 4', SUM(Results) FROM #t WHERE Category='Y' and Location IN (2,4)

This means the dataset will have all your data and will not require any more work in the report.

You can do a similar things in the report directly by adding more rows outside the main group and then setting the expressions to sum the relevant bits of the report but going via sql is much easier to work with and debug (IMO)