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:
Primavera P6 Enterprise Project Portfolio Management - Version 17.11.0.0 and laterPrimavera Analytics Cloud Service - Version 17.11.0.0 and later
Information in this document applies to any platform.
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:
- Login to Analytics / BI Publisher
- Select New > Data Model
- Create List of values:
- Click List of Values
- Click Add
- Enter a name
- Select 'SQL Query' from type dropdown
- Select the datasource from dropdown
- 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'
- select distinct( "P6PROJECTCODEHIERARCHY"."PARENTCODEVALUENAME") as "PARENTCODEVALUENAME"
- Example SQL:
- Create Parameter:
- Click Parameters
- Click Add
- Enter Name for parameter
- Example Name:
- CM
- Example Name:
- Select 'String' from data type dropdown
- Select 'Menu' from Parameter Type dropdown
- In Display label, Enter a user defined name
- In List of Values dropdown, click the list of value created in step 3
- Click 'Multiple Selection' and 'Can select all' checkboxes
- Create Data Sets
- Click Data Sets
- On diagram page, click New > SQL query
- Enter a name for the dataset
- Select the datasource from dropdown
- Select 'Standard SQL' in the type of SQL dropdown
- 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
- select
- Example SQL:
- Click OK
- Test Results:
- Click data link
- Click a single option from parameter
- Click View
- Results display successfully
- Click more than one option from parameter
- Click View
- Note the error which occurs
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |