Sorting sections headers based on number of rows

building reports desktopintelligence
1

Hi

I have the following table

Site ID…TT_ID
a…TTa1
a…TTa2
b…TTb1
b…TTb2
b…TTb3
b…TTb4
c…TTc1
c…TTc2
c…TTc3

I use Site_Id as Section
I want that the Site Ids having the greatest amount of rows will be displayed first that is first b then c and then a
Is there any way to control the order in which the section fields are displayed?
I can do this with custom sort but it means a lot of manual labor each time the report is refreshed.

Thanks
Rafael

Rafaelz (BOB member since 2007-10-28)

2

One option in this case, instead of using sections, use a “break” on site id

In slice and dice, remove site id as a section and move it back down into the block and put a break on it. Also put a count on tt_id. You’ll have to change the format of your report a little bit to make it appear like it has been sectioned. Use View/Outline to do this.

Then select Format/Sorts/Add --> the count formula will be available for the sort. The count formula is not available if you use sections in this case.

MBTDC :us: (BOB member since 2007-12-28)

3

Hi MBTDC

I’m afriad that doesn’t solve the problem because when you apply either a break or a section (in this case on Site ID) on a field, this field is automatically sorted first.
If you do this and then use Format/Sort/Add your sort is secondary to the break field and therefore doesn’t affect it’s internal order.
What i try to do is to overide the break field initial sort and sort by the count instead.

Thanks
Rafael

Rafaelz (BOB member since 2007-10-28)

4

Hi Rafael,

Try looking at the context of the count formula. If it is count(<TT_ID>) In (Site_Id, TT_id) its not going sort the break level. It should be count(<TT_ID>) In Site_Id. I have tested my solution and it is sorting according to your requirements.

DC

MBTDC :us: (BOB member since 2007-12-28)

5

Hi MBTDC

It is indeed possible that i do not understand what you are trying to explain.
I have applied a break on “Site ID” and then added a sort.
What can be seen on the screenshot that i have provided is that after applying a break the context of the sort is overridden by the break.
Without the break the context of Count Site is “in Site_ID” (The site for some misterious reason doesn’t allow me to add more attachements so i can’t provide a screenshot of this) What am i doing wrong?

Thanks
Rafael
Sorting A break 3.JPG

Rafaelz (BOB member since 2007-10-28)

6

Hi Rafael,

Lets try one more thing.

  1. In the report, highlight site id
  2. Select Format/Breaks
  3. Select Break Footer
  4. In the report, select Count Sites and copy
  5. Paste Count Sites into the footer
  6. Format/Sorts, Select Add and choose the new count
  7. Remove the old count and apply

This should create the count in the context that you need. If this doesn’t work, I am kinda of out of ideas…

DC

MBTDC :us: (BOB member since 2007-12-28)

7

Hi MBTDC

Thank you very much.
Steps 4 & 5 made the difference

Again thank you for your help
Rafael

Rafaelz (BOB member since 2007-10-28)