Group and subtotal columns in Reporting Services 2005

Question

I have a report (RS2005, against a MSSS2005 instance) which I have inherited. It shows a basic table of data: a handful of key fields which are used to group rows together, a few basic numeric fields, then a number of dated ('bucketed') fields (e.g. 1 month away, 2 months, 6 months, a year, 2 years, etc.)

The user would like to group together these dated fields in aggregated groups and be able to collapse or expand the columns as you can the rows. So we'd be able to show the next year's values' subtotal or expand it to break it out by month. Hiding the invidual months if the subtotal is shown.

This is basic pivot table behaviour (and can be done with the Group/Subtotal feature on Excel - that's the closest analogous behaviour I could use to describe the requirements).

While grouping by rows seems trivial in RptgSvcs, grouping columns and collapsing a group into a single subtotal and blowing it out again, seems hard to impossible.

Unless someone knows better?

Answer 1

On the right hand side of the table control on your report (RDL file), you should see icons representing the detail and grouping information for the table.

Here you can see whether or not a grouping has a header/footer and how it is calculated e.g. =SUM(fields!mySalesValue)

You can also set things like whether it is collapsed and so on

Answer 2

You should be able to do this by selecting the columns that you would like to hide and set their visibliity to be hidden.

Then set the toggle item on the hidden columns to be the textbox that shows the subtotal.

This textbox will then have the [+] symbol on it, and clicking will show/hide the your month-by-month breakout(the hidden columns).