Magia Potagia II: El desenlace

En el capítulo anterior habíamos usado ingredientes mágicos de Access 2007 para conseguir crear una serie de calendarios sin apenas usar código. Hoy se trata de licenciarnos como aprendices de brujo haciendo que la magia trabaje para nosotros resolviendo problemas de la vida real.

Necesitaba crear un subinforme que, para cada uno de los elementos del informe principal, me mostrara un conjunto de fechas representadas en tantos calendarios mensuales como fueran necesarios para mostrar todo el periodo entre la primera y la última. Más o menos, como en las imágenes.

Evidentemente, ya lo he hecho y el caso real lleva tiempo funcionando correctamente. Hay una demo, la de las imágenes, que se puede descargar en Access siglo XXI.

La magia en la vida real tiene un problema: que no hay quien entienda cómo funciona. Lo mágico de estos calendarios es que apenas usan código, pero el código se puede leer, está estructurado, se autodocumenta; es como un plano de la aplicación. Sin código es muy difícil ver cómo está hecho algo, tanto que, para que el que quiera pueda adaptar mi ejemplo, he tenido que incluir un pequeño asistente.

No obstante, voy a intentar explicar cómo está construido, aunque me temo que será bastante pesado. Cuando empieces a aburrirte, salta directamente al último punto, al desenlace.

El punto de partida es una tabla con un campo de fecha y un campo ID numérico, en este caso de usuario, es decir, con un conjunto de fechas, que son las que queremos mostrar en calendario, para cada usuario.

El idEmpleado es númerico y si se muestran nombres en la imágen es porque uso un campo de búsqueda.

En el ejemplo del capítulo anterior utilizaba una tabla tipo Calendar, con campos StartDate y EndDate, y mediante una consulta, la convertía en un resultado parecido a éste. Sin embargo, aunque no lo decía, trabajar directamente con la consulta afectaba mucho al rendimiento y sería preferible volcar los resultados en una tabla temporal.

Obtener todas las fechas de cualquier año

El primero de los ingredientes mágicos , es una tabla Numeros, con un campo Num con número consecutivos. La mía tiene poco más de mil.

A partir de esta tabla, mediante una consulta construimos todas las fechas que se corresponden con las del calendario “de fondo”

SELECT [Numero]+DateSerial(nz(TempVars!jbAnyoInicial,Year(Date())),1,1)-1 AS Fecha, Format([Numero]+DateSerial(nz(TempVars!jbAnyoInicial,Year(Date())),1,1)-1,"dddd") AS Dsemana, (Month([Numero]+DateSerial(nz(TempVars!jbAnyoInicial,Year(Date())),1,1)-1)) AS Mes, (1+(([Numero]+DateSerial(nz(TempVars!jbAnyoInicial,Year(Date())),1,1)-1-2)7)) AS Fila, Year([Numero]+DateSerial(nz(TempVars!jbAnyoInicial,Year(Date())),1,1)-1) AS Anyo
FROM Numeros
WHERE (((Year([Numero]+DateSerial(nz([TempVars]![jbAnyoInicial],Year(Date())),1,1)-1))=nz([TempVars]![jbAnyoInicial],Year(Date()))));

Mediante el procedimiento de sumar al campo Num una fecha inicial menos uno y hacer que sea menor o igual que una fecha final, podemos obtener cualquier rango de fechas que no supere el total de registros de nuestra tabla Numeros. Pero en el informe podemos superar este límite usando otro de los ingredientes mágicos de Access 2007, TempVars. Al utilizar una variable TempVars en el cálculo de la fecha inicial, podemos cambiar ésta sobre la marcha en el informe de manera que la consulta se vaya recalculando las veces que sea necesario. Ya nos pararemos en ello más adelante; de momento, guardamos esa consulta como jbFechasVirtuales y los resultados sería algo así:

Resaltar los festivos de cada empleado

Sobre ese “calendario de fondo” se trata de resaltar las fechas de nuestra tabla tblFechasEmpleados usando el tercer ingrediente mágico de A2007, el formato de texto enriquecido. Comparamos tabla y consulta y en las fechas coincidentes añadimos las etiquetas html necesarias para mostrar el resultado resaltado, en nuestro caso en rojo, tal como habíamos visto en el capítulo anterior.

Sin embargo, antes vamos a anticiparnos con un problema con el que nos toparíamos más adelante en el caso real. El objetivo es presentar los calendarios como subinformes, pero si construimos y formateamos como calendarios todo el conjunto de datos, al vincular por un campo ID informe y subinforme y, por tanto, filtrar los datos de éste, destrozamos el formato de calendario que hemos dado a todo el conjunto. En resumen, que debemos dar forma de calendario a cada subconjunto en vez de filtrar el conjunto de datos total y para ello la mejor solución que se me ha ocurrido es volver a usar TempVars de manera que, cambiando sobre la marcha la variable TempVars!jbID cada vez que cambie el ID del informe principal se recalcula la consulta en la que se basa el subinforme calendario filtrándose por ese ID.

Así, en vez de usar directamente la tabla tblFechasEmpleados, usaré la consulta que he llamado jbqFestivosVirtuales y que tiene el siguiente SQL.

SELECT tblFechasEmpleados.idEmpleado, tblFechasEmpleados.Fecha
FROM tblFechasEmpleados
WHERE (((tblFechasEmpleados.idEmpleado)=[TempVars]![jbID]));

Ahora ya podemos juntar las dos últimas consultas para resaltar con formato de texto enriquecido los festivos de los empleados.

SELECT jbqFechasVirtuales.*, IIf(Not IsNull([jbqFestivosFiltrados].Fecha),"<font color=red>" & Day(jbqFechasVirtuales.fecha) & " </font>",Day(jbqFechasVirtuales.fecha)) AS Expr1
FROM jbqFechasVirtuales LEFT JOIN jbqFestivosFiltrados ON jbqFechasVirtuales.Fecha = jbqFestivosFiltrados.Fecha
ORDER BY IIf(Not IsNull([jbqFestivosFiltrados].Fecha),"<font color=red>" & Day(jbqFechasVirtuales.fecha) & " </font>",Day(jbqFechasVirtuales.fecha));

Guardamos la consulta como jbFechasyFestivosVirtuales y el resultado es como en la imagen siguiente (hasta 365 días) donde destaca un campo que en ocasiones pone algo como <font color=red>10</font> con el que más adelante conseguirems que el número 10 se muestre en rojo.

Ya tenemos una secuencia con todas las fechas del año para un determinado usuario con los festivos formateados para que más adelante se muestren en rojo. Para que se parezca a un calendario falta ordenarlo en columnas por días de la semana.

Dar a los datos forma de calendario

Ya habíamos visto en el capítulo anterior que para ordenar en columnas por días de la semana una secuencia de fechas podemos usar una Consulta de Referencias Cruzadas que voy a llamar jbqCalendarioFormateado y que será el origen de datos del subinforme calendario:

TRANSFORM Min([jbqFechasyFestivosVirtuales].Expr1) AS MínDeExpr1
SELECT [jbqFechasyFestivosVirtuales].Mes, [jbqFechasyFestivosVirtuales].Anyo, [jbqFechasyFestivosVirtuales].Fila
FROM jbqFechasyFestivosVirtuales
GROUP BY [jbqFechasyFestivosVirtuales].Mes, [jbqFechasyFestivosVirtuales].Anyo, [jbqFechasyFestivosVirtuales].Fila
PIVOT [jbqFechasyFestivosVirtuales].Dsemana In ("lunes","martes","miércoles","jueves","viernes","sábado","domingo");

El resultado, algo parecido a esto:

El subinforme calendario

Es pequeñito, para que me quepan más calendarios en una hoja, que hay que ahorrar papel,

El Origen de Datos es la consulta jbaCalendarioFormateado y el truco principal es que los campos Lunes, Martes, Miércoles … están ocultos (en la imagen en amarillo) y sobre ellos se superponen sendos campos calculados dLunes, dMartes, dMiércoles… que tienen por origen el campo que está debajo. Al ser campos calculados, pueden tener formato de Texto enriquecido, que no es posible en campos de texto, de manera que el campo que antes habíamos construido con etiquetas html del estilo <font color=red>10</font>, ahora se mostrá como 10.

También están ocultos los campos Mes y Anyo, que nos van a servir para vincular con el subinforme MesesAnyos.

Este subinforme no tiene ni una sola línea de código.

El subinforme Meses-Año

El subinforme anterior muestra un solo calendario, pero necesitamos que nos muestre tantos calendarios como sean necesarios en el subinforme. Por eso lo incrustamos en otro subinforme Meses-Año que tiene todos los meses necesarios para mostrar las fechas de cada empleado.

El origen del subinforme es la consulta jbqMesesAnyos y tiene por objetivo obtener tantos registros identificados por mes y año como sean necesarios para mostrar todas las fechas de un empleado:

SELECT ((([Numero]-1)12)) AS Fila, 1+([Numero]-1) Mod 12 AS numMes, MonthName([NumMes]) AS NombreMes, (([Numero]-1)12)+Year(DMin("[Fecha]","jbqFestivosFiltrados")) AS Anyo, DateSerial(((([Numero]-1)12))+Year(nz(DMin("[Fecha]","jbqFestivosFiltrados"))),1+([Numero]-1) Mod (12),1) AS Expr1, [TempVars]![jbID] AS jbID
FROM Numeros
WHERE (((DateSerial(((([Numero]-1)12))+Year(nz(DMin("[Fecha]","jbqFestivosFiltrados"))),1+([Numero]-1) Mod (12),1)) Between nz(DMin("[Fecha]","jbqFestivosFiltrados"))-30 And nz(DMax("[Fecha]","jbqFestivosFiltrados")) And (DateSerial(((([Numero]-1)12))+Year(nz(DMin("[Fecha]","jbqFestivosFiltrados"))),1+([Numero]-1) Mod (12),1))>0));

La consultita se las trae, pero, resumiendo, obtiene los meses y año del subconjunto de fechas de un empleado a partir de la tabla Numeros y usando como filtro las fechas mayor y menor de la consulta jbqFestivosFiltrados. Además, obtiene el campo jbID, por el que se vincula con el informe principal, de la Variable Temporal TempVars!jbID.

En la seccion detalle tenemos el subinforme jbSubRptCalendarioFormateado y, cada vez que se formatea esa sección, cambiamos mediante código la variable temporal TempVars!jbAnyoInicial que, como vimos se usaba en la primera consulta, jbFechasVirtuales, de manera que al cambiarse, esto sí que es magia, se se recalcula toda la consulta y se refresca el subinforme jbSubRptCalendarioFormateado.

Este es el todo código del subinforme:

Option Compare Database
Option Explicit

Private Sub Detalle_Format(Cancel As Integer, FormatCount As Integer)
TempVars!jbAnyoInicial = Me.Anyo.Value
Me.SubRptCalendario.Requery
End Sub

Private Sub Report_Close()
TempVars.Remove ("jbAnyoInicial")
End Sub


Private Sub Report_Open(Cancel As Integer)
TempVars!jbAnyoInicial = Year(DMin("Fecha", "jbqFestivosFiltrados"))
End Sub

El informe principal

En el informe principal incrustamos el subinforme jbSubRptMesesAnyo y necesitamos un campo ID por el que vincular con éste.

Es necesario que tengamos una agrupación por ese campo ID y que ese campo se encuentre en la sección encabezado de ese grupo.

¿Por qué? Por que es la única forma que he conseguido que me funcione el abracadabrante rizado del rizo:

Los campos por los que se vinculan informe y subinforme son respectivamente id y jbid.

En el evento Format de la sección EncabezadoDelGrupo0 le doy a la variable temporal TempVars!jbID el valor de ese campo ID y, como resulta que el campo jbID del subinforme lo toma de esa variable temporal, estoy asignando sobre la marcha y mediante código en el informe principal el valor del campo del subinforme por el que éste se vincula con el informe principal ¡Con dos c…!

Para más magia potagia, cambiar por código TempVars!jbID, como forma parte de la primera consulta, jbFechasVirtuales, hace que ésta se refresque y haga los cálculos sólo para el empleado del ID. Como también está en la consulta jbqFestivosFiltrados y del máximo y mínimo de ésta depende la consulta jbqMesesAnyo, no sólo se restringen los festivos a los del empleado, sino que se recalcula jbqMesesAnyo, que es precisamente el origen del subinforme.

Todo el código necesario es el siguiente:

Option Compare Database
Option Explicit

Private Sub EncabezadoDelGrupo0_Format(Cancel As Integer, FormatCount As Integer)
TempVars!jbID = Me.ID.Value
Me.subRptMesesAnyo.Requery
End Sub

Private Sub Report_Close()
TempVars.Remove ("jbID")
End Sub

Private Sub Report_Open(Cancel As Integer)
TempVars!jbID = DFirst("id", Me.RecordSource)
End Sub

El desenlace

Yo ya advertí que iba a ser aburrido. Resumiendo, usando TempVars y consultas basadas en una tabla Numeros, se pueden hacer cosas inimaginables de otra manera.

Es magia empaquetada. No es necesario entenderla toda ni seguir complicados rituales para usarla en una aplicación propia pues, de todos los objetos y consultas que utiliza sólo es necesario cambiar el informe principal y la consulta jbqFestivosFiltrados. En Access siglo XXI puedes descargarte la aplicación de ejemplo que, además, tiene un asistente elemental para modificar esa consulta y el código del informe principal.

¿De dónde he sacado yo estas cosas de mágia? Ni idea, supongo que andaré hechizado, aunque algo de inspiración se me habrá pegado de Ramón Poch o de Julián Sánchez, aficionados también a jugar con una tabla Numeros.

Por cierto Dominguín iba a las siete de la mañana… ¡A contarlo!