Report Definition Structure › Queries

Queries

The <query> tag contains text that is passed as an SQL query to the database. The text can contain parameter values to control what is selected, and <databasename> tags to control which database is used. The text is always converted to upper case before being sent to the database.

Later versions of SQL use an 'as' keyword to allow user-friendly column names, for example, Select CrypticColumnName as UserFriendlyName. Datacom does not support this option; instead, the report generator supports a <columns> tag to achieve the same result. You can label the first nine columns, as required. If you do not assign a name, the column retains its original name, that is, the one it was assigned in the table from which it was selected.

Example: Query

<query>
	<columns column2=”Interval”/>
	select pRelativeDay, pIntervalName from <dtabasename/>.period
	where pRelativeDay=<parm name=”startRelativeDay”/>
</query>

Crosstabs

The result from a query can be converted from a simple table to a crosstab by specifying the summarise=crosstab attribute in the <query> tag. This attribute causes the values in the first column of the result to be used to label rows in the output, values from second column of the result to be used to label the columns of the output, and values from the third column of the result to be used as the actual data. Any other columns in the result are ignored. The usual case is that the first column contains dates, the second column contains attribute names, and the third column contains numeric facts.

Note: The <columns> tag is ignored for queries that have summarize=crosstab.

You can sort crosstabs by specifying the sort=column, direction attribute, where column is a column number or an untranslated column name, and direction is asc (ascending) or desc (descending). The default is ascending.

Example: Crosstab

<query summarise="crosstab" sort=”BytesOut, desc”>
	select pIntervalname, aName, nfValue from period, attribute, numericfact
	where numericfact.period = period.period
	and numericfact.attribute = attribute.attribute
	and aName in ( 'BytesIn', 'BytesOut' )
</query>

The query may return the following set of data:

This set of data would be converted to the following:

Reusing Queries

You can save and reuse the output from a query. To do this, you can give the original query a name, and refer to it later using the <reference> tag. You can use a <reference> tag anywhere that a query can be used. The <query> tag must appear before the <reference> tag.

You can include a column= attribute to select a subset of the columns of the original query. The parameter for the column attribute is a comma-separated list of columns.

Example: Reuse Query

<query name=”fred”>...</query>
...
<reference query=”fred” columns=”1,3,5”/>