0

I am writing a report in which there are groups in 3 columns, and a subtotal figured at the end of each group. When I create the grouping, a column is created out to the left of the report. I am normally in the habit of renaming the grouping column to the field that is being grouped and deleting the column in the report. However, the client wants the field inside the report and not on the outside (in other words he wants the columns to be specifically where he requested them. Is there a way to accomplish this?

Here is a representative example of what I am trying to achieve.

BusinessSegment Entity CostCenter  ShipDate InvoiceNumber CompanyCode Estimated Amount

I want to group by CostCenter and CompanyCode, and provide subtotals at the end of each group for Estimated Amount.

Matt Busche
  • 14,216
  • 5
  • 36
  • 61
Jerry C
  • 181
  • 1
  • 5
  • 15
  • please post sample data and what you've tried so far – Matt Busche Mar 05 '13 at 21:52
  • Sample data for BusinessSegment would be: Baby & Parenting, Commercial Products , Home Solutions; for entity would be: home, commercial, industrial; for costcenter: 655, 9771, 255: InvoiceNumber: 16 digit number from invoice; CompanyCode: 1445, 5223, 4478; Estimated Amount: $43.36, $556.35, $129.65. I have added the groups and then the totals by group, and then tried to delete the columns created by the grouping but when I did I lost the group also. Hope this helps. – Jerry C Mar 05 '13 at 22:05

1 Answers1

1

You can't group inline except with an aggregate (min, max, sum, count) etc. Otherwise you are going to get the first result something is grouped on and have inaccuracies.

Can you just collapse the values of the grouping instead?

In SSRS generally with reports you have a 'details' grouping whether it is a matrix or a table report. You mentioned 'columns' so it sounds like you have header's of A, B, and C and they want to see a totals. You can generally add a grouping but then have it collapse or expand on demand. That way you present an end user with the data but they have the option of expanding it to see more if they want.

Since you did not specify if you have a matrix I will assume you have one. When you have multiple categories you can hit the grouping of a row or column and if you are having a matrix it is probably using a [SUM(field)]. The grouping of columns are showing all of them, however you can specify they are collapsed or expanded at runtime. Right Click the grouing and you get 'Group Properties'. Select the 'Visibility' pane on the left. Choose 'Hide' radio button and the default for your report will 'collapse' the values to be an aggregate instead of the expanded details of each column. If you want an option to have the user expand or collapse select the checkbox 'Display can be toggled by this report item:'. Choose a textbox or other object outside the collected scope and a user can be presented with the option to collapse or expand on default.

djangojazz
  • 14,131
  • 10
  • 56
  • 94