Custom Reporting with Custom Properties

When you run against a project group, the custom properties will be different for each of the projects, so the values themselves will not be the same for each project.

In addition, the projects may not use the same fields for the same value (e.g. Browser could be CUS_01 in project PR1 and CUS_02 in project PR2). However assuming that is NOT the case, then if it is just the values that are different then you could use the following:

select R.REQUIREMENT_ID, R.REQUIREMENT_NAME, R.TEST_CASE_ID, R.TEST_CASE_NAME, T.CUST_02, C.NAME
from SpiraTestEntities.R_RequirementTestCases as R
inner join SpiraTestEntities.R_TestCases as T on R.TEST_CASE_ID = T.TEST_CASE_ID
left join SpiraTestEntities.R_CustomListValues as C on cast (T.CUST_02 as int32) = C.CUSTOM_PROPERTY_VALUE_ID and T.PROJECT_ID = C.PROJECT_ID
where R.PROJECT_GROUP_ID = ${ProjectGroupId}

where we added an additional clause to join the projects explicitly:

...and T.PROJECT_ID = C.PROJECT_ID

Regards

Jim

Hello Jim,

Thanks for your response. We've instructed all projects to use the same way of custom values, however it runs into another issue, for example: project 1, cust02 is Program, value is free form Text, but for project 2, cust02 is also Program, value is a List of predefined program (Civil, Electrical...). Consequently the report generated error cannot convert character correctly etc...In this case, it's type mismatch for the custom properties

Anyway to fix?

By the way, if custom value is Date type, error with conversion happens as well. Possibly we cannot apply "C on cast (T.CUST_02 as int32) = C.CUSTOM_PROPERTY_VALUE_ID"??

Many thanks