Write CA Business Intelligence Reports

Contents
casm171
Contents
You can write CA Business Intelligence reports for CA SDM.
CA SDM ODBC Driver
Business Objects reporting applications (Crystal Reports and Web Intelligence) access data using an ODBC driver that connects directly with the CA SDM object engine named the domsrvr.
This connection provides a number of benefits:
  • SELECT statements used by BusinessObjects reports reference objects and attributes using their CA SDM names (in other words, their Majic names). For example, a SELECT statement for a report on contacts could be written:
    SELECT combo_name FROM cnt WHERE last_name LIKE 'smith%'
  • The CA SDM ODBC driver maps attributes like combo_name and objects like cnt to their corresponding DBMS name or names.
  • All CA SDM security, including data partition and tenancy restrictions, is automatically applied to reports. All connections between BusinessObjects and CA SDM are associated with a CA SDM contact, and the CA SDM ODBC edits input SELECT statements to enforce the security restrictions associated with the end-user reporting role. BusinessObjects does not connect directly to the database.
  • The CA SDM Attribute Alias feature "flattens" or de-normalizes the CA SDM database. Attribute aliases are additional attributes in CA SDM objects that allow a query to reference attributes in joined tables without an explicit join, allowing the base table to be used as if it were a reporting view.
  • The CA SDM ODBC driver supports date literals in queries, and automatically translates values in CA SDM internal date format to a standard DBMS date.
The CA SDM ODBC driver is supported only for BusinessObjects reporting (Crystal and Web Intelligence). CA SDM does not provide a standalone ODBC client, and does not recommend use of the ODBC driver with applications other than BusinessObjects.
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.
    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.
    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
    CA Business Intelligence Documentation
    . 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).
PDM Functions for BusinessObjects Reports
To assist in working with specialized CA SDM features and data types, the ODBC driver extends SQL to support a number of additional query functions. All driver-supported functions begin with the string "Pdm", and are known as PDM functions as described in the following table:
PDM Functions
Description
PdmAddDays([date,] count)
When used with one argument, adds the number of days in its argument to today's date and returns the result. When used with two arguments, adds the number of days in its second argument to the value of the date column specified in its first argument and returns the result. This function may be used anywhere in the query
PdmAddMonths([date,] count)
When used with one argument, adds the number of months in its argument to today's date and returns the result. When used with two arguments, adds the number of months in its second argument to the value of the date column specified in it first argument and returns the result. The single argument form can be used anywhere in the query. The two-argument form can be used only in the selection list
PdmDay([date])
When used with no arguments, returns the current day as an integer. When used with one argument, returns the day associated with the value of the date column specified in its argument. The zero argument form can be used anywhere in the query. The one-argument form can be used only in the selection list.
PdmDownTime( slaName, workshift, startDate, endDate )
Calculates the downtime between two dates under the specified SLA and workshift. This function can be used only in the selection list.
PdmMonth([date])
When used with no arguments, returns the current month as an integer from 1 to 12. When used with one argument, returns the month associated with the value of the date column specified in its argument. The zero argument form can be used anywhere in the query. The one-argument form can be used only in the selection list.
PdmMonthName([date])
When used with no arguments, returns the localized name of the current month ("January", "February", and so on). When used with one argument, returns the localized name of the value of the date column specified in its argument. The zero argument form can be used anywhere in the query. The one-argument form can be used only in the selection list.
PdmDay([date])
When used with no arguments, returns the current day as an integer. When used with one argument, returns the day associated with the value of the date column specified in its argument. The zero argument form can be used anywhere in the query. The one-argument form can be used only in the selection list.
PdmSeconds(date)
Returns the value of the date column specified in its argument in its raw form as the number of seconds from midnight January 1, 1970. This function can be used only in the selection list. The argument is required.
PdmString(column)
Returns the string equivalent of value of the column specified in its argument. This function can be used with UUID, date, or string columns. It can be used only in the selection list.
PdmToday()
PdmToday() [timeAdj [, day [, month [, year]] ]] )
Evaluates to today's date (in seconds from 1/1/1970), adjusted according to the arguments:
timeAdj:
-1 -- adjust time to beginning of day (0:00:00);
+1 -- adjust time to end of day (23:59:59)
day:
negative -- adjust date by number of days specified
positive -- set day to absolute value specified (or to last day of month, whichever is less)
month:
negative -- adjust date by number of months specified
positive -- set month to absolute value specified (or to December (12), whichever is less)
year:
negative -- adjust date by number of years specified
positive -- set year to absolute value specified
Adjustments are applied in the order year, month, day. A zero or omitted argument is ignored.
PdmYear([date])
When used with no arguments, returns the current year as a four-digit integer. When used with one argument, returns the year associated with the value of the date column specified in its argument. The zero argument form can be used anywhere in the query. The one-argument form can be used only in the selection list.
 
Attribute Aliases
Attribute aliases are additional attributes in CA SDM objects that reference data from joined tables using Majic dotted join syntax (where the syntax srelname.attrname is a reference to attribute attrname in the table referenced by foreign key srelname. A large number of predefined attribute aliases are provided with CA SDM Release 12.9, with names that typically are the same as the corresponding Majic join, with underscores replacing the dots that indicate the join. For example, the following SELECT statement might be used for a report that lists information about the request assignees:
SELECT ref_num, assignee_combo_name, assignee_organization_name FROM cr WHERE customer_last_name LIKE 'smith%'
The CA SDM ODBC driver automatically builds joins as required to access the tables referenced by attribute aliases. A user in the CA SDM administrator role can easily add new attribute aliases online, providing a column-at-a-time way to extend the view corresponding to an object.
To access the Attribute Alias table, select the Administration tab, and browse to CA SDM, Codes, Attribute Aliases.
pdm_isql Interactive SQL
A command line utility, pdm_isql, is provided with CA SDM to allow interactive entry of SQL SELECT statements. SELECT statements entered into pdm_isql are sent to the CA SDM ODBC driver, allowing you to test SQL SELECT statements outside of BusinessObjects.
To use pdm_isql
  1. Ensure that $NX_ROOT/bin is in your path.
  2. Enter the command:
    pdm_isql
  3. At the pdm_isql prompt, enter the command:
    connect
    username*[email protected]_hostname
    (Where
    username
    and
    password
    are valid CA SDM login credentials, and the host name is the host name of a CA SDM server with a web engine.)
  4. Enter SQL select statements followed by a semicolon.