custom attributes of type Small String not displaying in JRS report
In our JRS reports, adding a column for an enumeration custom attribute works, displays the attribute value. But a column for a Small String custom attribute displays nothing. A comment on https://jazz.net/forum/questions/167078/is-it-true-that-jrs-reports-do-not-support-custom-attributes/167089 says "almost all of the simple (e.g., Boolean, String, Enumeration List) and complex (e.g. Contributor, Iteration, Project Area) types are supported" but other List types are not. So, what's wrong that our string attribute does not display?
Here's the generated SQL for a simple report that displays one work item. APARName is a custom attribute of type Small String.
SELECT DISTINCT T1.REFERENCE_ID,
T1.NAME AS URL1_title,
T1.URL AS URL1,
T2_1.LITERAL_NAME
FROM RIDW.VW_REQUEST T1
LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T2
ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='aparname'
LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T2_1
ON T2_1.EXTERNAL_ID=T2.VAL AND T2_1.PROJECT_ID=T1.PROJECT_ID
WHERE T1.PROJECT_ID = 6 AND
( T1.REFERENCE_ID = '99575'
) AND
(T1.ISSOFTDELETED = 0)
Accepted answer
It seems this string custom attribute is misinterpreted as enumeration attribute, most likely because its value(s) matched with external id(s) of an enumeration. This is because we don't have completely reliable info about custom RTC enumerations in data warehouse at the moment. It improves in 6.0, and will be resolved in 6.0.1 as it requires schema changes to data warehouse. Note that the problem may go away on its own as the data (for the string attribute) changes.
3 other answers
Thanks Lidija. I'm able to work around the problem by editing the SQL to remove the join with RICALM.VW_RQST_ENUMERATION for string custom attributes. Here's the edited SQL for the example above:
SELECT DISTINCT T1.REFERENCE_ID,
T1.NAME AS URL1_title,
T1.URL AS URL1,
T2.VAL AS APARNAME
FROM RIDW.VW_REQUEST T1
LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T2
ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='aparname'
WHERE ( T1.REFERENCE_ID = '99575'
) AND
(T1.ISSOFTDELETED = 0)
The join with the enumeration table is removed, and the query is changed to read T2.VAL instead of T2_1.LITERAL_NAME. The same fix works for reports that have multiple string custom attributes, removing the join with the enumeration table for each string attribute.
There seems to be a defect when using the SQL editor. Validating the query sometimes displays this error:
CRRGW5602E The variables in the query do not match the calculated variables from the query string. Look for two variables with the same name. 4:47:54 PM
The query variables do not match the variables calculated from the query string. Every variable must have a unique name.
Look for two variables with the same name and rename one of them.
I can avoid that error by setting an alias for each column (e.g. T2.VAL AS APARNAME). This isn't simply a case of duplicate names (e.g. T2.VAL and T3.VAL), as the error happens when there are no such duplicates.
Comments
Jackie Albert
Apr 02 '15, 9:43 a.m.Greg, what version of JRS are you on?
Greg Pflaum
Apr 02 '15, 9:49 a.m.Version: 5.0.2