sort totals on multiple dimensions

[Joe]

Hi all,

Does anyone know how to sort a total on multiple dimensions?
I’m using owc 11.

Suppose you have a pivot table with 2 dimensions called “customers†and
“stores†with a measure called “salesâ€. Both are in the pivot grid and can
be drilled into. If you sort on the total, it will sort it on the right most
dimension, so the inner expanded items are ordered, but the outer collapsed
ones are not.

The method I’m using is:
Dim ttl As PivotTotal
Set ttl = PivotTable1.ActiveView.Totals("Unit Sales")
PivotTable1.Select ttl, ttl
PivotTable1.Commands(2031).Execute 'descending
PivotTable1.Select Nothing, Nothing

I didn’t have luck with the SortOn function of the fieldset.field, I
received an error message on it – something about method not found.

End result: I want dim1 sorted by total, then dim2 sorted within dim1 by
total.

Any help would be appreciated.

Thanks,
--Joe

 

[Alvin Bruney [ASP.NET MVP]]

I think you have to run a custom MDX expression to get this to work because
it isn't on by default and I haven't seen anything similar in the object
model that would provide that type of functionality.

--

Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99

 

[Joe]

Alvin,

Thanks for the response, but I was hoping to do this with the owc and not
MDX. When I do it with the mouse, I get the following in the pivot xml:

<xivotField
<x:Name>My Pivot Field name</x:Name>
<x:SourceName>[DimXXXX].[XXXX].[XXXX]</x:SourceName>
<x:FilterCaption>My Caption</x:FilterCaption>
<x:Orientation>Row</x:Orientation>
<xosition>2</xosition>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
<x:AutoSortOrder>Ascending</x:AutoSortOrder>
<x:AutoSortField>My measure</x:AutoSortField>

</xivotField>

I was looking for a way to set the AutoSortOrder, and AutoSortField. if
not, maybe something like the following that I found in a posting, but this
doesn’t work with owc11.

Dim f As PivotField
Set f = PivotTable1.ActiveView.FieldSets("Customers").Fields("City")
Set f.SortOn = PivotTable1.ActiveView.Totals("Unit Sales")
f.SortDirection = plSortDirectionDescending

thanks,
--Joe

I think you have to run a custom MDX expression to get this to work because
it isn't on by default and I haven't seen anything similar in the object
model that would provide that type of functionality.

--

Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99
-------------------------------------------------------

Hi all,

Does anyone know how to sort a total on multiple dimensions?
I’m using owc 11.

Suppose you have a pivot table with 2 dimensions called “customers†and
“stores†with a measure called “salesâ€. Both are in the pivot grid and
can
be drilled into. If you sort on the total, it will sort it on the right
most
dimension, so the inner expanded items are ordered, but the outer
collapsed
ones are not.

The method I’m using is:
Dim ttl As PivotTotal
Set ttl = PivotTable1.ActiveView.Totals("Unit Sales")
PivotTable1.Select ttl, ttl
PivotTable1.Commands(2031).Execute 'descending
PivotTable1.Select Nothing, Nothing

I didn’t have luck with the SortOn function of the fieldset.field, I
received an error message on it – something about method not found.

End result: I want dim1 sorted by total, then dim2 sorted within dim1 by
total.

Any help would be appreciated.

Thanks,
--Joe

 

[Joe Sampino]

found it.

the .SortOn and .SortDirection works, it was just a matter of syntax.

Alvin,

Thanks for the response, but I was hoping to do this with the owc and not
MDX. When I do it with the mouse, I get the following in the pivot xml:

<xivotField
<x:Name>My Pivot Field name</x:Name>
<x:SourceName>[DimXXXX].[XXXX].[XXXX]</x:SourceName>
<x:FilterCaption>My Caption</x:FilterCaption>
<x:Orientation>Row</x:Orientation>
<xosition>2</xosition>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
<x:AutoSortOrder>Ascending</x:AutoSortOrder>
<x:AutoSortField>My measure</x:AutoSortField>

</xivotField>

I was looking for a way to set the AutoSortOrder, and AutoSortField. if
not, maybe something like the following that I found in a posting, but this
doesn’t work with owc11.

Dim f As PivotField
Set f = PivotTable1.ActiveView.FieldSets("Customers").Fields("City")
Set f.SortOn = PivotTable1.ActiveView.Totals("Unit Sales")
f.SortDirection = plSortDirectionDescending

thanks,
--Joe

I think you have to run a custom MDX expression to get this to work because
it isn't on by default and I haven't seen anything similar in the object
model that would provide that type of functionality.

--

Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99
-------------------------------------------------------

Hi all,

Does anyone know how to sort a total on multiple dimensions?
I’m using owc 11.

Suppose you have a pivot table with 2 dimensions called “customers†and
“stores†with a measure called “salesâ€. Both are in the pivot grid and
can
be drilled into. If you sort on the total, it will sort it on the right
most
dimension, so the inner expanded items are ordered, but the outer
collapsed
ones are not.

The method I’m using is:
Dim ttl As PivotTotal
Set ttl = PivotTable1.ActiveView.Totals("Unit Sales")
PivotTable1.Select ttl, ttl
PivotTable1.Commands(2031).Execute 'descending
PivotTable1.Select Nothing, Nothing

I didn’t have luck with the SortOn function of the fieldset.field, I
received an error message on it – something about method not found.

End result: I want dim1 sorted by total, then dim2 sorted within dim1 by
total.

Any help would be appreciated.

Thanks,
--Joe

 

[Alvin Bruney [ASP.NET MVP]]

Cool beans.

--

Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99
-------------------------------------------------------

found it.

the .SortOn and .SortDirection works, it was just a matter of syntax.

Alvin,

Thanks for the response, but I was hoping to do this with the owc and not
MDX. When I do it with the mouse, I get the following in the pivot xml:

<xivotField
<x:Name>My Pivot Field name</x:Name>
<x:SourceName>[DimXXXX].[XXXX].[XXXX]</x:SourceName>
<x:FilterCaption>My Caption</x:FilterCaption>
<x:Orientation>Row</x:Orientation>
<xosition>2</xosition>

<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
<x:AutoSortOrder>Ascending</x:AutoSortOrder>
<x:AutoSortField>My measure</x:AutoSortField>

</xivotField>

I was looking for a way to set the AutoSortOrder, and AutoSortField. if
not, maybe something like the following that I found in a posting, but
this
doesn’t work with owc11.

Dim f As PivotField
Set f = PivotTable1.ActiveView.FieldSets("Customers").Fields("City")
Set f.SortOn = PivotTable1.ActiveView.Totals("Unit Sales")
f.SortDirection = plSortDirectionDescending

thanks,
--Joe

I think you have to run a custom MDX expression to get this to work
because
it isn't on by default and I haven't seen anything similar in the
object
model that would provide that type of functionality.

--

Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99
-------------------------------------------------------


Hi all,

Does anyone know how to sort a total on multiple dimensions?
I’m using owc 11.

Suppose you have a pivot table with 2 dimensions called “customersâ€
and
“stores†with a measure called “salesâ€. Both are in the pivot grid
and
can
be drilled into. If you sort on the total, it will sort it on the
right
most
dimension, so the inner expanded items are ordered, but the outer
collapsed
ones are not.

The method I’m using is:
Dim ttl As PivotTotal
Set ttl = PivotTable1.ActiveView.Totals("Unit Sales")
PivotTable1.Select ttl, ttl
PivotTable1.Commands(2031).Execute 'descending
PivotTable1.Select Nothing, Nothing

I didn’t have luck with the SortOn function of the fieldset.field, I
received an error message on it – something about method not found.

End result: I want dim1 sorted by total, then dim2 sorted within dim1
by
total.

Any help would be appreciated.

Thanks,
--Joe