Hidden dimension in pivot table

[#1]

    Hi guys,

here is my pivot table. I have used few dimensions and i need to hide TxnLineId dimension but still want a view like this

RefNumber Rate

1                    11

                      120

                       10

                       10

i.e. i want to maintain the granularity of TxnLineID even if it is hidden.

Can we still have the granularity of a dimension even if it is hidden?

Saurabh

[Gysbert_Wassenaar]

No, but you can set the label of the dimension to a space and set the text color of the values to 100% transparent and resize the column to minimal width. You'll need a macro if you want to set the width to zero.

It's also possible to create a calculated dimension for the Rates, but then you would not be able to have subtotals. Dimensions are not totalled.

[#3]

Thanks for the solution!

Sorry for my ignorance but i could not find the macro. I did Ctrl+M but could not find any thing there.

[#4]

I also found that if I add another dimension after TxnLIneId then the + and - sign shows up for TXnLIneId. I guess this can be fixed by setting the minimal width to zero.

Can you please give a hint that how that macro can be written and used.

[Gysbert_Wassenaar · Accepted solution]

Sub HideColumn
  set chart = ActiveDocument.GetSheetObject("CH01")
    set p = chart.GetProperties
    ' set the width of the 4th column to 0
    chart.SetPixWidth 3,0
End sub

[#6]

I added this macro as a function because my script was recognizing it as a macro. After adding it as function I am calling it as

Let v = HideColumn

but I saw that nothing happened then i added a MsgBox like

MsgBox "Got here" to see if MsgBox gets executed and it didn't so somehow my script is not calling this function...

My Macro security level is "safe Mode" and current local security is "only safe mode"