Disable cell merging in row group in SSRS

4
votes

I've created several reports in SSRS. For example, I have report which shows number of people in cities grouped by sex.

If I export this report to Excel, the City column is merged over its whole group.

----------------------------------------------------------------
| City                | Sex             | Population           |
| ============================================================ |
|                     | men             | 5582                 |
|                     | -------------------------------------- |
| The Big City        | women           | 5468                 |
|                     | -------------------------------------- |
|                     | total           | 11350                |
| ------------------------------------------------------------ |
|                     | men             | 1609                 |
|                     | -------------------------------------- |
| The Small City      | women           | 1564                 |
|                     | -------------------------------------- |
|                     | total           | 3173                 |
----------------------------------------------------------------

But I have to filter data in Excel. For example find number of womens in Big City. This is not possible with merged cells. So I want this.

----------------------------------------------------------------
| City                | Sex             | Population           |
| ============================================================ |
| The Big City        | men             | 5582                 |
| ------------------------------------------------------------ |
| The Big City        | women           | 5468                 |
| ------------------------------------------------------------ |
| The Big City        | total           | 11350                |
| ------------------------------------------------------------ |
| The Small City      | men             | 1609                 |
| ------------------------------------------------------------ |
| The Small City      | women           | 1564                 |
| ------------------------------------------------------------ |
| The Small City      | total           | 3173                 |
----------------------------------------------------------------

I've found one possible solution here. Add following grouping expression to City row group properties.

=Fields!City.Value & Fields!Sex.Value

But I'm not satisfied with this solution. Reports are generating much more longer now, because report is generated from 3 - 4 millions of records. This is not acceptable for our customers.

How can I disable cells merging in row group?

Many thanks!

3
excelreporting-servicesexport-to-excel

3 Answers

2
votes

A little late, but these steps worked for me:

  1. Create the groupings per usual so that city is in a group as in the above.

  2. Create a new "City" column by inserting a column to the left of the current "Sex" that is still within the "details" grouping.

  3. Delete the original "City", but do not delete its group. In the "Grouping" tab at the bottom of the page you should still see (City) as a group.

I did not notice any changes to performance using this solution even with a very large dataset.

Hope this helps!

0
votes

I found a really good solution to this which is going to group properties and group on the cell you want to merge. So right click City under your row groups and on the group expression you group on sex, instead of grouping on city. At least that worked for me.

-2
votes

Instead of doing your groups and sum on the design/presentation side, you could modify your sql so that grouping and totals are done on the dataset. Since you are then not grouping any of the rows, the table should export out to Excel as individual sells and not merged cells.