Llevo unos días peleándome con el modelo de objetos de las tablas dinámicas. ¿Para qué? Para poder extender su funcionalidad. Si tienes Visual Studio 2010, crear un add-in para Excel es tan sencillo como indicar que vas a crear un nuevo proyecto de tipo Add-in para Office 2010 y a partir de ahí, ya puedes añadir funcionalidad a tu Office.
En el caso de Excel 2010 me interesa extender las funcionalidades de la tabla dinámica (o PivotTable para los amigos), pero para ello hay que saber en qué parte de tu woorkbook estás. Para eso es necesario pensar en posiciones relativas y que elementos están relacionados con dichas posiciones. ActiveCell nos va a decir que celda es la activa en el momento de ejecución y a partir de ahí podemos acceder a todos los objetos que sean válidos en esa celda.
Para el caso de las tablas dinámicas, el objeto a diseccionar va a ser PivotTable. Así para acceder a los valores que está manejando la tabla dinámica que hay creada en una hoja accederemos como Application.ActiveCell.PivotTable y a partir de ahí se abre todo un mundo de objetos y propiedades que nos van a permitir añadir nuevas funciones que eliminen algunas de las limitaciones que hayamos podido encontrar en Excel cuando nos conectamos a un cubo de Analysis Services (SSAS).
Un buen ejemplo de que se puede hacer son las PivotTable extensions que se pueden bajar de CodePlex, además pueden servir como guía para ver como codificar tu propio add-in.
Como punto de partida para ver con qué elementos contamos tenemos el interface de PivotTable publicado en MSDN.
Ahora bien entender el modelo y sus relaciones no es sencillo, aquí os pongo un pequeño resumen con lo que he experimentado que espero que ayude para aclarar que es cada cosa.
- PivotTable – El objeto PivotTable (o tabla dinámica en si).
- PivotCell – Cada una de las celdas que están dentro del rango de la tabla dinámica. Pueden tener valores, o elementos de las dimensiones, así como las cabeceras y totales.
- PivotField – Cada uno de los elementos que se pueden utilizar para componer la zona de filas, columnas, datos, etc en la PivotTable (digamos que son los elementos que nos salen en la lista de campos y que el usuario utiliza directamente). A su vez, cada uno de estos elementos tiene asociado un CubeField que lo mapea con un elemento del cubo OLAP al que está conectada la PivotTable.
- PivotCache – Contiene la información relacionada con la conexión utilizada actualmente por la PivotTable así como los datos actuales en memoria.
A través de estos objetos podemos acceder a algunos datos interesantes:
-
Obtener la cadena de conexión:
Application.ActiveCell.PivotTable.PivotCache.WorkbookConnection.OLEDBConnection.Connection
-
Obtener el nombre del cubo con el que estamos trabajando:
Application.ActiveCell.PivotTable.PivotCache.WorkbookConnection.OLEDBConnection.CommandText
- Obtener la configuración del número de filas máximas a devolver en una visualización de detalle: Application.ActiveCell.PivotTable.PivotCache.WorkbookConnection.OLEDBConnection.MaxDrillthroughRecords
-
Conocer el nombre del campo con el que se está rellenando una celda:
Application.ActiveCell.PivotCell.DataField.SourceName
-
Identificar si un campo de una celda hace referencia a una métrica calculada, una métrica, un atributo, etc.:
Application.ActiveCell.PivotField.CubeField.CubeFieldSubType
