4. The XML report definition syntax

EspressoViews speficies as set of XML tags and tag attributes to define a report entity and the hierarchy of entities described in the previous chapter. Describing these XML tags is the scope of this chapter.

In the XML report definition all entities are placed within a root tag which you can give a name of your choice.

A report entity is defined within the tag <SQLQueryDefinition>

If an EspressoViews Report is made of two entities then the basic structure will look like this:

<ROOT>
	<SQLQueryDefinition>
		...
	</SQLQueryDefinition>

	<SQLQueryDefinition>
                ...
	</SQLQueryDefinition>
</ROOT>

The tag <SQLQueryDefinition> accepts also up to four non-mandatory XML tag attributes:

• ExecuteOnFirstRun [true, false]

With ExecuteOnFirstRun the user can specify for a report entity whether the data should be retrieved at the beginning of the report or only when the GUI user manually expands the respective data tree branch. If not explictily used, this attribute is set by default to "true". This attribute cannot be used with the value of "false" for the first report entity. This means that the SQL of the first entity is always ran at the beginning.

<ROOT>
	<SQLQueryDefinition>
		...
	</SQLQueryDefinition>

	<SQLQueryDefinition ExecuteOnFirstRun="false">
                ...
	</SQLQueryDefinition>
</ROOT>

If the attribute is set to "false" in one non-first report entity then at start of the report the data retrieval for this report entity and for all its children entities is skipped. When the GUI user manually expands the respective data tree branch then the data retrieval is started for this entity and all its children entitites for which the attribute executeOnFirstRun is set to "true". In addition the SQL is further retricted by the applicable conditions transmitted by the parent entities. This feature is very usefull in reports with many and/or resource consuming SQLs, as it allows you not to select and display all possible data right from the start but only a minimum and the rest is sucessively selected piece by piece when it is needed by the GUI user.

• suppressDisplayIfNoData [true, false]

If suppressDisplayIfNoData = "true" for an SQL query definition and if the result set of this SQL query is empty then the empty result line and also the query name will not be shown. This option was used in the previous examples. By default this attribute is set to “false”, i.e. empty lines are shown by default.

<ROOT>
	<SQLQueryDefinition suppressDisplayIfNoData="false">
		...
	</SQLQueryDefinition>

	<SQLQueryDefinition suppressDisplayIfNoData="true">
                ...
	</SQLQueryDefinition>
</ROOT>

• emptyColumnDisplayMode ["Suppress", "Suppress Branch Level"]

If the resultset of an SQL is not empty, still some columns can be empty. This option allows you suppress the display of a column if 1) it is empty through out the whole result set of a query or 2) it is empty in a certain branch of the data tree, in which the result of this query is displayed. For the first suppress mode option you can use the keyword "Suppress" for the latter suppress mode you can use the keyword "Suppress Branch Level".

In the first query you can use only the keyword "Suppress", as here the data is shown only in one branch. Used in conjunction with the attribute "ExecuteOnFirstRun" set to "false" the display mode "Suppress" behaves like the display mode "Suppress Branch Level", as the SQL query is executed only for the branch which the user expands. In this case it is recommended to use the display mode "Suppress" instead of "Suppress Branch Level" as the first mode requires less computing power.

<ROOT>
	<SQLQueryDefinition emptyColumnDisplayMode="Suppress">
		...
	</SQLQueryDefinition>

	<SQLQueryDefinition emptyColumnDisplayMode="Suppress Branch Level">
                ...
	</SQLQueryDefinition>
</ROOT>

• MaxResultRows [1..max integer]

The user can freely set the maximum number of rows to be retrieved per SQL query using the atrribute MaxResultRows = "a decimal number". If the attribute is not used then by default a default maximum rows threshold of 10000 rows is set for each SQL query. If a SQL query exceeds this threshold, then the rows above the threshold are disregarded and a warning message describing this in detail is written in the report generation log. The reason for this threshold is to prevent that a SQL query selects, by mistake or because of wrong design, hundred of thousands or millions of rows which will cause an overload of the memory and CPU while the report for this huge result set is generated.

<ROOT>
	<SQLQueryDefinition>
		...
	</SQLQueryDefinition>

	<SQLQueryDefinition MaxResultRows="100">
                ...
	</SQLQueryDefinition>
</ROOT>

Within every <SQLQueryDefinition> tag you can specify the following sub tags:

Tag name	Mandatory	Description
SQLQueryLabel	Yes	This the is the identifier of a report entity. The value in this tag is alphanumeric and case sensitive.
SuperSQLQuery	It depends	Links a report entity to its parent entity. The first report entity must not contain this tag, for the others this tag is mandatory.
SQLQuery	Yes	Contains the SQL to be executed in the DB system
SubQueryLocator	No	The file locator for another report definition file. An EspressoViews report can be embedded in a report entity of another EspressoViews report. The embedded report will be added as sub-tree structure to the report entity where it is embedded.
ResultColumns	No	The user can define in this a tag the result set columns to be displayed or not displayed in the report
MaxInheritanceDepth	No	Every entity which is not the root entity can restrict the applicability inherited columns to a number of parent levels above it

All these sub-tags cannot occur more than once in a report entity. The order of the sub-tags within the <SQLQueryDefinition> tag is irrelevant for EspressoViews. All tags names and attributes are case insensitive.

About <SQLQueryLabel>

Every report entity needs to have exactly one identifier.

Example:

<SQLQueryLabel>Title of the Query X</SQLQueryLabel>

About <SuperSQLQuery>

Links a report entity to its parent entity. The first report entity i.e. the root entity is the first parent and cannot have another parent therefore it cannot have this tag. Every report can have only one root entity. The other, non-root, entities must have a link to a parent. The report definition file is read sequential and top-down, and therefore in the definition file the parent entity need to precede the child entity.

Example:

<SQLQueryDefinition>
	<SQLQueryLabel>Parent Query</SQLQueryLabel>
	...
</SQLQueryDefinition>

<SQLQueryDefinition>
	<SQLQueryLabel>A Child</SQLQueryLabel>
	<SuperSQLQuery>Parent Query</SQLQueryLabel>
	<!-- The child entity should come after under the parent entiry-->
	...
</SQLQueryDefinition>

About <SQLQuery>

EspressoViews executes the SQL query placed in this tag as an SQL statement which retrieves data. If the user puts here any other SQL statement, like delete, update, insert, or even some DDL statement to change the DB schema, then its execution will inevitably fail with error. In addition, EspressoViews creates read-only sessions (note this is not supported by all RDBMS implementing JDBC). Nevertheless with an SQL query one can still call a stored procedure which potentially could change the data and could produce a schema damage. In order to prevent this scenarios the DBA or however is responsible for the security of the DB should allow the users of this tool to access the DB only with an user which is granted only read rights.

The entered DB password is encrypted as soon as the user leaves the password input field. But, as of now (May 2018) the client-server connection is not encrypted! This is one TO-DO.

Example:

<SQLQueryDefinition>
...
<SQLQuery>
SELECT something1,…, somethingX from A_Table
</SQLQuery>
...
</SQLQueryDefinition>

Be also aware that in XML the charachters <, > and & are reserved. If these characters are used in an SQL query then they need to be replaced with the follwing escape signs:

<    &lt;
>    &gt;
&   &amp;

Note also that EspressoViews in not ordering the results lines retrieved by one query since this can be perfectly done by the SQL query. And generally, nothing which can be also achieved with SQL is or will be implemented by this tool.

About <SubQueryLocator>

The user can add or embed one or more report definition files in another report definition file. There is no structural difference between an embedded definition file and the definition file which embeds. They are both EspressoViews report definition files.
The embedded report will be added as sub-tree structure under the entity where the <SubQueryLocator> tag is used, whereby the root entity of the embedded report becomes a child of the embedding entity. This is an additional possibility for an entity to become a parent entity.

Example:

<SQLQueryDefinition>
...
	<SubQueryLocator>
		C:\FolderName\EmbeddedReportName.xml
	</SubQueryLocator>
...
</SQLQueryDefinition>

More than one files can be embedded with one and same <SubQueryLocator> tag. For this scope the file locators must be separated with comma:

	<SubQueryLocator>
		SubReportName1.xml, SubReportName2.xml
	</SubQueryLocator> 

The file locator can contain either absolute or relative paths. If the embedded and the embedding reports are in the same directory then you can also simply the embedded report like this:

<SubQueryLocator>
	EmbeddedReportName.xml
</SubQueryLocator>

About <ResultColumns>

This tag is optional. If it is not used then all selected result columns are displayed. This tag allows the user to display only a specified set of result columns.

In the below example only the specified there columns will be displayed:

<SQLQueryDefinition>
...
	<ResultColumns>
		Column_A, Column_B, column_c
	</ResultColumns>
...
</SQLQueryDefinition>

If the not: option is used (can be used only in the first element of the list) then all selected column names are displayed less the columns specified after the not: operator.

<SQLQueryDefinition>
...
	<ResultColumns>
		not: Column_B, column_c
	</ResultColumns>
...
</SQLQueryDefinition>

The column names and the not operator are case insensitive. Wrong worded column names will be ignored (and a correspondent warning message will be added to the execution logs section).

About <MaxInheritanceDepth>

By default all applicable transmitted columns are applied. But an entity has the possibility to restrict the applicability of the transmitted column values. For instance it could chose to apply only the inheritance coming from the father/mother entity but not also the inheritance coming from the grandfather/mother entity and older. For this purpose the entity can set the MaxInheritanceDepth tag to 1, meaning the inheritance is taken from 1 level above and not deeper (seen bottom up):

 <MaxInheritanceDepth>1</MaxInheritanceDepth>

Be aware than this tag is affecting only the applicability of the inheritance. The entity using this tag will still forward every transmitted values to its children independently of the depth.

Next Page->