This is the documentation page for citql. citql are tags that you add to your SQL to create interactivity. As the name implies, the tags provide a clear view of the table and column your are creating interactivity for.
If you have questions, or require assistance at any time, please raise a support ticket and we are here to assist.
citql tags allow you to add interactivity to your dashboards, reports, charts, and maps.
Simply add the tags and use the desired table and column names.
This will identity GEOM columns of table. This will only used in map report.
#Map
SELECT {citqlGEOMCol|ST_AsGeoJSON(GEOMCOL1},COL1,COL2
FROM TABLE1
This will identity GEOG columns of table. This will only used in map report.
#Map
SELECT {citqlGEOGCol|ST_AsGeoJSON(GEOGCOL1},COL1,COL2
FROM TABLE1
This will identity latitude and longitude columns of table. This will only used in map report.
#Map
SELECT {citqlLatCol|COLLAT},{citqlLngCol|COLLNG},COL1,COL2
FROM TABLE1
This will identity latitude and longitude columns of table. This will only used in map report.
#Map
SELECT {citqlLatCol|COLLAT},{citqlLngCol|COLLNG},COL1,COL2
FROM TABLE1
This will provide Column filter in report, chart and map.
#Table Report Sample Query
SELECT T1.COL1, T2.COL2
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.ID=T2.ID
WHERE {citqlUnique|TABLE1|T1.COL1|all}
#Pie Chart Sample Query
SELECT SUM(COL1), COL2
FROM TABLE1
WHERE {citqlUnique|TABLE1|T1.COL2|all}
#Map
#Replaced all value with vale of COL1
SELECT {citqlLatCol|COLLAT}, {citqlLngCol|COLLNG},COL1,COL2
FROM TABLE1
WHERE {citqlUnique|TABLE1|COL1|'V1'}
This will add date filter in report, chart and map.
# Table Report Sample Query
SELECT T1.COL1, T2.COL2
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.ID=T2.ID
WHERE DATECOL1 > {citqlDate|Y-m-d}
#Chart Sample Query
SELECT SUM(COL1), COL2
FROM TABLE1
WHERE DATECOL1 > {citqlDate|Y-m-d}
#Map
SELECT {citqlLatCol|COLLAT}, {citqlLngCol|COLLNG},COL1,COL2
FROM TABLE1
WHERE DATECOL1 > {citqlDate|Y-m-d}
This will add date range filter in report, chart and map.
# Table Report Sample Query
SELECT T1.COL1, T2.COL2
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.ID=T2.ID
WHERE DATECOL1 > {citqlStartDate|Y-m-d} AND DATECOL1 < {citqlEndDate|Y-m-d}
#Chart Sample Query
SELECT SUM(COL1), COL2
FROM TABLE1
WHERE DATECOL1 > {citqlStartDate|Y-m-d} AND DATECOL1 < {citqlEndDate|Y-m-d}
#Map
SELECT {citqlLatCol|COLLAT}, {citqlLngCol|COLLNG},COL1,COL2
FROM TABLE1
WHERE DATECOL1 > {citqlStartDate|Y-m-d} AND DATECOL1 < {citqlEndDate|Y-m-d}
This will combined line and bar chart.This will only used for bar Chart only.
# Bar Chart Sample Query
SELECT COL1, SUM(COL2), {citqlLineChart|avg(COL2)}
FROM TABLE1
Identify global filter column.
#Table Report Sample Query
SELECT T1.COL1, T2.COL2
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.ID=T2.ID
WHERE {citqlGlobalUnique|T1.COL1}
#Pie Chart Sample Query
SELECT SUM(COL1), COL2
FROM TABLE1
WHERE {citqlGlobalUnique|COL1}
#Map
SELECT {citqlLatCol|COLLAT}, {citqlLngCol|COLLNG},COL1,COL2
FROM TABLE1
WHERE {citqlGlobalUnique|COL1}
Identify global date filter column.
#Table Report Sample Query
SELECT T1.COL1, T2.COL2
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.ID=T2.ID
WHERE {citqlGlobalDate|T1.DATECOL|Y-m-d|>}
#Pie Chart Sample Query
SELECT SUM(COL1), COL2
FROM TABLE1
WHERE {citqlGlobalDate|DATECOL|Y-m-d|=}
#Map
SELECT {citqlLatCol|COLLAT}, {citqlLngCol|COLLNG},COL1,COL2
FROM TABLE1
WHERE {citqlGlobalDate|DATECOL|Y-m-d|<}
Identify global start and end date filter column.
#Table Report Sample Query
SELECT T1.COL1, T2.COL2
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.ID=T2.ID
WHERE {citqlGlobalStartDate|T1.DATECOL|Y-m-d} AND {citqlGlobalEndDate|T1.DATECOL|Y-m-d}
#Pie Chart Sample Query
SELECT SUM(COL1), COL2
FROM TABLE1
WHERE {citqlGlobalStartDate|DATECOL|Y-m-d} AND {citqlGlobalEndDate|DATECOL|Y-m-d}
#Map
SELECT {citqlLatCol|COLLAT}, {citqlLngCol|COLLNG},COL1,COL2
FROM TABLE1
WHERE {citqlGlobalStartDate|DATECOL|Y-m-d} AND {citqlGlobalEndDate|DATECOL|Y-m-d}