Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You could put these condition within a displayed dimension like:
if(DummySKU = 1, Dim5)
- Marcus
I don't think without can possible or not. Let's wait some time
You could put these condition within a displayed dimension like:
if(DummySKU = 1, Dim5)
- Marcus
Hi Marcus,
Such a Simple solution. Yes indeed your provided solution helped and I am able to Hide the Dimension in Pivot by putting the condition. Sorry, I could not think of this simple solution. Thanks Marcus.
Thanks.
Kuldeep.