• Author / Uploaded
• rvitecampos

• Categories
• Microsoft Excel
• Tabla (base de datos)
• SQL
• Bases de datos
• Software de gestión de datos

Citation preview

Reportes elásticos con tablas dinámicas y Visual FoxPro (Publicado originalmente en UTMag/Rapozine, http://www.UniversalThread.com/Spanish/Magazine) Por Héctor Correa. Este artículo describe cómo programar informes flexibles usando Tablas Dinámicas de Excel y Visual FoxPro. Con Tablas Dinámicas los usuarios finales pueden rotar filas y columnas (de allí el título de este artículo) para inspeccionar datos desde diferentes aspectos. La mayoría de los usuarios están muy familiarizados con Excel y, por lo tanto, el tiempo de entrenamiento se reduce considerablemente. Con Visual FoxPro se puede consolidar datos desde múltiples fuentes (tablas VFP, SQL Server y/o Oracle) y automatizar la creación de estas tablas.

Introducción a Tablas Dinámicas de Excel Una tabla dinámica es un tipo especial de tabla de Excel donde usted puede rotar filas y columnas para ver datos desde diferentes perspectivas. Se usan tablas dinámicas porque permiten sumarizar grandes cantidades de datos simplemente arrastrando y soltando campos dentro de sus filas y columnas. En su forma más simple, la tabla dinámica se ve como lo muestra la imagen siguiente. Como puede ver, está compuesta por cuatro elementos básicos: campos de filas, campos de columnas, items de datos y campos de página.

La imagen siguiente muestra una tabla dinámica con datos tomados desde la base de datos TestData que viene con VFP 7.

En este ejemplo puede ver ventas por año/mes y país. Note que los campos en los encabezados de filas y columnas son, de hecho, controles drop-down. Esto significa que cuando hace clic en ellos, la lista desplegable le permite seleccionar los valores que quiere mostrar (filtro) en la tabla dinámica. En este caso particular, yo seleccioné sólo los meses de Abril y Mayo de 1996 para cuatro países. Una copia de la planilla Excel con este ejemplo se encuentra en PT_TESTDATA.XLS. Uno de los aspectos más excitantes de estas tablas es precisamente que sean dinámicas. Esto significa que usted puede reestructurar los campos mostrados como filas, columnas e items de datos para obtener distintas vistas de los mismos datos. Por ejemplo, la siguiente imagen muestra los mismos datos de la anterior. Sin embargo, en este caso arrastré el campo "empleado" al área de fila. Observe como la tabla dinámica automáticamente reestructuró la información por empleado.

Otra interesante característica de las tablas dinámicas es su capacidad de drill-down. Usted puede hacer doble-clic en una celda en particular y Excel muestra los registros específicos que están detrás de la suma mostrada en la tabla dinámica. Por ejemplo, con doble-clic en la fila 1996/4, Andrew Fuller -para la columna Canada- muestra el detalle de dos ventas que constituyen los 1014 dólares mostrados en la tabla dinámica.

Hay muchas otras facilidades que vienen con las tablas dinámicas. Por ejemplo, puede crear Gráficos Dinámicos. Estos gráficos se asocian con la tabla dinámica, y se puede filtrar la información que se grafica de la misma forma (es decir por medio de las listas desplegables). Puede crear también campos calculados (p. ej. sumas, cuentas, promedios) en su tabla dinámica. Además puede formatear sus tablas dinámicas para darles un aspecto profesional usando varios estilos que vienen incorporados con Excel. Aunque las tablas dinámicas son muy flexibles y poderosas, usted todavía podría preguntarse por qué considerar las facilidades de Excel para generar informes, en vez de usar el generador de reportes de Visual FoxPro o cualquiera de terceras partes. Hay dos

fuertes razones que hacen a las tablas dinámicas mejor candidato que otras tecnologías para entregar informes a usuarios. Primero, como Tom Bellmer (http://www.visionds.net/hcorrea) mencionó en su artículo, la mayoría de las personas ya tiene Excel y sabe usarlo. Permitirles a sus usuarios crear informes dinámicos con una herramienta con la que se sienten seguros es algo que ciertamente apreciarán. Segundo, dado que las tablas dinámicas son tan flexibles, los usuarios pueden personalizar sus propios informes simplemente arrastrando y soltando campos. Pueden bajar al nivel de detalle que necesitan haciendo doble-clic en las celdas. Adicionalmente, pueden crear gráficos usando uno de los motores de graficación más conocidos disponible para usuarios finales. El resultado final es que hay una reducción importante en el tiempo empleado para crear informes y un incremento de la flexibilidad para representar y analizar datos.

Programando Tablas Dinámicas de Excel con Visual FoxPro Hay básicamente dos formas de crear tablas dinámicas en Excel. La primera es usando Excel interactivamente. Para ello, use el Asistente de Tablas Dinámicas que está en el sector Datos del menú principal de Excel. El segundo mecanismo es programáticamente mediante OLE-Automation. Este es el mecanismo que describiré en este artículo. Antes de entrar en detalles sobre cómo crear tablas dinámicas programáticamente, echemos un vistazo al proceso general que involucra crear una tabla dinámica. Este proceso puede ser dividido en tres pasos básicos: 

 

Primero, usted toma algunos datos guardados en un formato normalizado y crea una tabla temporal con una versión desnormalizada (archivo plano) de esta información. Segundo, somete estos datos desnormalizados a Excel y crea la tabla dinámica. Tercero, una vez que la tabla dinámica se ha creado, usted (o sus usuarios finales) sólo necesita repartir con Excel el archivo que creó. Desde ese momento, usted (o sus usuarios finales) no necesita estar conectado a la base de datos normalizada ni a la tabla desnormalizada.

La imagen siguiente muestra este proceso:

Paso 1. De datos normalizados a desnormalizados En sistemas bien diseñados, los datos se guardan en tablas normalizadas. Para crear una tabla dinámica, antes necesita crear una versión desnormalizada de su base de datos. Aunque esta desnormalización podría parecer un poco ineficiente, el hecho es que cualquier herramienta de reportes (incluso el Generador de Reportes de VFP) requiere que estos datos desnormalizados sean creados implícita o explícitamente. Digamos, por ejemplo, que usted tiene una base de datos como la expuesta en el siguiente diagrama. Este es el diagrama de la base de datos TestData que viene con VFP 7.

El proceso de crear una tabla desnormalizada es usualmente muy sencillo. Comienza identificando todos los campos de todas las tablas que desea incluir en la tabla dinámica. Por ejemplo, si usted quiere mostrar información por cliente, necesita incluir el nombre del cliente (customer.company) en esta tabla desnormalizada. Así mismo, si

quiere mostrar información por empleado necesita incluir el nombre del empleado (employee.first_name) asociado a cada registro. En general necesita traer todos los nombres descriptivos a la tabla desnormalizada y omitir las claves primarias. Una vez identificada la información que desea incluir en la tabla dinámica, usted escribe un programa en VFP para extraer esta información y guardarla en una tabla VFP. El siguiente fragmento de código muestra como puede crear una tabla desnormalizada para las tablas expuestas antes (el código completo se encuentra en GETDATA.PRG): select customer.company, ; customer.region, ; customer.country, ; orders.order_date, ; employee.last_name as employeelast, ; products.eng_name as "product", ; orditems.quantity, ; orditems.quantity * orditems.unit_price as linetotal; from customer ; inner join orders on customer.cust_id = orders.cust_id ; inner join employee on orders.emp_id = employee.emp_id ; inner join orditems on orders.order_id = orditems.order_id ; inner join products on orditems.product_id = products.product_id ; into table data\pt_testdata

La siguiente imagen muestra un fragmento de los datos que se generarán con la sentencia SQL anterior. Note cómo los campos company name, employee y product ahora están desnormalizados.

En el ejemplo anterior, para crear la tabla desnormalizada se necesita una sola sentencia SQL Select. En aplicaciones de la vida real, sin embargo, el proceso puede ser más complejo y requerir varias sentencias SQL Select (potencialmente desde varias bases de

datos) más una serie de etapas para "masajear" los datos hasta que tengan toda la información que nosotros eventualmente necesitaremos en la tabla dinámica. Con sus capacidades de manipulación de datos, es fácil ver como VFP se ajusta bien a esta tarea. Tenga en mente que, aunque la tabla desnormalizada sea una tabla VFP, no es necesario que la fuente de datos sea VFP. Puede usar VFP para obtener datos desde cualquier base de datos (ej. SQL Server u Oracle) vía SQL Pass Through o vistas remotas. Hay otros caminos que puede tomar para crear esta tabla desnormalizada y algunos son aun más poderosos que la técnica usada en este artículo. Un mecanismo común para encarar esta tarea es mediante paquetes OLAP (On Line Analytical Processing) como el que viene incluido en SQL Server. Estos mecanismos están fuera del alcance de este artículo. No obstante, usted debería recordar que cuando pasa a paquetes OLAP no solamente incrementa las capacidades de tabla dinámica, sino también el precio y la complejidad de la solución.

Paso 2. Creando la tabla dinámica Una vez que tiene sus datos desnormalizados, hay básicamente dos métodos que puede seguir para crear la tabla dinámica. Una opción es copiar sus datos a una hoja dentro de un libro Excel y crear la tabla dinámica usando estos datos. Aunque esta aproximación trabaja bien en la mayoría de los casos, tiene una limitación importante: solamente puede importar 65,000 registros a una hoja de cálculo Excel. Un ejemplo de cómo crear una tabla dinámica usando esta aproximación se encuentra en el programa CREATEPT1.PRG. La otra opción que usted puede seguir es crear la tabla dinámica desde una fuente de datos externa (en vez de una hoja de cálculo Excel). En este caso, usted pide a Excel que lea los datos desde una tabla VFP vía ODBC u OLE-DB. Excel a su vez lee estos datos hacia una caché interna (pero sin mostrar los datos en la hoja) y entonces crea la tabla dinámica. Esta es la técnica usada en este artículo. Es muy fácil crear una tabla dinámica desde VFP. Básicamente, sólo necesita usar OLEAutomation para instruir a Excel para que cree la tabla dinámica por usted. El siguiente fragmento de código muestra cómo crear la tabla dinámica programáticamente (el código completo se encuentra en CREATEPT2.PRG). * 1. Correr Excel vía OLE-Automation. oExcel = createobject("excel.application") oExcel.Application.Visible = .T. * 2. Crear un nuevo libro. oWorkbook = oExcel.Workbooks.Add() * 3. Definir un objeto range para volcar los resultados. oTargetSheet = oWorkbook.Sheets.Add() oTargetRange = oTargetSheet.range("A2") * 4. Definir la cadena de conección ODBC y la sentencia SQL * que Excel usará para leer los datos. dimension aSource[2]

aSource[1] = "Driver={Microsoft Visual FoxPro Driver};" +; "SourceDB=" + DATAPATH + ";SourceType=DBF;" aSource[2] = "select * from pt_testdata" * 5. Crear el objeto tabla dinámica de Excel. oPivotTable = oExcel.Sheets[1].PivotTableWizard( 2,; && external data. @aSource, oTargetRange, "PivotTable", .T., .T. ) * 6. Definir cómo se organizarán inicialmente los datos dinámica. oPivotTable.PivotFields("country").orientation = 1 && oPivotTable.PivotFields("yearmonth").orientation = 2 && oPivotTable.PivotFields("linetotal").orientation = 4 &&

en la tabla row column data

Note cómo en el paso 5 estamos usando un método de Excel llamado PivotTableWizard para crear la tabla dinámica. El parámetro aSource indica a Excel que lea los datos usando la conección ODBC y la sentencia SQL que hemos definido en el paso 4. Aunque el Asistente de tabla dinámica simplifica el proceso de creación de la tabla dinámica, ello no le permite ver como organiza Excel internamente las tablas dinámicas. La imagen siguiente, tomada del archivo de ayuda de Microsoft Excel Visual Basic Reference, muestra los principales objetos usados por las tablas dinámicas.

Para crear una tabla dinámica, Excel primero lee los datos hacia un objeto interno (e invisible) llamado PivotCache. La parte actualmente visible de la tabla dinámica es el objeto PivotTable. Dentro de la tabla dinámica hay objetos PivotFields (que representan campos guardados en la fuente de datos) y objetos PivotFormulas (que pueden ser usados para representar campos calculados). * 1. Correr Excel vía OLE-Automation. oExcel = createobject("excel.application") oExcel.Application.Visible = .T. * 2. Crear un nuevo libro. oWorkbook = oExcel.Workbooks.Add() * 3. Definir un objeto range para volcar dentro los resultados. oTargetSheet = oWorkbook.Sheets.Add() oTargetRange = oTargetSheet.range("A2")

* 4. Crear un objeto pivot cache. oPivotCache = oWorkbook.PivotCaches.Add( 2 ) && external data * 5. Establecer en el objeto pivot cache el OLE-DB provider * y la sentencia SQL que Excel usará para leer los datos. oPivotCache.Connection = "OLEDB;Provider=vfpoledb.1;data source=" + DATAPATH oPivotCache.Commandtext = "select * from pt_testdata" * 6. Pedir al objeto pivot cache que cree la tabla dinámica * con los datos. oPivotTable = oPivotCache.CreatePivotTable( oTargetRange, "PivotTable" ) * 7. Definir cómo se organizarán inicialmente los datos dinámica. oPivotTable.PivotFields("country").orientation = 1 && oPivotTable.PivotFields("yearmonth").orientation = 2 && oPivotTable.PivotFields("linetotal").orientation = 4 &&

en la tabla fila columna datos

Los primeros tres pasos en este ejemplo son idénticos al ejemplo anterior. Sin embargo, en los pasos 4 a 6 crearemos manualmente los objetos pivot cache y pivot table en vez de usar el Asistente de tabla dinámica. Haciendo esto ahora podremos pedir a Excel que lea los datos usando un OLE-DB provider en vez de ODBC. Aunque el uso de un OLE-DB provider sobre un driver ODBC puede a simple vista no parecer una ventaja, es ciertamente una mucho mejor estrategia si ud. considera que esto evita totalmente que Excel intente usar Microsoft Query para leer los datos en el PivotCache. Microsoft Query es un componente adicional que no todos los usuarios de Excel tienen instalado y que es bastante difícil de configurar programáticamente.

Paso 3. Usando la tabla dinámica Una vez que la tabla dinámica ha sido creada y guardada en un archivo Excel, le toca a usted decidir qué hacer con el mismo. Puede enviarlo por e-mail a sus usuarios o ponerlo en un lugar compartido donde ellos puedan accederlo. Recuerde que una vez creada la tabla dinámica, sus usuarios no necesitarán más su copia de los datos desnormalizados ya que Excel guarda su propia copia de los datos como parte del objeto PivotCache. Además, dado que los datos son guardados internamente en el archivo Excel, los usuarios seguirán teniendo las capacidades drill-down al usar la tabla dinámica, ¡aunque no estén conectados a la base de datos real ni a la tabla desnormalizada! Como puede ver, la tarea se reduce a entregar un solo archivo Excel a los usuarios. Puede agregar alguna funcionalidad extra al archivo Excel usando Visual Basic for Applications (VBA). Por ejemplo, puede crear formularios (Microsoft Forms) en los archivos Excel con command buttons y checkboxes. Este tema está fuera del alcance de este artículo, pero hay muchos libros al respecto.

Allanando el camino

Con este artículo se incluye un set de clases que encapsulan el acceso a las tablas dinámicas de Excel. La imagen siguiente muestra la jerarquía de estas clases. La única clase que usted necesita instanciar es la PivotTable. Esta clase llamará automáticamente a las otras.

El siguiente fragmento de código muestra cómo usar la clase PivotTable para crear una tabla dinámica para la tabla VFP llamada PT_TESTDATA.DBF (el código completo está en PT_SAMPLE.PRG y PT_CLASS.PRG). oPT = createobject("pivottable" ) oPT.cSourceDBF = "c:\efox_pivot\data\pt_testdata.dbf" oPT.cTargetXLS = "c:\efox_pivot\data\pt_testdata.xls" oPT.Create() oPT.SetField( "country", "row" ) oPT.SetField( "yearmonth", "column" ) oPT.SetField( "linetotal", "data" ) oPT.Save()

Como puede ver, esta clase simplifica bastante el proceso de crear la tabla dinámica ya que usted no necesitará entenderse con Excel directamente, sino con una interfaz mucho más simple. Dado que se provee el código fuente de esta clase, puede extenderlo y adaptarlo a sus necesidades particulares.

Tablas Dinámicas de Excel y sus Usuarios Las Tablas Dinámicas son una poderosa herramienta que usted puede proveer a sus usuarios para ayudarlos a analizar datos desde múltiples ángulos. Uno de los beneficios de esto es que, dado que muchos usuarios están familiarizados con Excel, el tiempo de entrenamiento puede ser reducido drásticamente sin sacrificar flexibilidad. Una ventaja adicional de usar tablas dinámicas es la reducción en el número de hits a la base de datos activa, al dar a los usuarios información de consulta desde un set de datos fuera de línea. Los usuarios remotos y móviles apreciarán también las capacidades fuera de línea.

Finalmente, al dar a los usuarios esta flexible herramienta, puede reducir sus pedidos de nuevos reportes. Con las tablas dinámicas los usarios pueden crear fácilmente esos reportes, y también gráficos, por sí mismos, simplemente arrastrando datos a filas y columnas. No hay nada más fácil que esto.