Cómo crear listados personalizados con Filtros

Ya sea porque hemos migrado de una versión anterior o porque hemos descubierto esta característica en Nexus, en muchas implantaciones nos encontramos con la necesidad de crear algún listado adicional a los que nuestro software estándar nos ofrece.

Aquellos con cierto dominio de lenguaje SQL (y concretamente en la variante que el SGBD que Nexus utiliza: MS SQLServer, llamada Transact-SQL) pueden ofrecer al cliente una vista de los datos que les sea útil, usando la personalización de los listados existente o creando otros nuevos. Pero está clara una de las principales ventajas que tiene el software estándar sobre el distribuidor a la hora de realizar un listado: la aplicación pregunta una serie de parámetros de entrada que filtran los datos, desde la misma pantalla en la que obtenemos el listado. Además, Nexus puede hacer cálculos y transformaciones en el momento de obtener los datos que se muestran en el listado, muy importante al realizar la presentación. Sin embargo algunos no habrán caído en la cuenta de que cuando recuperamos la información mediante una sentencia SQL, podemos hacer exactamente lo mismo; ya sea llamando a funciones o procedimientos de SQL-Server como usando toda la expresividad de Transact-SQL. Pero esto se va un poco más allá de lo que pretendemos tratar en este artículo. Centrémonos en la carencia que encuentra cualquiera que se enfrente por primera vez con un nuevo listado. ¿Cómo puedo seleccionar los datos que debo presentar?

Para ello Nexus nos proporciona una pequeña sintaxis de marcas que podemos introducir en nuestras sentencias SQL. Una vez que se pida el listado, se detectará esta marca (o marcas, si hubiera varias) y se presentará al usuario una pantalla en la cual podrá introducir los valores. Una vez aceptados, se sustituirán las marcas por los valores introducidos permitiéndonos seleccionar los datos que el usuario necesita.

Las marcas

Una marca es una cadena de texto entre corchetes que consta de cuatro campos, separados por comas (sin espacios entre valor del campo y comas):

[DescripciónUsuario,Tipo,ValorPorDefecto,RelaciónExterna]

donde cada campo es:

Parámetro	Significado	Obligatorio
Nombre	Descripción que le aparecerá al usuario.	SÍ
Tipo	Tipo del parámetro.	NO
ValoresPorDefecto	Valor o valores por defecto.	NO
RelaciónExterna	Si hace o no referencia a un registro de una tabla.	NO

En profundidad:

Parámetro	Funcionamiento
Nombre	Descripción que le aparecerá al usuario del parámetro que se le pregunta. Debe ser único. Si se repite, el sistema puede presentar (tantas veces como repeticiones) otra vez el diálogo desde el que se preguntan los parámetros.
Tipo	Tipo del parámetro. Hace que la introducción del valor por parte del usuario tenga las restricciones que el tipo impone (Por ejemplo, no se pueden introducir caracteres en un numérico) y evita problemas al tratar de asignar valores fuera del tipo a campos de la base de datos. Los tipos válidos son: BINARY BIGINT BIT DATETIME DECIMAL FLOAT IMAGE INT MONEY IDENTIFICADOR NCHAR NTEXT NVARCHAR REAL SMALLDATETIME SMALLINT SMALLMONEY TEXT TIMESTAMP TINYINT VARBINARY VARCHAR CHAR UNIQUEIDENTIFIER SYSNAME (Valor por defecto) Valor de sistema de uso interno. CUADRADO(X) donde X es un valor entero > 0 Hemos subrayado los tipos usados en las tablas de Nexus y que no deberían dar problemas. Lo recomendable es que se elija el tipo en función del campo con el que se comparará en la sentencia SQL del listado. Nota: CUADRADO(X) fuerza que el valor introducido se comporte como los códigos de Nexus (VARCHAR(X)). Es decir, si es numérico, se alineará a la derecha poniendo tantos espacios como resulten de la resta de X de la longitud del valor introducido. Así, si X = 8 y hemos introducido un «1», el valor quedará »       1″ (un uno precedido de 7 espacios).
Valor por defecto	Cadena que representa el valor que por defecto tendrá el parámetro.
Referencia	Indica que el valor que el usuario debe introducir es un elemento existente en una determinada tabla. Permite introducir el código del elemento o seleccionarlo a partir de un botón lupa.
	(Tabla, Campo, Descripción, Filtro)
	Tabla Tabla de la cual se seleccionará el registro. Campo Campo del cual se devolverá el valor, al seleccionar un elemento de la tabla. Descripción Texto que se dará al campo (sin uso). opcional Filtro Expresión SQL que filtra los elementos para que en la selección solo aparezca un subconjunto de la Tabla determinado. Nota: La expresión no debe contener paréntesis. Triste pero cierto. opcional
	Nota: El hecho de indicar una referencia a una tabla no obliga a que el valor introducido exista en dicha tabla. Así el usuario puede seleccionar (al escribir directamente el valor) un elemento que no exista en la tabla o que no esté incluido en el Filtro de la referencia.

Así, una expresión completa se parecerá a:

[DescripciónUsuario, Tipo, ValorPorDefecto1;ValorPorDefecto2;ValorPorDefectoN,(Tabla,CampoValor,DescripcionCampo,FiltroSQLSobreTabla)]

Los campos opcionales no hace falta indicarlos, pero sí hay que dejar las comas de separación; Ejemplo:

[Valor de prueba,INT,0,]
Preguntará un valor entero con descripción «Valor de prueba» con valor por defecto el numérico 0.
[Valor de prueba,Cuadrado(16),,(ARTICULO,CODART,,CODART > ‘              0’)]
Nos pedirá un valor, que cuadrará a 16 caracteres y permitirá seleccionarlo de los CODART de la tabla de artículos cuyo código de artículo esté por encima del ‘              0’.

Ejemplos

Veamos un ejemplo sencillo:

select *
from clientes
where YEAR(FECALTA) = [Año de alta,INT,2000,]

De sencillo que es, se permite que un usuario de aquellos que jamás tocan nada «rompan» el listado (por ejemplo introduciendo un valor erróneo). Aunque aquí es más bien culpa nuestra.

Intentemos arreglarlo un poco:

-- Esto es un comentario...
-- Declaramos una variable de tipo entero para guardar
-- el año que selecciona el usuario.
declare @Year INT

-- Asignamos a la variable el valor (Nexus sustituirá la marca
-- por el valor y se encargará de la comprobación de tipo)
select @Year = [Año de alta,INT,2000,]
-- Validaciones previas
if @Year < 1980 then begin
 @Year = 2000
end
if @Year > 2100 then begin
 @Year = 2000
end

-- La sentencia con un parámetro que sabemos válido.
select *
from clientes
where YEAR(FECALTA) = @Year

Y ahora veamos un ejemplo en el que mezclamos un poco de juego con el SQL-Server. Al usar las estructuras sintácticas del Transact-SQL, podemos realizar las validaciones y/o transformaciones necesarias para obtener los valores tal y como los necesitamos para nuestro listado:

-- Declaración de variables:
-- Las variables que van a guardar desde que Centro hasta cual restrinjo los datos
-- La última, una variable temporal para hacer un intercambio entre las dos de arriba,
-- si hiciera falta
declare @CodCenIni varchar(8)
declare @CodCenFin varchar(8)
declare @Temp varchar(8)
-- Variable Tabla para hacer un proceso previo a la obtención
declare @temporal TABLE(CentroC varchar(8), DescCen varchar(50), Debe money, Haber money, SaldoAcumulado money)

-- Asignamos a las dos variables los valores que nos haya introducido el usuario.
select @CodCenIni = [Desde centro,cuadrado(8),"       1",(CENTROSC,CentroCoste,,)]
select @CodCenFin = [Hasta centro,cuadrado(8),"ZZZZZZZZ",(CENTROSC,CentroCoste,,)]

-- Que tenga valor por defecto no significa que el usuario no haya podido introducir
-- lo que quiera.
-- Así fuerzo que si el usuario limpió las casillas, siga usando los valores
-- por defecto (que son el menor y mayor posibles).
if ltrim(rtrim(@CodCenIni)) = '' then begin
  select @CodCenIni = '       1'
end
if ltrim(rtrim(@CodCenFin)) = '' then begin
  select @CodCenFin = 'ZZZZZZZZ'
end

-- El usuario puede haber dado los valores desde-hasta al revés.
-- Intercambiémoslo si es así.
if @CodCenFin < @CodCenIni
begin
  -- intercambio de valores (swap) entre las variables  @CodCenIni y @CodCenFin
  select @Temp = @CodCenIni, @CodCenIni = @CodCenFin, @CodCenFin = @Temp
end

-- La select en cuestión. Suma los apuntes agrupados por centro de coste, siempre
-- que estén estos entre los centros de coste que el usuario ha indicado.
INSERT INTO @Actualizar
SELECT A.CentroCoste, C.DescCentro, SUM(A.Debe) DEBE, SUM(A.Haber) HABER, DEBE-HABER SALDO
FROM Apuntes A (NOLOCK)
LEFT JOIN CentrosC C (NOLOCK) on A.CentroCoste = C.CentroCoste
WHERE A.CentroCoste between @CodCenIni and @CodCenFin
group by A.CentroCoste, C.DescCentro
order by A.CentroCoste

update @Actualizar
set SaldoAcumulado = SaldoAcumulado +
                    (select SUM(SaldoAcumulado)
                     from @Actualizar B
                     where B.CentroCoste < @Actualizar.CentroCoste)

-- Lo que finalmente retornamos al listado
-- Además recupero en la select los valores que el usuario ha introducido.
-- Esto puede ser útil para mostrar en el listado algún resumen de selección.
select Datos.*, @CodCenIni CodCenIni, @CodCenFin CodCenFin
from @Actualizar Datos

Y un par de consideraciones finales, cuidado con el uso de comentarios en las sentencias SQL. Si uno contiene una marca de filtrado, Nexus lo preguntará al usuario igualmente. Y si al ejecutar el listado (tras preguntarnos los valores desde el dialogo de filtrado) da un error del estilo «sintaxis incorrecta cerca de AND» o algo parecido, posiblemente en el filtro sobra algún espacio, o hemos puesto algún paréntesis en el filtro…

Por supuesto, las dudas que os surjan por la sentencia SQL en sí (no por las marcas, que es lo único imputable a NEXUS) siempre os las puede responder un manual, los enlaces que hemos puesto al inicio del artículo o en su defecto San Google. El comodín de la llamada a hotline no vale 🙂

Esperamos que con lo visto en los ejemplos y las marcas, tengáis más artillería para enfrentaros con los clientes. Siempre queda, como segundo recurso, montar una DLL que lance vuestros propios informes (programados) o, usando un menú personalizado, llamar a otra aplicación externa que lance el informe (Crystal Reports, Jaspe Reports, iReports, etc…).