Error #1
The argument types 'Edm.String' and 'Edm.DateTime' are incompatible for this operation.
(The same applies to any other value in the quotes - it can be also type Boolean and Int32)
This mean that there is probably a comparison of incompatible field types.
In this example there are two values being compared in the end of WHERE statement where R.CUST_08 is initially a Date-type field:
SELECT value R.INCIDENT_ID from SpiraTestEntities.R_Incidents AS R WHERE R.PROJECT_ID = ${ProjectId} AND R.INCIDENT_ID = 1 AND R.CUST_08 < 3
Solution:
What you need to do here is 'translate' the given field to the format that can be compared with the value on the right of the expression: below Entity SQL query uses the CAST operator to cast an expression of one data type to another, so final query will be:
SELECT value R.INCIDENT_ID from SpiraTestEntities.R_Incidents AS R WHERE R.PROJECT_ID = ${ProjectId} AND R.INCIDENT_ID = 1 AND (Cast(R.CUST_08 As Edm.Int32))
Error #2
'RELEASE_ID' is not a member of type 'Inflectra.SpiraTest.DataModel.R_Incidents' in the currently loaded schemas.
This mean that there is no such a column in the given database view (table) used for Custom reporting.
Solution:
In above example, for incidents, RELEASE_ID is not a valid field. You would need to use either DETECTED_RELEASE_ID or RESOLVED_RELEASE_ID for incidents.
More details about available fields per tables can be found here: https://spiradoc.inflectra.com/Reporting/Custom-Report-Tables/
Error #3
System.Data.EntitySqlException: 'dbo.TST_HISTORY_CHANGESET' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly
This error means that the name of the table (view) does not exist and thus, cannot be resolved by the engine - The TST_* tables can not be directly accessed by reports.
Solution:
The table name to be used in Custom reporting in Spira is "SpiraTestEntities.R_HistoryChangeSets" in particular for this error, while it should start with "SpiraTestEntities.R_" for any other tables.
Here is the full list of available custom report tables: https://spiradoc.inflectra.com/Reporting/Custom-Report-Tables/
Error #4
System.Xml.XmlException: The '#' character, hexadecimal value 0x23, cannot be included in a name
Commonly this error is being displayed when two columns from two separate tables with the exact same name.
Example:
SELECT rel.NAME, req.NAME FROM SpiraTestEntities.R_Releases AS rel LEFT JOIN SpiraTestEntities.R_Requirements AS req on rel.RELEASE_ID = req.RELEASE_ID WHERE rel.PROJECT_ID = ${ProjectId}
Solution:
Use aliasing to prevent the problem:
SELECT rel.NAME AS rel_Name, req.NAME AS req_NAME FROM SpiraTestEntities.R_Releases AS rel LEFT JOIN SpiraTestEntities.R_Requirements AS req on rel.RELEASE_ID = req.RELEASE_ID WHERE rel.PROJECT_ID = ${ProjectId}
Error #5
System.Data.EntitySqlException: The entity set or function import '[R_INCIDENTRESOLUTION'] is not defined in the entity container 'SpiraTestEntities'
Solution:
Use the preview option dropdown list to get the correct name of the custom report view, the error indicates that the custom report table name does not exist in the current container.
Error #6
System.Data.EntitySqlException: 'CURRENT_TIMESTAMP' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly
Solution:
Use CurrentDateTime() function instead, since 'CURRENT_TIMESTAMP' is purely T-SQL command and not supported by Entity SQL.
Error #7
System.Data.EntityCommandExecutionException: An error occurred while reading from the store provider's data reader. See the inner exception for details.
And the detailed message says:
[System.Data.EntityCommandExecutionException {0x8013193c}]
Divide by zero error encountered. [System.Data.SqlClient.SqlException {0x80131904}]
Solution:
Make sure that the ESQL query (or its part) not resulting "0" (zero) in the expression, since division by zero is not allowed in basic math.
Error #8
System.Data.EntitySqlException: 'component_name' is not a member of type 'Inflectra.SpiraTest.DataModel.R_Component' in the currently loaded schemas.
This error occurs when the ESQL clauses identify a column name that is either misspelled or does not exist.
Solution
To resolve the error check the schema and give the correct column name.
Example incorrect query:
select R.component_name from SpiraTestEntities.R_Components as R where
R.PROJECT_ID = ${ProjectId}
Example correct query:
select R.name from SpiraTestEntities.R_Components as R where R.
PROJECT_ID = ${ProjectId}