Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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
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