Access table property (suppress zero-values) from VB/JS script

[giacomom]

Hi everybody,

I have a button that should enable/disable the suppress zero-values option in a table but I can't understand how to do it in a script. I read in the documentation that from the macro interpreter the only reference available is the ActiveDocument property and all references must be made starting from that point, but after I access to the table by

set table = ActiveDocument.GetSheetObject("CH36")

I don't know how to proceed. Can you help me find the solution?

Thank you in advance,

Giacomo

[marcus_sommer · Accepted solution]

Have a look on this example from the APIGuide.qvw. The important thing is to read the object-properties, change them and set the properties again.

set chart = ActiveDocument.Activesheet.CreatePivotTable

chart.AddDimension "ProductType"

chart.AddExpression "sum(Amount)"

chart.AddExpression "count(Customer)"

set cp = chart.GetProperties

cp.SuppressZero = false

chart.SetProperties cp

- Marcus

[giacomom]

Great! Thank you so much Marcus!

In this way though I set the suppress zero-values option for all the dimensions and expressions in the chart. Is there a way to specify only a subset of dimension/expression I want the option to be enabled for?

Giacomo

[marcus_sommer]

This option is global for each object. On dimension-level are options available to supress null in dimension-values or show all values. With this and in combination with dynamic dimensions (need a lot of performance) and set analysis expressions you could reach maybe a more flexible solution. But it's difficult and need some trial and error - if it's more nice to have and not a must you should it leave so simply as possible.

- Marcus