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;