Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Below is an image of how the pivot grid is set up.
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
Thanks, Marcus but that is quite complex just to sort on additional dimensions...and I am not sure even where to start.
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