When creating a custom CA Business Intelligence (CABI) Web Intelligence (WEBI) report and using a Derived Universe, there are issues with reports that contains objects from the OOTB TICKET UNIVERSE CLASS.

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

Description:

When creating a custom WEBI report and using a derived Universe, there are issues with reports that contains objects from the OOTB TICKET UNIVERSE CLASS

For example, a simple report that contains the following fields:

TICKET-> Open Date
TICKET-> Close Date
TICKET-> Ref Num
TICKET-> Assignee Combo Name
TICKET-> Type
TICKET-> Group Name

Add the GROUP NAME as a filter to the report with IN LIST and PROMPT attributes

When you attempt to run the report and do a REFRESH VALUES on the GROUP NAME filter, an error is received

A database error occurred. The database error text is: [DataDirect][ODBC OpenAccess SDK Driver] [OpenAccess SDK SQL Engine] AHD12002: Error in QueryHandler in method fetch_response: AHD04199: An unexpected error occurred. Contact your administrator [Microsoft OLE DB Provider for SQL Server] {SQL Code =156 SQL State=42000] Incorrect syntax near keyword 'UNION'.: [Microsoft OLE DB Provider for SQL Server] [SQL Code=156 SQL State=42000] Incorrect syntax near the keyword 'UNION'.. (WIS10901)

Solution:

An ORDER BY clause is being added to the WEBI report query.

This can be resolved by changing the parameter 'FORCE_SORT_LOV' within the Derived Universe

This can be accomplished by:

  1. Launch the CABI Universe Designer application
  2. Login with a CABI administrator account
  3. Open the CABI Derived Universe which the WEBI report(s) are based on
  4. Select FILE -> PARAMETERS
  5. Click on the PARAMETER tab
  6. Scroll down and locate the 'FORCE_SORTED_LOV' attribute. Change the value for attribute from YES to NO
  7. Save and export the derived Universe