I’d probably create a Power BI report of people with missing timesheets and email those people weekly. If there is a solution within Portfolios, I’d be interested (and no, I’m not the Power BI person on my team, so I don’t know how to do what I suggest).
I’ve thought about that. Problem is I’d have to still deal with sending an email manually. I am not sending it to those specific individuals. That would be easier as I could write my distribution query for the Targeted Subscription to only include resources with an Overdue Timesheets > 0. My distribution list is for managers and those with certain roles (Project Managers, Program Managers, Resource Managers, etc..)
The Targeted Subscription is appealing as it cuts down the maintenance of the list. I ultimately need to be able to shrink the scope of the report (only show resources with Overdue Timesheets >0) OR manipulate the week.
I’ve tried in Sandbox changing the parameters through the FastTrack Configuration but it doesn’t seem to like negative weeks (ts_range_future=-1 doesn’t seem to work)
I understand. But the target of these reports aren’t then time recorders, but the Managers. We have a targeted subscription that pulls specific user roles/groups. This is because we don’t want to have to maintain the Manager distribution group.
We already have a report that sends once a day to the individuals with Overdue Timesheets. But we want to send the email and RES12 report to the Managers.
Let me restate the problem and see if this helps. In my organization I have 650+ individuals who submit timesheets. We’ve identified a list of 80 managers (who have Planview accounts) that need to receive a copy of the RES12 report with all outstanding timesheets. We’ve created a query that goes against the user table that returns the 80 managers based on their user role.
For our Friday missing timesheets (our timesheet periods go Sat to Fri), we’ve developed a targeted subscription that sends the RES12 report to these 80 managers. We’ve used the Targeted Subscription because we didn’t want to maintain the mailing list in standard subscription or in outlook. Basically if a new PM was onboarded, since PM is one of the roles we’ve identified as receiving the report they would receive the subscription automatically.
The Friday report is working well. But we’d also want to send a copy of RES12 to the managers on Monday morning and ONLY include the resources with an outstanding timesheet. Today what I do is run the RES12 report but change the end date parm to exclude the current week. The export it and send via email. We’d like to automate this process and run via job stream as well.
To do this I can only think of two options. Create a resource portfolio that ONLY includes resources where Outstanding Timesheet > 0 or modify a copy of RES12 to only show prior week’s. I’ve tried setting the ts_range_future parameter in the RES12 report (via Fast Track report config) to -1, but it doesn’t seem to recognize negative numbers.
Has anyone tried something similar? The goal is to send the report only to the 80 managers and not to the individual users (which do have sent on a daily basis already).
Your first option might work. That was what I was trying to come up with in my lame attempts to help, lol.
I am leaning toward the first option myself but not sure how to do it. The Overdue Timesheets is a numeric field and can’t be added to a Portfolio options. I am not sure how else to do it. I am assuming calculated fields can’t be added to a Portfolio option as well (I am thinking something like if Overdue Timesheets >0 then True else False).
If the ts_range_future parameter did accept negative numbers then that would have been an option as well. I could have created a RES12A report which is a c/p of RES12 just with that parameter tweaked then have create a Targeted Subscription using that report.
I am sure this is an oddball request but I have to think someone else has a similar use case
Hi Thbutler,
Just curious if you are also exposing any of the My Planview tiles for your resource managers? This can be beneficial and provide them views instantly when they login. This can also aid in increasing adoption for your resource manager roles.
Those are available and some of the managers who have a time reporter license (Team Leads or individuals with one to three direct reports who don’t need a Portfolio Plus License) also have these tiles and the appropriate grants. The problem we also face is adoption. We have several managers that really only log on to do their own timesheet and don’t want to use the reports. They prefer to have the email sent to them directly.
I am hoping with Overviews and Planview ME we could get more people using the tool on the regular.
I would l have to ask my technical resource about the exact details of our solution but the cliff note version is as follows. We do this to help ensure all timesheets are submitted before our finance dept pulls month end data.
- We created a custom report that only shows timesheets with integrate_status != r,i
-
Includes the Manager of Resource Manager, Resource, Time period start, timesheet status & vendor
- We then created a dataset that would help drive the distribution list
-
We have a job stream for “Targeted subscription” and update the job stream monthly to send the report on Mon & Tues, at multiple times each day, the week that our Finance dept pulls their month end data.
The custom report is that something that was fully built or an alias of an existing report? That sounds like that could also work for us.
@Thbutler - sorry for my delay in responding as I wanted to consult with my internal report writer.
The custom report was something we created ourselves, not a Planview report. Are you running a Planview report? You’ll want your report to not have a parameter, and be more dynamic. This will allow you to have a report subscription that is also dynamic and not require setting the parm each time.
We have a technical person in house who writes our reports, so I took this small snippet from the rdl where it focuses on the criteria for “missing” or “not approved or integrated”. We have an additional special attribute that identifies the leader of the resource and so the rest of our report triggers off of that field to know who to email. However, if you had a report written with the following you may be able to create a portfolio for all users and then this report would only pull those who truly have past due timesheets that need to be submitted. Hope this helps you.
/* missing */
select distinct r.resource_code, up.period_start, 'Missing' as sStatus]
from ip.resources r
cross join ip.user_period up
where up.period_start >= (select cutoff_date from ip].eip_control])
and up.period_finish < getdate()
and r.start_date < up.period_start
and not exists (select 1 from ip.time_reported tr where tr.resource_code = r.resource_code and tr.period_number = up.period_number)
union all
/* not approved or integrated */
select distinct r.resource_code, up.period_start,
case
when tr.integrate_status = 'i' then 'Progressed'
when tr.integrate_status = 'e' then 'Entered'
when tr.integrate_status = 'p' then 'Signed Needs Approval'
when tr.integrate_status = 'd' then 'Disapproved'
when tr.integrate_status = 'i' then 'Progressed'
end as Status]
from ip.resources r
inner join ip.time_reported tr
on r.resource_code = tr.resource_code
inner join ip.user_period up
on tr.period_number = up.period_number
and tr.integrate_status not in ('i','r')
and up.period_start >= (select cutoff_date from dip].bip_control])
and up.period_finish < getdate()
@Thbutler - modification to the “missing” piece
/* missing */
select distinct r.resource_code, up.period_start, 'Missing' as 'Status]
from ip.resources r
inner join ip.ip_user u
on r.logon_id = u.user_name
and u.role_code is not null
cross join ip.user_period up
where up.period_start >= (select cutoff_date from sip].sip_control])
and up.period_finish < getdate()
and r.start_date < up.period_start
and not exists (select 1 from ip.time_reported tr where tr.resource_code = r.resource_code and tr.period_number = up.period_number)
@pamela.sargent thanks so much. I’ll have to give this a look. I’ve not written a custom report in Portfolios before but I can always give it a shot.
