i'm trying to filter my task report to display only those that are assigned to users not having PTO today.
Using the filter "Assigned to >> has time off" equal FALSE doesn't do the trick as it excludes those that never ever have used there PTO. My report has filter "Planned start date" equal TODAY applied so I would love to see the results narrowed down to today only.
Is this even possible? any suggestions for workaround?
Topics help categorize Community content and increase your ability to discover relevant content.
Rafal, the PTO data points are locked up in a very tight and very secure box. In other words, I've been trying to find ways to utilize it and have been almost wholly unsuccessful EXCEPT on calendars. In a report though...if you find a way I would love to know.
I'm happy to help people try and figure it out if everyone wants to pitch in with brainpower. (purely an experiment, because I don't know if this is possible or what people have tried)
I took a quick look at the API explorer and I think the collection people are going to want to dig into is called reservedTimes (aka Time Off). If I'm reading this correctly, a single reservedTime has an ID, a start date and an end date, amongst other things.
Best way to see things work is probably experiment in a user report to start with and if you can successfully isolate people having PTO or not having PTO today, you can try and see if that will push into a task report through an exists statement (I'm assuming people only care about the primary assignee having PTO as opposed to multiple users having it).
A sample call in a user report would look like this:
If I'm reading it correctly it will show me everyone who has set a PTO future start date.
To filter PTO-users out, we would want some combination of start date not equal to today, plus end date is not equal to today, plus leave off any occurrence where start date is less than today and end date is greater than today, to cover:
So... is that possible, and if so what does the filter look like in a conventional user report?
PS, sometimes it's fun to figure out both scenarios (people having PTO today vs not having) and seeing if one is easier than the other, or if both might be useful in different situations.
I did reach out to support about this and here is their answer:
Looking over the tables available, this would not be even remotely possible without at least 3 table jumps. Those are what are called "Exists Filters". Exists filters have the potential to be very complicated, and to look into those further we would definitely refer you to work with our Professional Services team, as Support is unable to assist with those.
I'm afraid I don't have budget at the moment to accomplish this, but might need look into this in future.
Another limitation for us would be that we are already heavily using fusion with most of scenarios being in use and reaching our limits :) I would be looking not only for working solution, but also smart one :)
I'm not sure this meets your particular requirement, but as a (simplified) workaround...
Perhaps you could retrieve the Assigned To Last Login Date on your report, show it in Green if it is between $$TODAY and $$TODAY+1d (or in Red, otherwise) to indicate who is NOT on PTO (presumably), but HAS logged in (encouragingly), and MIGHT be working on such an assigned task (ideally).
If you like that concept, you could then go one step further and filter by Last Login Date, perhaps on two separate but similar reports: "Work that MIGHT be Happening" using the Green concepts, and "Work that SHOULD be Happening (but isn't yet)" using the Red concepts.
this is probably the easiest way to get the closest result I need without too much effort.
thanks so much for this suggestion!
My pleasure Rafal,
I can imagine this evolving into a heavy-handed but motivating Work That Is Slipping dashboard, perhaps layering in a few additional pressure points such as a Last Logged In chart, a Days Since Hours Logged Per Task grid, and a Famous Last Words report showing each person’s last update.
I’m interested to hear how you get on; good luck!
Given that everyone needs to login to the system in order to review and start the work your suggestion pretty much solves our problem. The moment someones is online I can see their task. Ideally I could now that ahead them being online, but we can live with this for now. I'm also in touch with Skye to learn more about EXIST filters and how could they potentially be applied for my use case