CODEWITHSUNDEEP
excelpivot-tablepowerpivotvba
tbone
tbone

Reputation: 5865

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

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.

Upvotes: 1

Views: 11534

Answers (1)

tbone
tbone

Reputation: 5865

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]"

Upvotes: 2

Related Questions