Using dual() method in Pivot Table

[vlad_komarov]

Hello all,

I have a normal Pivot table with two dimensions (Group and Name):

I need to display the same structure in the chart, but without Name column. Using dual() converts this chart to a Straight table (with Group values on every row), which is not acceptable for me.

The only other UI option I was able to find is to minimize the width of the Name column and hide it's values.

Anyone knows a better solution?

Looking forward for comments and suggestions.

Best regards,

Vladimir

[swuehl]

Not clear to me how you have used the dual() function here, what have you used as numeric representation?

But besides this, I think you want to have a second dimension with a 1:1 relation to Name here.

I really don't see a way using only Group dimension and having a grouping with a finer granularity.

Hiding the second dimension (you can set column width to zero with a macro) might be an option to get the visual appearance you want.

Maybe I just don't understand what you are trying to achieve.

[vlad_komarov]

I've tried to use dual as :=dual(Group, Name_ID), but as I've mentioned it did not help much.

But setting up the column's width to 0 might be the simplest solution for me...

Do you have this macro's code handy? I will try to search for it on the forum, if not.

Thanks for a suggestion!

Vladimir 

[swuehl · Accepted solution]

Here is code and sample (for an expression column, but AFAIR should be straight forward for a dimension column).

Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

[vlad_komarov]

Found the one in this post:

Re: Pivot Column Width to Zero

sub Squeeze

call HideColumn("CH01", 2)
end sub
'
private sub HideColumn(ch, n)
set ch = ActiveDocument.GetSheetObject(ch)
ch.SetPixWidth (n-1), 0
end sub