Hi Team,
Can we Hide Dimension of a PIVOT table without MACRO? I cannot use Straight table (Requirement is for Pivot only) and neither a Macro (Since IE plugin would be needed on all Clients and not feasible)
I have a Pivot table which has 5 Dimensions(Dim1,Dim2,Dim3,Dim4,Dim5) and 2 Expressions(Exp1,Exp2)
Consider Sample Data as
Dim1 Dim2 Dim3 Dim4 Dim5 Sales Profit DummySKU
Asia India Maharashtra Mumbai Prod1 50 50 1
Asia India Maharashtra Mumbai Prod2 50 50 0
Asia India Maharashtra Pune Prod3 50 50 1
Asia China Beijing Huan Prod4 50 50 1
Asia China Huan1 Seqop Prod5 50 50 1
User wants to see PIVOT output as below:
Asia India Maharashtra Mumbai Prod1 100 100
Asia India Maharashtra Pune Prod3 50 50
Asia China Beijing Huan Prod4 50 50
Asia China Huan1 Seqop Prod5 50 50
(Please observe that Sum(Sales) and Sum(Profit) is to be done for all Records but we do not want to display the rows where DummySKU = 0. Thus, Prod2 is getting added but not getting displayed)
I have done a solution of adding a Calculated Dimension of DummySKU and Suppress Null when it is 0, This suppresses the record but this additional Dimension is still visible to the User. I have put a Blank TextBox on this extra dimesnion but managing the Length of TextBox is being looked upon by me now.
Is there any Straightforward solution to my Problem given all of the above conditions. I have already looked at solution given by Gysbert to use a MACRO at the link Hidden dimension in pivot table. Kindly suggest.
Thanks.
Kuldeep.
