The report element
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
<!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
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
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>