Sorting Macro Does Not Sort All Dimensions

[danielreber]

Hi,

I have a macro that sorts a dynamically created pivot grid. The sort works on the first level but not the subsequent levels. Is there something that I am doing wrong?

Below are the macro and image of the properties.

Sub Sort

set pivot = ActiveDocument.GetSheetObject("PG01")

set prop = pivot.GetProperties

set dims = prop.Dimensions

'clear all sorts
for i = 0 to dims.Count-1

	dims.Item(i).SortCriteria.SortByLoadOrder = 0
	
	dims.Item(i).SortCriteria.SortByAscii = 0
	
	dims.Item(i).SortCriteria.SortByFrequency = 0
	
	dims.Item(i).SortCriteria.SortByLoadOrder = 0
	
	dims.Item(i).SortCriteria.SortByNumeric = 0
	
	dims.Item(i).SortCriteria.SortByState = 0
	
	dims.Item(i).SortCriteria.SortByExpression = 0
	
	dims.Item(i).SortCriteria.Expression.v = ""

next

pivot.SetProperties prop

set sortExpression =  ActiveDocument.Variables("vSortExpression")
set sortDirectionVariable = ActiveDocument.Variables("vSortDirection")

if sortDirectionVariable.GetContent.string = "" then
	sortDirection = 1
else
	sortDirection = CInt(sortDirectionVariable.GetContent.string)
end if

if sortExpression.GetContent.string = "Dimensions" then

	for i = 0 to dims.Count-1
		
		dims.Item(i).SortCriteria.SortByAscii = sortDirection
		dims.Item(i).SortCriteria.SortByNumeric = sortDirection
	
	next
	
end if

if sortExpression.GetContent.string <> "Dimensions" then

	'set new sort for all dimensions
	for i = 0 to dims.Count-1	
		
		dims.Item(i).SortCriteria.SortByExpression = sortDirection
		dims.Item(i).SortCriteria.Expression.v = sortExpression.GetContent.string
	
	next

end if

pivot.SetProperties prop

end sub

[danielreber]

Below is an image of how the pivot grid is set up.

[marcus_sommer]

I think the following will be quite useful for your task: Perfectly-Sorting-Pivot-Table-by-A-Z-y-Value-set-for-each-dim.

- Marcus

[danielreber]

Thanks, Marcus but that is quite complex just to sort on additional dimensions...and I am not sure even where to start.

[marcus_sommer]

I think there is rather no easier way to solve it and that you will need to struggle through the matter ...

But I suggest to do it not within your target-application else to create a small dummy application with just a few dimensions/measures and to apply the logic there manually - this means without your macro-stuff (the sort-logic itself is independent from the fact if it's done with a macro or not). If you could then comprehend the logic you could transfer it into your origin application and maybe you don't need the macro for it (all things which could be done with the native UI features shouldn't be done with a macro, see: Macros-are-Bad).

- Marcus