The report element

A report element defines how C-Phrase presents results to a specific class of user select queries. When C-Phrase decides to evaluate a particular query, it scans the reports, in the order they are defined in the .cphrase file looking for the first report that matches the query. The matching report determines a handler (table, bar, line or pie) and optionally an SQL template into which the query will be substituted before evaluation. This final resolved SQL is evaluated over the database and the results are passed to the associated handler. If no report is matched, then C-Phrase will fall back to normal, default handling of table, list and grouping queries.

Matching is determined by whether the question contains words that match a lexical attachment of the report. Note this is only required if the report actually has attached lexical material. Matching is also logic based. A query matches a report only if it is logically contained under the table or view mentioned in the purview of the report.

Here we see the DTD-based definition of the report element:

<!ELEMENT report (#PCDATA)>
<!ATTLIST report
          purview CDATA #REQUIRED
          handler CDATA #REQUIRED
          lex CDATA #IMPLIED
          head CDATA #IMPLIED
          order CDATA #IMPLIED
          distinct CDATA #IMPLIED
          over_aggs CDATA #IMPLIED
          alive CDATA #IMPLIED
          description CDATA #IMPLIED>

Here we see a report that gives a time series chart over the daily calorie view example defined in views:

<report lex="calorie report" purview="Daily" handler="bar">
    <![CDATA[
        SELECT date, calories as Daily FROM ($SUB) ORDER BY DATE ASC
    ]]>
</report>

The first thing to note is the query purview. If the user asks a question that gets interpreted as SELECT X1.* FROM Daily AS X1 WHERE X1.date > '2022-12-25' then this report may match because the user's query is logically contained under the purview table or view. However, a query over the table Eating would not match.

Given a match, the CDATA within the report contains the SQL template into which the user's SQL query will be substituted. The substitution will be directly in place of $SUB inside the SQL template. Note that $SUB is case-sensitive, it must be in all caps. If there is no such CDATA, then the user's SQL simply becomes the SQL that will be evaluated over the database, thus, by default a report encloses <![CDATA[$SUB]]>.

Assume that the user has asked a question "Calorie report for last month" that got interpreted as:

   SELECT * FROM Daily WHERE date > '2022-12-25'
    

If the above report matches, then the generated SQL will be exactly:

        SELECT date, calories as Daily FROM (
           SELECT * FROM Daily WHERE date > '2022-12-25')
        ORDER BY DATE ASC
    

of course Daily is a view, so once everything is resolved the final query will actually be:

        SELECT date, calories as Daily
        FROM (
          SELECT X1.date,X1.calories
          FROM (select sum(helping * calories) as calories, date
                from Eating, Food
                where eating.food = Food.name
                group by date) AS X1
          WHERE
          X1.date>'2022-12-25'
        )
    

This final query will be evaluated and the results will be passed to the bar handler which will generate a bar chart.

Report Attributes in Detail

purviewis the table or view which the user query must be over.

handler names how the results should be handled. The current values we use in the NLI Portal are "table","bar","line" and "chart".

lex, when present, stipulates that at least one of the phrases must be in the user's question for the report to match.

head, when present, determines the list of attributes which are excluded from the $ATTS variable. This is because these attributes are already being selected in the SQL Template. (see example below).

order determines the value of $ORDER which can be present in the CDATA for the report, but which only substitutes if there is no order by clause in the user's query.

distinct determines if the user's query should be a set, not a bag. This defaults to "true".

over_aggs defaults to "false". When this is true, the report will only apply if the matching query is an aggregation query. Note that reports with over_aggs will ignore the speficiation of the SQL template in the CDATA and merely execute the user query directly.

SQL Templates are in the dialect of the underlying database and often produce HTML on the actual values generated. So for example, consider the example from PIM.cphrase:

<report purview="Note_Attachment" handler="table">
<![CDATA[
SELECT DISTINCT
  (SELECT Y1.title FROM Note as Y1 WHERE Y1.id = X1.note) as note_title,
        '<img src="static/files/' || X1.file || '"/>' as image
 FROM ($SUB) AS X1
]]>
</report>