Excel 2007 : Excel Pivot - overcome calcualted items size issue and use them as report filter

Hi,

I created a custom group for pivot using a calculated item however I am unable to add more items to it due to its size limitation of 255 chars.

e.g. you wish to group some cities (50 cities) data to Easter-Region group.



Another issue is, Excel is not allowing field which is having calculated item to be moved to Report Filter section.



Any clue is there any alternate way to handle this?

Excel-Pivot-Grouping.jpg

Thanks. You are very close to my question scenario.

added screen shots of 2 Pivots using group feature (A) and calculated item approach. (B)

I need just group total lines to be displayed in pivot as there will be many items under each group. pivot Refresh should not change pivot group rows position
If you filter items under A approach, base rows has be to displayed to see group total rows

With B approach, you can just filter for new group however however it has size limit (how many items you can add to this group) in addition to filter restriction issues using report filter.

Hope this clarifies question scenario further.

Thanks. Using Group and Expand and Collapse feature I am able to get group total rows only but how do I move one item from one group to another without redefining all custom groups again?
How can I include one item in multiple groups also if required without changing grand totals shown below?