Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1176
  • Last Modified:

Custom order of pivot items

Dear Experts,
I have a simple pivot table that is generated by code with a user-chosen data set. Since I want to print the result along with other tables, I need to constrain the number of rows to a fixed number. My strategy is to sort the PT by the single data field (a sum, descending), then group the pivotitems that exceed a certain number of rows into an "other" category,  My problem is I'd like to retain the descending sort based on the data field, but force "other" to always be at the bottom row.  I know I can set the pivotitem.position, but I can't seem to understand how excel assigns item positions, and their relationship to the ordinal position in the list.

Here's  a snippet:
 With PT 'ActiveSheet.PivotTables("SR4")
        iNumSppRows = WorksheetFunction.CountA(Range("J" & iSumRowStart & ":J80")) - 2 'remove total row
        iLastSppRow = iSumRowStart + iNumSppRows  'store a pointer to the last row
         If iNumSppRows > 11 Then
            Range(Cells(iSumRowStart + iMaxListSize, 10), Cells(iLastSppRow, 10)).Select  '10 is that max spp well accept
            'store the list of spp in a range, then group
            Selection.Group   'group the >10 spp into an OTHER group

          ' NB The distinctions between Species and Species2 below.
          'Hide Species, collapse species2, rename group1
          'And finally, move it to the bottom of the species list
            .PivotFields("Species2").ShowDetail = False
            .PivotFields("Species").Orientation = xlHidden
            .PivotFields("Species2").AutoSort xlDescending, _
        "Sum of BF_Total", ActiveSheet.PivotTables("SR4").PivotColumnAxis.PivotLines(1) , 1
     '      .PivotFields("Species2").PivotItems("Group1").Position = 16 '  PROBLEM
            .PivotFields("Species2").PivotItems("Group1").Caption = "Other"
     
         End If
   '********************************************
   End With
0
RJT_VT
Asked:
RJT_VT
  • 2
  • 2
  • 2
1 Solution
 
RobOwner (Aidellio)Commented:
Unfortunately I don't believe this is possible.  have you seen otherwise?

When it comes to data warehousing and business intelligence, excel is very limited.  You may be interested in Qlikview (http://qlikview.com) a very simple and easy to use reporting tool.
0
 
RJT_VTAuthor Commented:
I have seen a solution that suggests one can sort all pivotitems in a recordset to reorder the position, but the description was cryptic.  Hard to believe you can't replicate what takes two seconds with a mouse!  
This needs to be distributed, so qlikview won't work.
0
 
RobOwner (Aidellio)Commented:
Qlikview is a distributed system but we won't go down that path and too big a learning curve...

If you can do it via the mouse, have you tried recording a macro while you're doing it?  I know the number of rows will vary but that's easily fixed.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Rory ArchibaldCommented:
Probably easier to reverse what you are doing and move each of the original top 10 items back to position 1 in reverse order:
   Dim n As Long
   Dim vSortData

   With PT   'ActiveSheet.PivotTables("SR4")
      .PivotFields("Species").AutoSort xlDescending, .DataFields(1).SourceName, .PivotColumnAxis.PivotLines(1), 1
      With .RowFields(1).DataRange
         vSortData = .Cells(1).Resize(10).Value
         If .Count > 11 Then .Cells(11).Resize(.Cells.Count - 10).Group   'group the >10 spp into an OTHER group
      End With
      ' NB The distinctions between Species and Species2 below.
      'Hide Species, collapse species2, rename group1
      'And finally, move it to the bottom of the species list
      .PivotFields("Species").Orientation = xlHidden
      With .PivotFields("Species2")
         .PivotItems("Group1").Caption = "Other"
         ' move each of the top 10 back to position 1 in reverse order
         For n = UBound(vSortData, 1) To LBound(vSortData, 1) Step -1
            .PivotItems(vSortData(n, 1)).Position = 1
         Next n
            
      End With
      '********************************************
   End With

Open in new window

0
 
RJT_VTAuthor Commented:
Worked like a charm. Thank you for an elegant solution that both solves the problem and adds measurably to my knowledge!
0
 
Rory ArchibaldCommented:
Glad to help. :)
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now