It's all about the answers!

Ask a question

Create a JRS report on Users and Roles


James Sullivan (15932) | asked Dec 23 '19, 12:21 p.m.
retagged Jan 02 '20, 10:55 a.m. by Jackie Albert (1.6k14744)

Create a JRS report on Users and Roles. Is there a way to create a JRS report on the users and roles and in my RTC project area? We need to report on the teams as well.

3 answers



permanent link
Davyd Norris (1.1k8) | answered Dec 23 '19, 10:11 p.m.
You can't get that information via JRS but you can via OSLC or the reportable REST API with a query like this:

<your server>/ccm/rpt/repository/foundation?fields=foundation/teamArea[name=<team name>]/roleAssignments/(contributor/itemId|contributorRoles/name)

permanent link
Jackie Albert (1.6k14744) | answered Jan 02 '20, 11:01 a.m.
edited Jan 02 '20, 11:02 a.m.

 You can do this in JRS against the data warehouse, but you need to build a custom SQL report. All the data is available in the data warehouse if you know how to query for it. 


You'll want to look in the RIODS schema for these tables:  TEAM, TEAM_ASSIGNMENT, TEAM_MEMBER_LOOKUP, TEAM_RESOURCE_LOOKUP, TEAM_ROLE, TEAM_ROLE_MEMBER_LOOKUP

Here's a sample SQL query that will give you the Teams, Roles, and users who have these roles for a specific project
SELECT DISTINCT P.name as Project, T.NAME as Team, TR.NAME as TeamRole, R.name as Resource FROM RIODS.TEAM T, RIODS.TEAM_ROLE TR, RIODS.PROJECT P, RIODS.RESOURCE R, RIODS.TEAM_ROLE_MEMBER_LOOKUP L WHERE P.name = 'your project name here' and TR.TEAM_ID = T.TEAM_ID AND L.RESOURCE_ID = R.RESOURCE_ID AND L.TEAM_ROLE_ID = TR.TEAM_ROLE_ID AND P.project_id = T.project_id


Comments
Davyd Norris commented Jan 06 '20, 5:31 p.m.

This is useful to know - thanks!


permanent link
Daniel Moul (4.7k1118) | answered Jan 06 '20, 8:11 a.m.
FORUM MODERATOR / JAZZ DEVELOPER

Expanding on Davyd's answer ... I believe some teams use the Jazz Foundation API to pull the data directly. See https://jazz.net/wiki/bin/view/Deployment/CLMProductAPILanding


Your answer


Register or to post your answer.