59914

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 <strong>columns</strong> 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?

Answer1:

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).

Answer2:

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

Recommend

  • Summing and ordering at once
  • How do I generate a series of hourly averages in MySQL?
  • How is it possible to manually execute the “OnCalcFields” event?
  • Why does cpu load not change more than a few hundredths?
  • regex, multiline extract in R
  • add new field to form with rvest
  • How to issue a command that produces infinite output and return immediately
  • dc.js: Reducing rows in data table
  • Change Divider Color Android DatePicker Dialog
  • Extract data between rows r
  • Google Bigquery Command Line Return Limit
  • Two columns in subquery in where clause
  • Regex for incomplete lines within known start and end strings
  • CSS: How to fix overlapping divs
  • SQL: Getting the physical size of a subset of a table
  • Accessing Rows In A LINQ Result Without A Foreach Loop?
  • iText RadioGroup/RadioButtons across multiple PdfPCells
  • Primefaces :radioButton inside a ui:repeat
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Invalid Date on validation Date of js
  • Breaking out column by groups in Pandas
  • MySQL Order by column = x, column asc?
  • Unable to get column index with table.getColumn method using custom table Model
  • Should I or shouldn't I use the CachingConnectionFactory with hornetq 2.4.1
  • Does it make sense to call System.gc() and Thread.sleep() when working on Bitmaps?
  • Can Jackson SerializationFeature be overridden per field or class?
  • TFS: Get latest causes slow project reloading
  • Sending data from AppleScript to FileMaker records
  • Updated Ionic CLI but shows previous version (Windows)
  • Convert array of 8 bytes to signed long in C++
  • KeystoneJS: Relationships in Admin UI not updating
  • AngularJs get employee from factory
  • Load html files in TinyMce
  • Understanding cpu registers
  • Authorize attributes not working in MVC 4
  • Busy indicator not showing up in wpf window [duplicate]
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF