Hello everyone,
I wanted to see if anyone has something like this already built out or if anyone has any ideas I could try? To clarify the Title of this post, I'm not asking how we can run a report as of X date. I'm looking to have a daily active report where every field returns live data, except one additional field that returns the name of whoever was that employee's manager 30 days ago.
We have a need to routinely run an employee active list that includes a field for "this person's manager as of 30 days ago". We already know about some delivered options for this via EmployeeTransactionHistory, EmployeeFieldHistory, some turnover reporting, etc., but we specifically want to add it to our regular Active report. We're trying to avoid creating a new Derived field because we have 70,000+ active resources and Derived fields tend to slow reporting down as they're scanning for what to return on the report.
I don't know if this is possible but it'd be great if we could Personalize/CreateReport and simply add some text to a new field that would allow us to calculate this on only that list/report. In the same way we can string Relations together and start them with first, sum or last, I'd like to be able to enter a calculation right into the field while personalizing. If I were directly editing a list's LPL, I'd be able to make a column that's something like (StartDate - 30) and it would calculate 30 days before their Start Date. Is there a way to do something like that where it looks for the value of a field as of X days ago? I was able to make (PrimaryWorkAssignment.DirectSupervisor as of (current date - 30)) return a value but it's only returning a number. That's somewhat helpful in that I can see someone who reported DirectSupervisor #100 30 days ago now reports to #200, but I'm trying to add the Name on the report. This tactic only seems to work if it's going to return a number, even if the delivered field is text. Tweaking this to return a field that's usually text just produces zeros.
We've also had limited success with derived fields like this:
EmployeeTransactionHistory is a BusinessClass
owned by hr
Derived Fields
PrevWorkType is a DerivedField
type is Alpha size 30
return Employee(as of EffectiveDate - 1).WorkType
So that final statement basically returns "WorkType as of a day before the most recent change". We're trying to get away from derived fields, though. If I try to add something like PrimaryWorkAssignment.DirectSupervisor(as of EffectiveDate - 1) to a Personalization, for example, I haven't been able to find any sort of combination that would be valid, either as a Field or Compute.
Does anyone know of a way to express LPL within the Personalize/CreateReport screen that works like Derived fields can work (without configuring new derived fields, which we're trying to get away from)? I could source this info from multiple reports and do excel work to get what I need but I'm hoping someone has a tip for how to do this directly within personalization. Thank you for reading,
