SSRS group total by grand total

1
votes

I have a report which is grouped by location and charge_type with detail information. I would like to show the charge_type totals below the location information and right above the grand total.

Current report, including totals after the city and type sections:

    +-------+------+-------------+
    | City  |Type  |  Detail     |
    +-------+------+-------------+
    |  A    |  a   |   1         | 
    |       |  b   |   2         | 
    |       |  c   |   3         | 
    |       |  d   |   4         | 
    |       |Total |   10        | 
    |  B    |  a   |   7         | 
    |       |  b   |   8         | 
    |       |  c   |   9         | 
    |       |  d   |   10        |  
    |       |Total |   34        | 
    |Grand  |      |             | 
    |Total  |      |   44        | 
    +-------+------+-------------+

What I would like:

    +-------+------+-------------+
    | City  |Type  |  Detail     |
    +-------+------+-------------+
    |  A    |  a   |   1         | 
    |       |  b   |   2         | 
    |       |  c   |   3         | 
    |       |  d   |   4         | 
    |  B    |  a   |   7         | 
    |       |  b   |   8         | 
    |       |  c   |   9         | 
    |       |  d   |   10        | 
    |Total  |  a   |   8         |  
    |       |  b   |   10        |  
    |       |  c   |   12        |  
    |       |  d   |   14        |  
    |Grand  |      |             | 
    |Total  |      |   44        | 
    +-------+------+-------------+

I tried to add in scopes such as =Sum(Fields!Detail.Value, "city"), but I am getting errors that the scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a data region, or the name of a dataset, which makes me think I'm going about this the wrong way, and I'm out of other ideas.

2
reporting-servicesssrs-grouping

2 Answers

3
votes

I think the simplest way to achieve what you want would be to:

  • copy the existing tablix and paste a new version directly underneath it;
  • remove the totals rows from the old tablix;
  • remove the City group (but not the associated column) from the new tablix, and replace the [City] value with the word "Total";
  • remove the column headings from the new table (but not the associated groups).
0
votes

I thought the simplest way to achieve what you want would be to:

copy the existing tablix and paste a new version directly underneath it; remove the totals rows from the old tablix; remove the City group only (but not the associated column) from the new tablix, and replace the [City] value with the word "Total"; remove the column headings from the new table (but not the associated groups). now you can get your actual requirement