Hi All,
The below code allows me to select only zero items in my pivot table. Is there a way to modify this to de-select zeros (I basically want to remove zero amounts from my pivot). Thank you in advance for any assistance!
Please Login or Register to view this content.
.VisibleItemsList can only set list of visible items as it's name suggests.
You will need to construct array of pivot items that you want to show.
You can loop over the PivotFiels().PivotItems to construct your array.
Though you may find it's far easier to control Pivots via manual slicer selection rather than code (or through use of DAX measure if that's available).
Ex:
Please Login or Register to view this content.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Thanks CK76. I tried your code and received a run-time error 13 (type mismatch) on the last line of code.
I've been researching this trying to find a way to use HiddenItemsList with no success. I really just want to ensure that each month as my data changes I remove all zeros from the GL Balance.
Upload sample workbook. If you need specific help.
Type mismatch indicates to me it is likely that there is no item added to iDic.
Did you have pivot table filtered and showing only .&[0] item showing when the code was run?
Try changing code to...
pvF.ClearAllFilters will ensure that filter is reset before the code runs.Please Login or Register to view this content.
Thanks again CK76. No filters were applied previously, and the updated code returns the same error on the same line. I can try to modify the file and upload a sample as the information is sensitive. I appreciate your help.
I tried stepping through the code and noticed that it skips over the If statement completely. It goes from the For Each to the last line of code then errors out.
Wait... that means pvI (Pivot Item) has no item that passes If condition...
I'll need to look at your pivot's OLAP model to really see what's going on.
You can desensitize data, for your model. Make it static model with connection/query killed and upload it here. I can take a deeper look at what's going on.
Tested code on one of my OLAP pivots. Had no issue.
Here is the revised file. Thanks again!
Ah, you have it in page field. That's the issue.
PageField cannot be looped using the method, and it does not have VisibleItemsList property.
Code would work as is, if you add the field to "Row Label" field.
Alternative is to create Slicer using the field. And control it via Slicer.
Are you open to using Slicer for this field?
I am open to everything!
With slicer there's no need to clear filter. So code would be something like below.
SlicerCacheLevels is needed to work with OLAP based slicer cache (to deal with hierarchy columns/slicer).
But this one only have single level, you can use SlicerCacheLevels(1) without issue.
See sample with Slicer added. Code is in Module5.Please Login or Register to view this content.
Awesome CK76. Thank you for all your time and assistance!
You are welcome and thanks for the rep
Please Login or Register to view this content.
Last edited by aishwarya_srivastava; 06-26-2023 at 08:29 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks