System / Add-in Manager / View Definition Wizard

About Adding Virtual Fields Using the View Definition Wizard

When creating views using the View Definition Wizard, you can add calculated fields based on SQL expressions to a view. These fields are called virtual fields, as Archibus calculates them on demand based on data in the database, but the calculation is not stored in the database. Including virtual fields in views is useful when your reporting needs require specific statistics that are not stored in the database. For example, a real estate or facility manager might need to report on the facility cost per headcount, or the difference between the room area specified for a room standard and the room's actual area. This data is readily available when you create a view that includes these calculations.

Using the View Definition Wizard to add these statistics to a view simplifies the process of defining the view and writing the SQL query.

This topic provides background information to help you get started adding virtual fields. For the procedure for adding virtual fields, see Setting View Characteristics.

This topic includes the following sections:

• Supported Report Patterns
• Entering SQL Snippets
• SQL Syntax Examples
• Testing and Troubleshooting SQL
• Working with Advanced SQL in Debug Mode

Supported Report Patterns

You can add virtual fields to:

• Any interactive report (a report, a drill-down to a report, and a two-level drill-down)
• A paginated report with the following exceptions:
  • To include a virtual field in a data band, the data band cannot use grouping; that is, the check box to "Summarize the data in this data band by its sort order" cannot be checked for that data band. This check box is found on the Set Characteristics>Set Options tab.
  • You cannot add a virtual field to a summary band, such as the summary band in "A paginated report with one summary band and one data band".
  • Only the "Drawing Legend Data" panel for a paginated report with highlighted drawings can have a virtual field. For all other panels for this paginated report pattern, you cannot add a virtual field.
  • A paginated report with thematically highlighted drawings cannot have a virtual field.

Edit forms and view analysis views cannot have virtual fields. For these report patterns, only the drill-down panel can have a virtual field.

Entering SQL Snippets

You enter the SQL as a "snippet' that represents the calculation for the virtual field. The program completes the SQL statement for the dataSource by enclosing the snippet in a wrapper that injects the SQL snippet into the fields section of the SELECT statement.

This "snippet" can use any SQL statement that:

• Refers to only fields in the dataSource
• Is a subquery related to a field in the dataSource
• Returns a single value

See Adding Virtual Fields for the procedure for adding a virtual field to a view.

SQL Syntax Examples

The following are examples of SQL snippets that you can enter to create virtual fields. The examples include the wrapper that Archibus inserts around the snippet for your entry. If you prefer, you can enter the wrapper along with the SQL snippet, but it is not necessary to do so.

Simple calculation using fields in the dataSource

For example, if you have a dataSource on the Buildings (bl) table, you could use a virtual field for calculating the exterior wall area. You would enter this snippet:

(bl.area_gross_ext - bl.area_gross_int )

The View Definition Wizard would add the following <field /> to your view:

<field table="bl" name="fci" dataType="number" decimals="2"> <sql dialect="generic"> ( bl.area_gross_ext - bl.area_gross_int ) </sql> </field>

Example calculation using a standards table

Or, in a dataSource on the Rooms (rm) table that has the Room Standards (rmstd) table as a related standards table , you could enter a virtual field SQL snippet like the following that obtains the difference between the standard and the actual area:

(rm.area - rmstd.area )

The View Definition Wizard would add the following <field /> to your view:

<field table="bl" name="fci" dataType="number" decimals="2"> <sql dialect="generic"> (rm.area - rmstd.area ) </sql> </field>

Example relating fields in a dataSource to summarize data

It is often useful to summarize data for each row in a view. For example, in a dataSource showing records from the Buildings (bl) table, you might want to show all operating costs associated with each building. To do so, you sum all operating costs from the Costs (cost_tran) table, and show that value in each row. You can do this using an SQL snippet such as the following:

(SELECT SUM( amount_expense ) FROM cost_tran, cost_cat WHERE cost_tran.cost_cat_id = cost_cat.cost_cat_id AND cost_tran.bl_id = bl.bl_id AND cost_cat.cost_type = 'OPERATING EXP.' )

The View Definition Wizard would add the following <field /> to your view:

<field table="bl" name="operating_costs" dataType="number" decimals="2"> <sql dialect="generic"> (SELECT SUM( amount_expense ) FROM cost_tran, cost_cat WHERE cost_tran.cost_cat_id = cost_cat.cost_cat_id AND cost_tran.bl_id = bl.bl_id AND cost_cat.cost_type = 'OPERATING EXP.' ) </sql> /field>

Example - using conditionals in the SQL snippet

A typical reporting need is to show one field or another based on the field's value. You can do this using conditionals. For example, suppose you want to show the total suite area entered from CAD (su.area_usable) if this measured suite area is available; if the measured area is not available, you want to show the total estimated manual areas for the suites (su.area_manual). To do so, you would use a snippet such as the following:

(SELECT SUM(CASE WHEN su.area_usable = 0 THEN su.area_manual ELSE su.area_usable END) FROM su WHERE su.bl_id = bl.bl_id)

The View Definition Wizard would add the following <field /> to your view:

<field table="bl" name="area_estimated" dataType="number" decimals="0"> <sql dialect="generic"> (SELECT SUM(CASE WHEN su.area_usable = 0 THEN su.area_manual ELSE su.area_usable END) FROM su WHERE su.bl_id = bl.bl_id) </sql> </field>

Testing and Troubleshooting SQL

There are several ways to test and troubleshoot your SQL snippet.

• Preview tab: You can use the Preview tab of the View Definition Wizard to test your virtual fields, just as you can use it to test other changes you make with the Wizard.
• Edit button: If you have made an error in your SQL snippet, the SQL error will appear in a pop-up dialog when you test the view. You can then return to the Select Fields tab, click the Edit button for the virtual field, and correct the issue.
• Enable Advanced Editing check box: You can also make changes by selecting the Enable Advanced Editing check box on the Preview tab which enables you to edit the view (.axvw) file XML directly.

Working with Advanced SQL in Debug Mode

If you are creating virtual fields with complex queries, and if you have Web Central loaded locally on your machine or on a staging server, you might want to work in debug mode.

To enter debug mode, set debug=true in the \web-inf\config\debug.properties file, then restart your application server.

Working in debug mode has the following advantages:

• Your view is reloaded every time you load it from the URL or the Navigator. You can edit the view (.axvw) XML text file directly, and reload your changes as many times as you like.
• The Archibus program echoes the full SQL query that Archibus issues to the log file in \web-inf\config\archibus.log. You can see all parts of the SQL query – both the snippet that you provided and the enclosing wrapper that Archibus added. This information can help debug queries that do not work the way you expect – perhaps because of other clauses that Archibus adds, such as the ORDER BY clause.
• To modify and test your changes directly, you can cut the query out of the log file and paste it into your own SQL execution environment, such as Oracle’s SQL Plus program. Once finished, you can cut the snippet out, and paste it back to the Archibus view file by clicking the "Enable advanced editing"check box on the Preview tab.