Error: "Failed To Load XML" In New Data Model After Selecting Multiple Values For A Parameter And Clicking 'View' On Data Tab For Data Set SQL Which Filters Results From The Parameter Values (Doc ID 2426703.1)

Last updated on SEPTEMBER 10, 2019

Applies to:

Symptoms

When attempting to view data from a new data model (on the Data Model > Data window within BI Publisher or Analytics) which uses a parameter list of values to return results, selecting a single value from the parameter returns results but selecting more than one value from the parameter returns the following error when attempting to view data:

ERROR
-----------------------
Failed to load XML.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. Login to Analytics / BI Publisher
2. Select New > Data Model
3. Create List of values:
   
   1. Click List of Values
   2. Click Add
   3. Enter a name
   4. Select 'SQL Query' from type dropdown
   5. Select the datasource from dropdown
   6. Enter SQL in SQL Query Field
      
      • Example SQL:
        • select distinct( "P6PROJECTCODEHIERARCHY"."PARENTCODEVALUENAME") as "PARENTCODEVALUENAME"
          from "PXRPTUSER"."P6ACTIVITYPERIODACTUAL" "P6ACTIVITYPERIODACTUAL", "PXRPTUSER"."P6PROJECTCODEASSIGNMENT" "P6PROJECTCODEASSIGNMENT", "PXRPTUSER"."P6PROJECTCODEHIERARCHY" "P6PROJECTCODEHIERARCHY"
          where "P6PROJECTCODEHIERARCHY"."PARENTCODEVALUENAME"="P6PROJECTCODEASSIGNMENT"."PROJECTCODEVALUE"
          and "P6PROJECTCODEASSIGNMENT"."PROJECTOBJECTID"="P6ACTIVITYPERIODACTUAL"."PROJECTOBJECTID" and "P6PROJECTCODEHIERARCHY"."CODETYPENAME" ='Construction Manager'
4. Create Parameter:
   
   1. Click Parameters
   2. Click Add
   3. Enter Name for parameter
      
      • Example Name:
        • CM
   4. Select 'String' from data type dropdown
   5. Select 'Menu' from Parameter Type dropdown
   6. In Display label, Enter a user defined name
   7. In List of Values dropdown, click the list of value created in step 3
   8. Click 'Multiple Selection' and 'Can select all' checkboxes
5. Create Data Sets
   
   1. Click Data Sets
   2. On diagram page, click New > SQL query
   3. Enter a name for the dataset
   4. Select the datasource from dropdown
   5. Select 'Standard SQL' in the type of SQL dropdown
   6. Enter SQL in SQL query window which uses the parameter
      
      • Example SQL:
        • select  
          distinct (to_char( "P6FINANCIALPERIOD"."ENDDATE", 'YYYY-MM')) as "ENDDATE",
          sum("P6ACTIVITYPERIODACTUAL"."ACTUALLABORCOST") over (PARTITION BY to_char( "P6FINANCIALPERIOD"."ENDDATE", 'YYYY-MM'))as "ACTUALLABORCOST",
          sum("P6ACTIVITYPERIODACTUAL"."PLANNEDVALUECOST") over (PARTITION BY to_char( "P6FINANCIALPERIOD"."ENDDATE", 'YYYY-MM'))as "PLANNEDVALUECOST",
          sum("P6ACTIVITYPERIODACTUAL"."PLANNEDVALUECOST") over (order by to_char( "P6FINANCIALPERIOD"."ENDDATE", 'YYYY-MM')) as "Planned Running COST" ,
          sum("P6ACTIVITYPERIODACTUAL"."ACTUALLABORCOST") over (order by to_char( "P6FINANCIALPERIOD"."ENDDATE", 'YYYY-MM')) as "Actual Running COST" ,
          "P6PROJECTCODEASSIGNMENT"."PROJECTCODEVALUE" as "PROJECTCODEVALUE"
          from "PXRPTUSER"."P6PROJECTCODEASSIGNMENT" "P6PROJECTCODEASSIGNMENT", "PXRPTUSER"."P6FINANCIALPERIOD" "P6FINANCIALPERIOD", "PXRPTUSER"."P6ACTIVITYPERIODACTUAL" "P6ACTIVITYPERIODACTUAL"
          where "P6FINANCIALPERIOD"."OBJECTID"="P6ACTIVITYPERIODACTUAL"."FINANCIALPERIODOBJECTID" and "P6PROJECTCODEASSIGNMENT"."PROJECTOBJECTID"="P6ACTIVITYPERIODACTUAL"."PROJECTOBJECTID"
          and "P6PROJECTCODEASSIGNMENT"."PROJECTCODEVALUE" =:CM
   7. Click OK
6. Test Results:
   
   1. Click data link
   2. Click a single option from parameter
   3. Click View
   4. Results display successfully
   5. Click more than one option from parameter
   6. Click View
   7. Note the error which occurs

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.