Insight: RTC user allocation report

Hello,

I'm looking for the way to report users allocation % and availability time in the case when users are participating in many RTC projects.

So I would like to create report like this:

April Mike Project A 30 % 30 hours
Project B 20% 20 hours
Project C 50 % 50 hours
Ivan Project A 70 % 70 hours
Project B 30% 30 hours

May .........

Any suggestions?

-thanks in advance

Hi,

How are you modeling this in RTC? Is it the resource allocation in the user editor? If so, this information is not currently extracted to the data warehouse. It is exposed in the RTC Reportable REST API, so it should be possible to customize the ETL to store this information.

james

What is achievable using what is out of the box is: using each owner's workitems' time_spent, and add up to project level to decide each person spends how many hours on each project.
workitem's time_spent is loaded into riods.request.actual_work, and further calculated in ridw.request_metrics.actual_work

Hi Junli,

Thanks for your response!

My goal is rather to display available time not spent time.

How could I take into account allocation % and individuals' schedule?

For example Person A works 40 hours per week but next week he will attend 2 days training. I know that this person has 2 projects and his allocation for Project 1 is 25% and for Project 2 is 75%.

So I have Person A has 40 - 2*8 = 24 hours available during next week.

Taking into account his allocation numbers we have

Project 1 - 24/4 = 6 hours
Project 2 - 24*3/4 = 18 hours

How could I extract this info from data warehouse or CCM database?

-thanks in advance

if you are looking for individual's available time, then the data is in user's editor, as James mentioned in comment2, it's not extracted into data warehouse by default.
you may need to customize ETL to load this data into riods.resource and riods.timesheet, and do further calculation in ridw.resource_utilization_metrics.

Thanks Junli!

I've got it. So question is what RTC database tables/columns should I use in such ETL job?

-thanks in advance