Dynamic sorting on pivot Table

[#1]

HI,

Please help me in Dynamic sorting on pivot Table like stright table ,where ever we clik on table cols, data has to sort.

thanks in advance.

srini

[Anonymous]

Calculated dimensions can be sorted only by alfabetical order or loading order.

If you want something more complex you can try to set up a macro that runs your ordering algorithm and try to write the value in the cells, but I don't know if it works...

[srinivasa1]

Hi ,

Thx for reply,

hey can u please guide me to how can achive this writing macro? do you have any example...

thanks once agian.

srini

[Anonymous]

If you don't want sort a calculated dimension you should check the pivot table box API. I think that in the property class there is something to set up the sort order (usually the API realize by functions what we can see on the object property window).

I checked the API, I didn't find something specific for Pivot table...what you can try is to modify the default sort criteria for a specific field...I don't know if it works, but you can make a try:

set fld=ActiveDocument.GetField("Salesman")
set fldsrt = fld.GetDefaultSortCriteria
fldsrt.SortByExpression = 1
fldsrt.Expression.v = "sum(Amount)"
fld.SetDefaultSortCriteria fldsrt

If you must sort a calculated dimension your target is harder to reach..I think the only solution is to calculate the dimension in the script pahse (so it isn't anymore a calculated dimension).

If the macro doesn't work you also try to build in the script a set of preordered dimensions and by macro each time you press a button you rebuild the table with the appropriate dimension. (you also build serveral table, each one preordered by a specific criteria and by a macro set the "visible" property, so you can always display the correct ordered table; the drawback is that you have to maintain several table: one for each sort criteria).

I hope these tips can help you

regards,

Matteo