Funciones especiales para reportes

En esta sección se podrá encontrar información sobre funciones especiales para creación de reportes

Los reportes con grandes cantidades de registros pueden tardar mucho tiempo en la generación del informe o incluso verse interrumpidos cuando el tiempo de procesamiento excede los parámetros del sistema. En este sentido se recomiendo hacer uso de las siguientes variables:

• FECHADESDE: Corresponde a la fecha inicial del rango con el cual se va a consultar.
• FECHAHASTA: Corresponde a la fecha final del rango con el cual se realiza la consulta.

Las variables en el sistema toman como valor por defecto un rango de una semana, es decir, FECHAHASTA corresponde al día actual (equivalente al now()) y FECHADESDE corresponde a 7 días anteriores a la fecha actual (equivalente al now()-7). Estas variables luego cambiarán en el Dashboard cuando se haga uso del calendario tomando las fechas que se seleccionen.

Ejemplo de uso

Para usar las variables de filtrado se usan las llaves( {{}} )

SELECT caso.REFNUM,
       caso.FECHACREACION
FROM caso
WHERE caso.FECHACREACION BETWEEN {{FECHADESDE}} AND {{FECHAHASTA}}

Las fechas en el sistema se almacenan en formato UTC, adicionalmente la visualización es similar a un string, por lo que es importante considerar un par de funciones para mejorar el uso de fechas en los reportes.

Nota: Resulta muy importante considerar la conversión de las fechas al UTC de la región correcta especialmente cuando se usan como filtro para la consulta, pues así nos estaremos asegurando que estamos tomando los registros dentro del rango correcto.

La función Convert_TZ es propia de mysql y permite convertir una fecha desde una zona horaria a otra diferente. Un ejemplo de uso es el siguiente, donde la fecha actual se convierte de la zona horaria UTC a 'América/Bogota'

SELECT NOW(), CONVERT_TZ(NOW(),'UTC','America/Bogota')

La función GET_VALUE permite obtener el valor correspondiente a la zona horaria especificada en el sistema, el cual requiere el parámetro 'TIME_ZONE'. Su uso es el siguiente:

SELECT GETVALUE('TIME_ZONE')

La función DATE_FORMAT permite darle un formato a las fechas mediante comodines: Año (%Y), Mes (%m), Día(%d), Hora (%H), Minutos (%i), Segundos (%s). Se usa de la siguiente forma:

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

El resultado será : 2020-07-29 19:17:32

Las tres funciones anteriores se pueden combinar con el fin de tener la visualización de las fechas de forma correcta o para definir el filtro correctamente en el WHERE. Un ejemplo de esto sería:

SELECT caso.FECHACREACION,
       DATE_FORMAT(caso.FECHACREACION, '%Y-%m-%d %H:%i:%s') AS 'FECHA CON FORMATO',
       GETVALUE('TIME_ZONE') AS 'ZONA HORARIA',
       DATE_FORMAT(CONVERT_TZ(caso.FECHACREACION,'UTC',GETVALUE('TIME_ZONE')), '%Y-%m-%d %H:%i:%s') AS 'FECHA CONVERTIDA'
FROM caso 

El anterior código ejecutado nos arroja algo parecido a lo siguiente:

Los campos personalizados pueden ser creados y asociados casos, cuentas, contactos y usuarios, de tal forma que se pueden añadir datos adicionales a los que el formulario estándar permite, como por ejemplo agregar la edad del contacto, el número de hijos de un usuario, la placa\matrícula\patente de un vehículo para un caso, entre otros. Todos estos datos, conocidos como Custom Fields (CF) se encuentran en la tabla del objeto (caso, contacto, cuenta, usuario) y se almacenan en formato JSON. Por ejemplo al obtener la información del caso 200501-000001 (un caso de prueba) podemos ver que la columna CF contiene los datos personalizados que se ingresaron al ticket.

La consulta normal sería:

 SELECT caso.REFNUM, caso.CF
FROM caso
where caso.REFNUM = '200501-000001'

Y el resultado entregado por el sistema se vería de la siguiente forma:

Los datos son visibles, pero en algunos casos se requiere que además los campos en CF se encuentren separados por columnas; la función JSON_EXTRACT de SQL permite obtener los datos puntales del campo CF recibiendo como parámetro el nombre del campo en la tabla ( por ejemplo caso.CF) y el nombre del campo personalizado (Ejemplo “$.guia”).

La consulta anterior modificada quedaría de la siguiente manera:

 SELECT caso.REFNUM, 
 JSON_EXTRACT(caso.CF, "$.guia") AS "Guia",
 JSON_EXTRACT(caso.CF, "$.nombre")  AS "Nombre",
 JSON_EXTRACT(caso.CF, "$.hobbie_principal") as "Hobbie Principal"
FROM caso
where caso.REFNUM = '200501-000001'

Ahora el resultado entregado por el sistema se verá así:

Los datos guardados en campos personalizados que contengan acentos pueden visualizarse de forma incorrecta al momento de llevarlos a un reporte. La siguiente sentencia SQL obtiene los datos de un caso cuyos CF contienen datos con acentos:

 SELECT caso.REFNUM, 
 JSON_EXTRACT(caso.CF, "$.guia") AS "Guia",
 JSON_EXTRACT(caso.CF, "$.nombre")  AS "Nombre",
 JSON_EXTRACT(caso.CF, "$.hobbie_principal") as "Hobbie Principal"
FROM caso
where caso.REFNUM ="200724-000001"

El resultado arrojado es el siguiente:

Con el fin de dar un formato adecuado a los textos con acentos se usa la función de SQL CONVERT usando además el parámetro UTF8, de esta forma: CONVERT(Texto using UTF8). El siguiente ejemplo usa la función CONVERT en la consulta anterior para corregir el texto y mostrar correctamente el acento:

 SELECT caso.REFNUM, 
 JSON_EXTRACT(caso.CF, "$.guia") AS "Guia",
 CONVERT(JSON_EXTRACT(caso.CF, "$.nombre") using UTF8)  AS "Nombre",
 CONVERT(JSON_EXTRACT(caso.CF, "$.hobbie_principal") using UTF8) as "Hobbie Principal"
FROM caso
where caso.REFNUM ="200724-000001"

Ahora el resultado mostrado en el reporte será el siguiente: