Managing Reports › Write CA Business Intelligence Reports › Write SQL for BusinessObjects Reports

Write SQL for BusinessObjects Reports

All SQL statements used by BusinessObjects reports are processed by the CA SDM ODBC driver. The ODBC driver supports standard SQL 92 SELECT statements with the following changes and extensions:

• CA SDM object names are used in place of DBMS table names, and CA SDM attribute names are used in place of DBMS column names.
• A CA SDM attribute alias name can be used anywhere in the query where a column name is valid. The ODBC driver replaces the attribute alias reference with one or more joins.

  Note: For more information, see Attribute Aliases.

• CA SDM DERIVED attributes (such as combo_name) can be used in the selection list only. They are not supported in any other part of the query, including in the WHERE clause.

  Note: Many Combo Name With Userid objects have been provided in the universe, such as the Customer Combo Name With Userid object used as a filter in the sample ad hoc report provided in the Administration Guide. These objects allow Combo Name to be used as filter prompts in ad hoc queries with Web Intelligence, to overcome the limitation of including Combo Name in the WHERE clause. They present both Combo Name and Userid in the filter prompt, but use only the selected Userids in the resultant SQL query.

• Queries can contain date literals in either of the forms:

  d'yyyy-mm-dd hh:mm:ss xm' (where xm is either am or pm)
  ts'yyyy-mm-dd hh:mm:ss'
  

  These literals can be used anywhere in the query. The ODBC driver automatically converts them to CA SDM internal date format (the number of seconds from midnight January 1, 1970).