11g Essbase Hierarchy - Drill On Members With Ampersand (&) Special Character Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

Last updated on MARCH 08, 2017

Applies to:

Symptoms

Using Obiee 11g to pull data from Essbase 11.

Have a number of members in Essbase with the ampersand (&) character in their name and Alias name.

Have created a pie chart from a hierarchichal column which has values with ampersand (&) character.

Drill on a member with ampersand (&) character for example, "R & D" it works.

Drill on member names where ampersand (&) character is not prefixed/suffixed with a space, for example "R&D'", it errors out saying "Unknown Member" error.

The entire error encountered is:

View Display Error

Odbc driver returned an error (SQLExecDirectW).
 
Error Details

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 96002] Essbase Error: Unknown Member People Cost (R&D) - MPL used in query (HY000)
SQL Issued: SELECT s_0, s_1, s_2, s_3, s_4, s_5, s_6, s_7, s_8, s_9, s_10, s_11, s_12, s_13, s_14, s_15, s_16, s_17, s_18, s_19, s_20, s_21, s_22, s_23 FROM ( SELECT 0 s_0, "MR"."Accounts"."Gen10,Accounts - Default" s_1, "MR"."Accounts"."Gen7,Accounts - Default" s_2, "MR"."Accounts"."Gen8,Accounts - Default" s_3, "MR"."Accounts"."Gen9,Accounts - Default" s_4, "MR"."Management Units"."Gen3,Management Units - Default" s_5, "MR"."Management Units"."Gen4,Management Units - Default" s_6, CASE WHEN ISLEAF("MR"."Accounts"."Accounts") THEN 1 ELSE 0 END s_7, CASE WHEN ISLEAF("MR"."Management Units"."Management Units") THEN 1 ELSE 0 END s_8, IDOF("MR"."Accounts"."Accounts"."Gen10,Accounts") s_9, IDOF("MR"."Accounts"."Accounts"."Gen7,Accounts") s_10, IDOF("MR"."Accounts"."Accounts"."Gen8,Accounts") s_11, IDOF("MR"."Accounts"."Accounts"."Gen9,Accounts") s_12, IDOF("MR"."Management Units"."Management Units"."Gen3,Management Units") s_13, IDOF("MR"."Management Units"."Management Units"."Gen4,Management Units") s_14, SORTKEY("MR"."Accounts"."Gen10,Accounts - Default") s_15, SORTKEY("MR"."Accounts"."Gen7,Accounts - Default") s_16, SORTKEY("MR"."Accounts"."Gen8,Accounts - Default") s_17, SORTKEY("MR"."Accounts"."Gen9,Accounts - Default") s_18, SORTKEY("MR"."Management Units"."Gen3,Management Units - Default") s_19, SORTKEY("MR"."Management Units"."Gen4,Management Units - Default") s_20, "MR"."MR#1"."AprF" s_21, "MR"."MR#1"."Fall Plan" s_22, "MR"."MR#1"."GL Data" s_23 FROM "MR" WHERE (("Compliance Units"."Compliance Units - Default" = 'World Wide (WW00)') AND ("Periods"."Periods - Default" = 'Q2') AND ("Set of Books"."Set of Books - Default" = 'Agilent Consolidation SOB (ZZ-PR-999)') AND ("Years"."Years - Default" = 'FY12')) AND ((IDOF("MR"."Management Units"."Management Units"."Gen3,Management Units") IN ('C25001')) AND (IDOF("MR"."Management Units"."Management Units"."Gen4,Management Units") IS NOT NULL) AND (IDOF("MR"."Management Units"."Management Units"."Gen3,Management Units") IS NOT NULL) AND (IDOF("MR"."Accounts"."Accounts"."Gen7,Accounts") IN ('8 - MPL')) AND (IDOF("MR"."Accounts"."Accounts"."Gen9,Accounts") IN ('People Cost (R&D) - MPL')) AND (IDOF("MR"."Accounts"."Accounts"."Gen10,Accounts") IS NOT NULL)) UNION ALL SELECT 1 s_0, "MR"."Accounts"."Gen10,Accounts - Default" s_1, "MR"."Accounts"."Gen7,Accounts - Default" s_2, "MR"."Accounts"."Gen8,Accounts - Default" s_3, "MR"."Accounts"."Gen9,Accounts - Default" s_4, "MR"."Management Units"."Gen3,Management Units - Default" s_5, CAST(NULL AS VARCHAR(1)) s_6, CASE WHEN ISLEAF("MR"."Accounts"."Accounts") THEN 1 ELSE 0 END s_7, CASE WHEN ISLEAF("MR"."Management Units"."Management Units") THEN 1 ELSE 0 END s_8, IDOF("MR"."Accounts"."Accounts"."Gen10,Accounts") s_9, IDOF("MR"."Accounts"."Accounts"."Gen7,Accounts") s_10, IDOF("MR"."Accounts"."Accounts"."Gen8,Accounts") s_11, IDOF("MR"."Accounts"."Accounts"."Gen9,Accounts") s_12, IDOF("MR"."Management Units"."Management Units"."Gen3,Management Units") s_13, CAST(NULL AS VARCHAR(1)) s_14, SORTKEY("MR"."Accounts"."Gen10,Accounts - Default") s_15, SORTKEY("MR"."Accounts"."Gen7,Accounts - Default") s_16, SORTKEY("MR"."Accounts"."Gen8,Accounts - Default") s_17, SORTKEY("MR"."Accounts"."Gen9,Accounts - Default") s_18, SORTKEY("MR"."Management Units"."Gen3,Management Units - Default") s_19, CAST(NULL AS INTEGER) s_20, "MR"."MR#1"."AprF" s_21, "MR"."MR#1"."Fall Plan" s_22, "MR"."MR#1"."GL Data" s_23 FROM "MR" WHERE (("Compliance Units"."Compliance Units - Default" = 'World Wide (WW00)') AND ("Periods"."Periods - Default" = 'Q2') AND ("Set of Books"."Set of Books - Default" = 'Agilent Consolidation SOB (ZZ-PR-999)') AND ("Years"."Years - Default" = 'FY12')) AND ((IDOF("MR"."Management Units"."Management Units"."Gen3,Management Units") IN ('C25001')) AND (IDOF("MR"."Management Units"."Management Units"."Gen3,Management Units") IS NOT NULL) AND (IDOF("MR"."Accounts"."Accounts"."Gen7,Accounts") IN ('8 - MPL')) AND (IDOF("MR"."Accounts"."Accounts"."Gen9,Accounts") IN ('People Cost (R&D) - MPL')) AND (IDOF("MR"."Accounts"."Accounts"."Gen10,Accounts") IS NOT NULL)) ) djm FETCH FIRST 65001 ROWS ONLY

Cause