Trying to Add Sub Row Group inside Parent but flush left in the nested cell

PositiveGuy picture PositiveGuy · Jun 8, 2012 · Viewed 31.9k times · Source

What I am trying to do:

I am trying to create nested groups but I want the sub row group to show directly underneath the parent, flush to the left like the parent.

So in this case You'd have:

Market
SubMarket

so results for example would be:

California <<< market
SanJose
Pleasanton
Walnut Creek

Illinois <<< market
Chicago Suburbs
Chicago

... and so on

I cannot figure out why the heck I can't get a child row group to show directly under a

Can anyone help me on this? I'm truly frustrated with SSRS. No idea why it doesn't just add the grouping right on the cell I had clicked. Instead it creates new columns! Yea by design but why? and how to get around this. What I am trying to do seems so simple...yet so hard in SSRS.

Here's an example of what I tried to do:

  • Creating a Parent Row Group Within a Parent: If you click a cell within a parent group (in this case Market) and then add another parent group on that cell it'll add a new column in your SSRS report to the RIGHT of the parent that you are in:

enter image description here

  • Creating a Child Row Group Within a Parent: If you are in a Parent Group and click on a cell and add a child row group, it'll add the row to the right of the parent

enter image description here

What I want this to do is this, and I can't get SSRS to put that submarket here:

enter image description here

Answer

Jeroen picture Jeroen · Jun 16, 2012

If I understood your question correctly, what you want is certainly possible, and it's certainly a pain to get it to work. Here's a small example with some mock data that should be more or less analogous to your situation.

The dataset:

Market    SubMarket
------    ---------
A         1
A         2
A         3
B         4
B         5
B         6
B         7

Here are the steps I've taken to create the table:

  1. Drag/drop a new, fresh table to the designer.

  2. Drag/drop the SubMarket field to the first column.

  3. Right-click the detail row and add a new parent group.

    Add parent group

  4. Group on Market and choose to add a group header row.

    New group settings

    As you've already noted, SSRS "helps" and adds an extra column.

  5. Right-click the extra (first) column and delete it.

  6. Select the cell in the header row for the group and enter "[Market]", or pick it from the quick-list.

    Add field to header row

  7. Finish up by filling in the other fields/columns/cells. For the header row drag/drop won't work, you'll have to pick the fields for that row yourself (or type 'em).

    This is the resulting table in preview (with some emphasis on the header row):

    Sample output

Hope this helped!