Coding with pivots and Multiselect list

[job]

I have a pivot table that I'm refreshing based upon the values on a form.
Parameter is updated by combobox1 Specialty is updated by a multiselect
listbox and region is updated by combobox3. I've found that in order to
make the Specialty selections work for the pivot, I have to iterate through
the .PivotItems and make them all hidden, then I loop through the listbox to
determine what was selected. Using an array to store those values I then
unhide the .PivotItems saved in the array as selected items. This code
works if you build the pivot from scratch (via code) each time. However,
for this particular pivot it gives me the runtime '1006' because it is
saying that I can't hide everything, I need at least 1 selection. Has
anyone ever worked around this or think of a better way to deal with a
multiselect listbox as critera for a pivot?

Cheers,

Job

 

[Jim Thomlinson]

That is a dilly of a pickle. I work Olap Cubes and at first glance I would
say that you need to create a new parent member for the item you have
selected in the list box. If your data is stored in Excel this should not be
too bad. Insert a column in the raw data and use the selections from the
multiselect list box to populte the Column. It could be a simple as calling
the column Selection and populating the column with True / False values
depending on whether the row meets the criteria.

HTH

 

[job]

Thanks for the idea Jim. The dataset had quite a few rows. Are you saying
to look at each value in the multiselect then go look at every row in the
dataset to see if that particular cells value = to multiselect selection,
then do a true false in another column? I hadn't thought of doing it that
way. Am I correct in my explanation of what you had in mind?

 

[Jim Thomlinson]

Exactly... Sorry about taking so long to get back to you... Essentailly you
are dropping flags down that you can use as a dimension upon which to pivot.

HTH