How to find data source of a slicer for a pivot table via the Excel UI?

0
votes

Note: I don't think it makes any fundamental difference, but I am working with pivot tables running on top of a PowerPivot model.

Example scenario:

Three tables in a model: SalesTransaction, BuyerCustomers, SellerCustomers, with a two defined PowerPivot relations: 

BuyerCustomers.CustomerCode --> SalesTransaction.BuyerCustomerCode
SellerCustomers.CustomerCode --> SalesTransaction.SellerCustomerCode

I have a PivotTable defined using SalesTransaction as the data source.

Now, if I want to create slicers on both BuyerCustomer and SellerCustomer, in the Pivot Table fields window I can right click and "add as slicer" on either:

  1. SalesTransaction.BuyerCustomerCode and SalesTransaction.SellerCustomerCode (the two columns in the transaction table)
  2. BuyerCustomers.CustomerCode and SellerCustomers.CustomerCode (the individual lookup tables)

Either way, the behavior is identical. My question is: once this has been set up, how can one tell what a slicer is bound to via the UI in Excel? Other than being able to deduce the obvious association via column names, how does one tell?

Using VBA, one can discover the association like so:

ActiveWorkbook.SlicerCaches("Slicer_CustomerCode").SourceName

...which yields: 

"[SalesTransaction].[BuyerCustomerCode]"  
        or  
"[BuyerCustomers].[CustomerCode]"

....but as far as I can tell, there is no way to see this via the UI.

1
excelpivot-tablepowerpivotvba
There is no way to drag this result into a cell via a formula, but you can see which pivots a slicer (or rather it's cache) is bound to by going to SlicerTools>>Options>>PivotTable Connections. To bring it into a cell you would have to use a UDF. – JNevill
Sorry, I'm not trying to display it, I'm just trying to SEE what column the slicer acts upon in the Pivot Table. Sometimes column naming is ambiguous enough that you really can't know for certain which column it is bound to - it seems unbelievable that there is no way of knowing this without writing VBA code. – tbone
Oh. I misunderstood. The field in the original table to which it is bound can be found in SlicerTools>>Options>>Slicer Settings. It's the "Source Name" at the top of that dialog. – JNevill
Ya I have seen that, the problem is it only shows the COLUMN name rather than the TABLE.COLUMN name that can be seen via SlicerCaches("slicerName").SourceName (NOTE: I accidentally excluded .SourceName in my code example above, fixed now.) In large models with not always unique column names, not being able to see the fully qualified name is unfortunate. Sadly, I think the correct answer to this question is: "You can not see the TABLE.COLUMN to which a Slicer is bound, only the name of the COLUMN." – tbone
Yep. I agree that the answer is "no" in that case. I've had a single slicer hooked up up to nearly 52 small tables, so I would imagine that the Microsoft powers that be decided not to make a table/field display since it could overwhelm a dialog box. – JNevill

1 Answers

1
votes

You cannot find the slicer TableName.ColumnName data source via the UI, you can only see the ColumnName.

As posted in the question, you can see both table and column names via VBA:

ActiveWorkbook.SlicerCaches("Slicer_YourSlicerName").SourceName

...which yields:

"[TableName].[ColumnName]"