Determine record number within sub summary?

Questions
,
1

Suppose I have data like this:

City        Name           Role       
--------------------------------------
Bakersfield Alice Allister President  
Bakersfield Bruce Bagston  Secretary  
Camarillo   Cathy Carson   President  
Camarillo   Dorothy Dall   Secretary
Camarillo   Ernie Erneston Treasurer

But in my report, for aesthetic resaons, I only want to show the City name once, like this:

City        Name           Role       
--------------------------------------
Bakersfield Alice Allister President  
            Bruce Bagston  Secretary  
Camarillo   Cathy Carson   President  
            Dorothy Dall   Secretary
            Ernie Erneston Treasurer

What's the best way to achieve this?

Right now, I'm
• using a single body part
• sorting on City then Role then Name
• the City field has conditional formatting which hdes the City name if Role = "President"
• This sort-of works, but is brittle, for example it will fail if there's a City with no president.

Is there a calculation or Get() function which will tell me, for a sorted table, which record number I'm at within a sorted group?

2

Try a hide object rule with the GetNthRecord ( field ; recordNumber ) function.

3

@bdbd - not sure what you mean by that. Did you mean to have Get(recordNumber) in the formula?

4

I think I've figured it out:

  1. [Edit: this step is actually not necessary, unless you want to separate the groups with a line or header or similar] The layout must have subsummary parts for each sort field down to the level where you want to start counting rows, e.g. in this case, City (but not Role or Name). The subsummary part(s) aren't going to display anything, so they can be 1 pixel high.
  2. Put your display fields in the Body part of the layout
  3. Sort your table by these same subsummary parts in the same order as well as any other Sort criteria you want. In this case, I sorted by City, Role, Name.
  4. Create a Summary field. The summary field needs to have these options:
    sNum = Count of [serial number] (running, with restart) when sorted by City
  5. This summary field then gives me exactly what I want: within a sorted group, the effective record number within the group, like this:
sNum City        Name           Role       
--------------------------------------
1    Bakersfield Alice Allister President  
2    Bakersfield Bruce Bagston  Secretary  
1    Camarillo   Cathy Carson   President  
2    Camarillo   Dorothy Dall   Secretary
3    Camarillo   Ernie Erneston Treasurer

Then it's easy - just set the City field to hide when sNum > 1

The key were these somewhat hidden options for summary fields:

image

(Edited for clarity)

5

Hide the city field with this calculation:

Get (RecordNumber) > 1
and city = GetNthRecord (city; Get (RecordNumber) - 1)
3 Likes
6

I'll leave mine tagged as the "solution" since I think the general idea of being able to know the record number within a sorted group is useful, but yours is a really nice solution too...

It's much simpler than mine as it doesn't require a summary field or subsummary part on the layout.

7

I was a little surprised to read that the sub-summary layout parts have to exist. I would not have expected that. I would have expected that doing the sort, and defining the summary field would, alone, would have been enough to make such an approach work.

8

You are right - I just ran a test, and indeed the sub-summary part is not required. In my case, I wanted to have a line separating each group, so I included it, but it looks like one can do without.

2 Likes