Define your own SQL query in a view datasource

Technologies > User Interface Add-Ins > Parts for Reports > Report Based on a Custom SQL Query

A report data source can use a custom SQL query load report records from the database. This can be used to:

• Display calculated values that do not exist in the database directly.
• Display records obtained using sub-queries, relational set operations, or any other SQL techniques that are not directly supported by Archibus.

The SQL query text can contain binding expressions that use the ${sql} object. When the query is executed, these expressions are evaluated and replaced by database-specific functions or expressions. For example, the ${sql.as} expressions above is replaced by the AS keyword on Sybase and SQL Server, and by an empty string on Oracle:

<dataSource id="roomAreaByFloorDS">
<!-- Custom SQL query is used as a data source for all displayed grid records. It can include calculated fields. -->
<sql dialect="generic">
SELECT bl_id, fl_id, count(rm_id) ${sql.as} rooms, sum(area) ${sql.as} total_area
FROM rm
GROUP BY bl_id, fl_id
</sql>
<table name="rm" role="main"/>
<!-- All database fields used in the SELECT clause must be declared here. -->
<field table="rm" name="rm_id"/>
<field table="rm" name="fl_id"/>
<field table="rm" name="bl_id"/>
<field table="rm" name="area"/>
</dataSource>

If the SQL query has to use database-dependent functions that are not supported by the ${sql} object, the view can include separate SQL queries for each database dialect in use. In runtime, Web Central will determine which SQL query to use based on the database connection. The SQL query labeled "generic" is used as a fallback when a database-specific SQL query is not defined.

<dataSource id="roomAreaByFloorDS">
<!-- Custom SQL query is used as a data source for all displayed grid records. It can include calculated fields. -->
<sql dialect="generic">
SELECT bl_id, fl_id, count(rm_id) AS rooms, sum(area) AS total_area
FROM rm
GROUP BY bl_id, fl_id
</sql>
<sql dialect="oracle">
SELECT bl_id, fl_id, count(rm_id) rooms, sum(area) total_area
FROM rm
GROUP BY bl_id, fl_id
</sql>
<sql dialect="sqlserver">
SELECT TOP 100 PERCENT bl_id, fl_id, count(rm_id) AS rooms, sum(area)
AS total_area
FROM rm
GROUP BY bl_id, fl_id
</sql>
<table name="rm" role="main"/>
<!-- All database fields used in the SELECT clause must be declared here. -->    <field table="rm" name="rm_id"/>
<field table="rm" name="fl_id"/>
<field table="rm" name="bl_id"/>
<field table="rm" name="area"/>
</dataSource>

Only fields returned by the SELECT clause of the SQL query will be displayed in the report. The <dataSource> still must list all schema fields used in the SELECT clause of the SQL query. The <dataSource> should not list any calculated fields.

The panel that uses such custom data source must define field elements for all display fields if any calculated fields are displayed. Each calculated field must contain at least the dataType attribute. Valid values of the dataType attribute are "number", "text", "date" and "time". Numeric fields can also define the size and decimals attributes.

<panel type="grid" id="roomAreaByFloorGrid" dataSource="roomAreaByFloorDS"> <field table="rm" name="bl_id"/> <field table="rm" name="fl_id"/> <field table="rm" name="rooms" dataType="number" size="6" decimals="0"> <title>Room Count</title> </field> <field table="rm" name="total_area" dataType="number"> <title>Total Room Area</title> </field> </panel>

A calculated field used in a grid panel may also be set up as a link:

<panel type="grid" ...>
<field name="location" dataType="text" controlType="link" >
<title>Building, Floor, Room</title>
<command type="callFunction" functionName="View.alert('You clicked on a custom SQL field');"/>
</field>