SQL

Introduction

This is the documentation page for ClearSQL. ClearSQL 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.

                                

                            

Citql GEOM {citqlGEOMCol|ST_AsGeoJSON(columnName}

This will identity GEOM columns of table. This will only used in map report.

Input Data

ColumnName:
Replace with GEOM column of table

Output Data

Return
Draw Marker, Polygon, Multiline on Map according to data.

                                                                   

                                    #Map           

                                    SELECT {citqlGEOMCol|ST_AsGeoJSON(GEOMCOL1},COL1,COL2 

                                    FROM TABLE1                   

                                

                            

Citql GEOG {citqlGEOGCol|ST_AsGeoJSON(columnName}

This will identity GEOG columns of table. This will only used in map report.

Input Data

ColumnName:
Replace with GEOG column of table

Output Data

Return
Draw Marker, Polygon, Multiline on Map according to data.

                                                                   

                                    #Map           

                                    SELECT {citqlGEOGCol|ST_AsGeoJSON(GEOGCOL1},COL1,COL2 

                                    FROM TABLE1                   

                                

                            

Citql Lat {citqlLatCol|columnName} & Citql Lng {citqlLngCol|columnName}

This will identity latitude and longitude columns of table. This will only used in map report.

Input Data

ColumnName:
Replace with latitude and longitude column name of table

Output Data

Return
Add Marker in map.

                                                                   

                                    #Map           

                                    SELECT {citqlLatCol|COLLAT},{citqlLngCol|COLLNG},COL1,COL2 

                                    FROM TABLE1                   

                                

                            

Citql Lat {citqlLatCol|columnName} & Citql Lng {citqlLngCol|columnName}

This will identity latitude and longitude columns of table. This will only used in map report.

Input Data

ColumnName:
Replace with latitude and longitude column name of table

Output Data

Return
Add Marker in map.

                                                                   

                                    #Map           

                                    SELECT {citqlLatCol|COLLAT},{citqlLngCol|COLLNG},COL1,COL2 

                                    FROM TABLE1                   

                                

                            

Citql Unique {citqlUnique|tableName|columnName|all}

This will provide Column filter in report, chart and map.

Input Data

tableName:
Replace tableName by actual table name
columnName:
Replace columnName by column name of selected table name
all:
Default filter, you can replace with value of one of selected column leave as it if you want to show all values in reports, charts and map

Output Data

Return
Add Multi select Dropdown with values of selected Column in reports, charts 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'}

                                

                            

Citql Date {citqlDate|mm-dd-yyyy}

This will add date filter in report, chart and map.

Input Data

mm-dd-yyyy:
Date format for column

Output Data

Return
Add input box with date picker.

                                

                                    # 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}

                                

                            

Citql Date Range {citqlStartDate|mm-dd-yyyy} and {citqlEndDate|mm-dd-yyyy}

This will add date range filter in report, chart and map.

Input Data

mm-dd-yyyy:
Date format for column

Output Data

Return
Add 2 input box with date pickers.

                                

                                    # 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}                                    

                                

                            

Citql Line Chart {citqlLineChart|columnName}

This will combined line and bar chart.This will only used for bar Chart only.

Input Data

columnName:
Column name of table

Output Data

Return
Add Line chart inside bar chart.

                                

                                    # Bar Chart Sample Query                                      

                                    SELECT COL1, SUM(COL2), {citqlLineChart|avg(COL2)} 

                                    FROM TABLE1                        

                                

                            

Citql Global Unique {citqlGlobalUnique|columnName}

Identify global filter column.

Input Data

columnName:
Replace columnName by column name that you want to filter when select global filter.

Output Data

Return
Filter reports, charts and map by global filter value.

                                

                                    #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}

                                

                            

Citql Global Date {citqlGlobalDate|columnName|dd-mm-yyyy|op}

Identify global date filter column.

Input Data

columnName:
Replace columnName by column name that you want to filter when select global date picker.
dd-mm-yyyy:
Date format of column.
op:
you can use any of operator like >,<,=, >=, <=

Output Data

Return
Filter reports, charts and map by global date value.

                                

                                    #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|<}

                                

                            

Citql Global Date Range {citqlGlobalStartDate|columnName|dd-mm-yyyy} And {citqlGlobalEndDate|columnName|dd-mm-yyyy}

Identify global start and end date filter column.

Input Data

columnName:
Replace columnName by column name that you want to filter when select global date picker.
dd-mm-yyyy:
Date format of column.

Output Data

Return
Filter reports, charts and map by global start and end date value.

                                

                                    #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}