There are instances that you might be programming in languages other than JavaScript in Protobi. In particular:
- writing HTML code in custom charts
- writing SQL code in data processing while in Protobi SQL
We utilize Embedded JavaScript in these instances to allow the creation of content with dynamic values.
Embedded JavaScript
In short, anything that is within the tags <% … %> is JavaScript code, and the <%=… %> tag allows you to generate a value to insert into your SQL or HTML code.
It means you can utilize the power of JavaScript to create loops, and calculate values that generate the desired results.
Custom charts example
The code below uses <% … %> to create variables that reference element specific data, and <%= %> to insert dynamic values in HTML code
<%
let showMissing = getShowMissing()
let marg = tabular.getMarginal(model.getKey(),'current')
let colorKeys = Object.keys(model.get('format'))
initColors(colorKeys)
%>
<% _.each(model.get('format'), function(label ,key) { %>
<% var pct=marg.getPercent(key,showMissing) %>
<% var pctStr = fmtPercent(pct)%>
<svg height=200 width=200>
<circle cx=100 cy=100 r=<%=pct*100%> stroke="black" stroke-width="0" fill=<%=getColor(key)%> />
<text x="50%" y="10%" text-anchor="middle" stroke="black" stroke-width="0.5px" ><%=label%></text>
<text x="50%" y="50%" text-anchor="middle" stroke="black" stroke-width="0.5px" ><%=pctStr%></text>
</svg>
</div>
<%})%>SQL EXAMPLE
The code below utilizes <% … %> to create an object called “params”, and then create the SQL code utilizing the values:
Process code:
DROP TABLE IF EXISTS <%=project.schema_name%>.<%=tableKey%>_temp CASCADE;
<% let params =
{
"columns": [
"XID",
"Q1",
"Q2",
"Q3"
]
}
%>
CREATE TABLE <%=project.schema_name%>.<%=tableKey%>_temp AS
SELECT <%=params.columns.join(',')%> ,
'20231117' AS report_date
FROM <%=project.schema_name%>.main
;
DROP TABLE IF EXISTS <%=project.schema_name%>.<%=tableKey%> CASCADE;
CREATE TABLE <%=project.schema_name%>.<%=tableKey%> AS
SELECT * FROM <%=project.schema_name%>.<%=tableKey%>_temp;Output code:
DROP TABLE IF EXISTS protobi_db.q1_to_q3 CASCADE;
CREATE TABLE protobi_db.q1_to_q3_temp AS
SELECT XID,Q1,Q2,Q3 ,
'20231117' AS report_date
FROM protobi_db.main
;
DROP TABLE IF EXISTS protobi_db.q1_to_q3 CASCADE;
CREATE TABLE protobi_db.q1_to_q3 AS
SELECT * FROM protobi_db.q1_to_q3_temp;