Automated query generation of CA Business Intelligence (CABI) Web Intelligence (WEBI) reports creates incorrect syntax, which results in a CABI Parse Error.

Document ID : KB000048489
Last Modified Date : 14/02/2018
Show Technical Document Details

Automated query generation of CA Business Intelligence (CABI) Web Intelligence (WEBI) reports creates incorrect syntax.

For example, when attempting to join the CR and CR_PRP tables through the 'drag and drop' process of creating a WEBI report, the following query is created:


 SELECT       
 cr.ref_num, 
 cr.summary,
 cr.priority_symbol,
 cr.request_area_symbol,
 cr.status_symbol,
 cr.assignee_combo_name,
 cr.affected_end_user_combo_name,
 cr.open_date,
 cr.close_date,
 cr_prp.label,
 cr_prp.value
 FROM
 { oj cr LEFT OUTER JOIN cr_prp ON ( cr.persistent_id=cr_prp.owning_cr ) }

This syntax results in a CABI Parse Error similar to the following:

Figure 1

The error is a result of the Outer Join parameters added to the FROM clause of the query.

Perform the following to resolve the issue:

  1. Launch the CABI Designer application on the CABI server and import the Universe (out-of-box or Derived) that is being used for custom WEBI reporting

  2. Click on FILE-> PARAMETERS and go to the PARAMETER tab.

    Figure 2

  3. Ensure that the value of ANSI92 is set to 'Yes'.

  4. Add a new parameter with a Property Name of SORT_JOINS_BY_QUERY_OBJECTS and set its value to 'Yes'. Click OK.

    Figure 3

  5. Click FILE-> SAVE and FILE-> EXPORT to save and export the Universe changes.

    Figure 4

  6. Restart the CABI Server Intelligent Agent (SIA) service via the Central Configuration Manager (CCM)

    Figure 5