Using reports

The Oracle Service Cloud driver exposes reports defined on an Oracle Service Cloud instance as stored procedures. An application can obtain a list of the reports defined on an Oracle Service Cloud instance by calling the SQLProcedures catalog function. The names of the reports that can be invoked through the driver are listed in the PROCEDURE_NAME name column of the SQLProcedures results.

The driver incorporates report name and unique report ID into the procedure name reported by SQLProcedures. The driver creates the reported procedure name by appending the report ID to the report name using an underscore (_) to join them. Additionally, any spaces in the report name are replaced with an underscore character. The report name is also prepended with RN if the report starts with a digit. Like all identifier name metadata returned by the driver, the procedure name is uppercase. For example, a report named Contact Lookups would be modified as follows:

CONTACT_LOOKUPS_14001

CALL ReportName (FilterName operator FilterValue;FilterName operator FilterValue...)

And ODBC mechanisms are used for calling a stored procedure that returns a result set. The following example shows one way to invoke the Contact Lookups report:

char[] sql = "{call CONTACT_LOOKUPS_14001('[Contact ID]< 10')}";
rc = SQLPrepare(hstmt,sql,SQL_NTS);
if(rc==SQL_ERROR)
    // process errors
rc = SQLExecute(hstmt);
if(rc==SQL_ERROR)
    // process errors
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS){
    // process results
}

In this example, the standard Call escape syntax is employed for the Opportunities report:

{CALL Opportunities_13029 ([Opportunity ID] > 10;[Organization ID] < 20;
      Name [NOT LIKE] [Imaging Equipment])}

The following rules for filters apply when writing applications which use reports:

• Filters are separated by semicolons.

• The FilterName must be specified to the left of the operator; the FilterValue must be specified to the right of the operator.

• FilterName or FilterValue must be delimited by brackets ([]) if it contains the following special characters:

  ';', '.', '?', '/', '(', ')', '{', '}', '[', ']', ',', whitespace, 
  operators,  single quote, double quote

• If a singlequote (') is part of the Filter Name or Filter Value, it must be doubled.

• If single bracket (]) is part of the Filter Name or Filter Value, it must be doubled.

• The following multiple-word operators must be delimited by []:

  NOT LIKE, IN LIST, NOT IN LIST, NOT REGEX

• null is considered a NULL value. For example, Name = null. In contrast, if 'null' is the string literal, it must be delimited by [].

• An empty string is represented as []. For example, Name = [].

The following table shows the operators available for column comparison.