Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the below Pivot table where I have generated dates which isn't available in my DB. The row is getting added(in here 10/19/2017) along with a column full of 0's. Is there a way to hide the column with 0's alone?
Cdate/LOB | Manual | Auto | Referred | |
10/21/2017 | 10 | 0 | 0 | 0 |
10/20/2017 | 10 | 35 | 0 | 0 |
10/19/2017 | 0 | 0 | 0 | 0 |
10/18/2017 | 67 | 6 | 6 | 0 |
10/17/2017 | 0 | 78 | 0 | 0 |
10/16/2017 | 89 | 0 | 8 | 0 |
I did find this macro which says that we can hide the colum . Could you please let me know how exactly this works? or is there any other way?
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
Any help will be much appreciated.
Thanks,
Anupama Jagan
You can use design options to realize this goal. Like a report adhoc, look instead
But this column with 0's is populated because of the missing dates im generating. It doesn't have any expression attached to it.
post the sample app .
regards
Pradosh
I would suggest you use a calculation condition for that column something like below
=if(total for referred expression > 0 ,1,0) // show the expression if there is atleast one non zero value else hide expression
The macro only reduces the column width, which means it's still there ...just not visible!
Can you explain little more is about povit table and when you need to open the document you are good to hide one specific column. I believe this may possible here..
This pivot table is shown when we open the application itself .this is the first tab.
The pivot table without generating the missing dates will be like this:
Cdate/LOB | Manual | Auto | Referred |
10/21/2017 | 10 | 0 | 0 |
10/20/2017 | 10 | 35 | 0 |
10/18/2017 | 67 | 6 | 6 |
10/17/2017 | 0 | 78 | 0 |
10/16/2017 | 89 | 0 | 8 |
But after generating the missing date which is 10/19/2017 im getting an extra column with 0's
Cdate/LOB | Manual | Auto | Referred | |
10/21/2017 | 10 | 0 | 0 | 0 |
10/20/2017 | 10 | 35 | 0 | 0 |
10/19/2017 | 0 | 0 | 0 | 0 |
10/18/2017 | 67 | 6 | 6 | 0 |
10/17/2017 | 0 | 78 | 0 | 0 |
10/16/2017 | 89 | 0 | 8 | 0 |
So, that means when you suppress from presentation tab what will be the report
when we suppress zeros from Presentation tab it takes out the 0's rows also..
I need to have the row with zeros ( in the above table 10/19/2017) but need to remove the 0's column ( after the referred column)
Manual ,Auto, Referred are all LOB's
Tri to enable the condition for all metrics line
If(manual exp >0, 0,1)
If(auto exp >0, 0,1)
If(referred exp >0, 0,1)
Actually my expression is only one:
sum(Subs)
In dimension I have Date and LOB
I have dragged the LOB to the top to show sum per LOB