expand only one field value in a pivot table (on open action)

[Anonymous]

Hi,

I'm reloading a dashboard daily. The Date is the primary dimension in a pivot table. I'd like to write a macro which would be run on open which would firstly collapse all [this is easy "TableProperties.PivotAlwaysFullyExpanded = False"] but then I'd like to expand the max(Date). I'm looking in "API Guide version 11" but i cannot find the property that is responsible for the field/cell being collapsed or expanded, could anyone help with this?

Regards

AM

[#2]

Czesc Andrzej!

I would approach it with the use of bookmarks. Create bookmarks with all states of collapse / expand you need.

(You would need to utilise "Include Layout State" option of bookmark)

Then in macro just set a proper bookmark.

Attaching sample app, which utilises buttons, but you can easily add macros:

ActiveDocument.RecallDocBookmark "MyBookmark"

BR,

Kuba

[antoniotiman]

Document Property -> Trigger

On Open Document

Select in Field

Field : Date

Value : =Max({1} Date)

[Anonymous]

Thanks for the good hint but I'm afraid that it cannot be utilized here because each day a new date value comes and this one should be expanded while other should be collapsed. The use of the bookmarks poses two problems here:

1. I wold have to create a table of dates in advance and for each of them I would have to create a bookmark

2. This is to be a long lasting project so it would have to be a long list of bookmarks [If i were to set up a bookmark via code i'd still need to know how to set the expand/collapse state that i require]

If I misunderstood your idea please let me know.

[#5]

Hello Andrzej,

Sorry, somehow I missed the most important information, that you want to always exapnd a cell not a whole column.

As per my knowledge you can only collapse all / expand all via macro, not specific columns in a table, so this is not an option.

How about just adding additional fully expanded pivot table, showing information only for max(date)?

BR,

Kuba

[Anonymous]

Hi,

it's a good suggestions. If there won't be any reply regarding the cell property i think I'll go with this one. Thank you

AM

[rodneybarrett]

sorry to revive this thread but I have the same exact question.  I will restate here.

Let's say we have two dimensions in a pivot table and an expression.  The fields are Report Period and Date.  

The way I want this to work is For all report periods that are not the current report period, then collapse Report Period.

For the current report period,  expand that Report Period to show the specific dates .   see example data below

BTW,  I need this work programmatically since this qvw is used for a daily nprinting report 

Kind of annoying to go into it each 1st of the month and collapse old month and expand new month.

Jan-2021 +                                       10

Feb-2021 +                                       12

Mar-2021    3/1/2021                    2

                        3/2/2021                   1

                        etc....