I managed to solve the problem by applying a filter using the MAX function :
"Person Work Permit"."Expiration Date" = MAX("Person Work Permit"."Expiration Date" by "Person Details"."Person Number") on the Expiration Date field
Hi Alan,
I have similar requirements for DBS Checks and Work Permits, but I use RANK instead of MAX. I have a calculated column called 'Rank of Renewal Date' with the following formula:
RANK("Workforce Management - Documents of Record Real Time"."Document of Record Details"."PER_DOCUMENTS_OF_RECORD_DFF_RENEWAL_DATE_" BY "Worker"."Person Number")
I then use this column in the Analytic Filter as shown in the screenshot below.
Welcome to Oracle Analytics Community.
Can you please elaborate your query? And share the product details where you want to get this data? A detailed question helps the community members to understand the requirement and answer appropriately.
Thanks.
Hi MandeepGupta
Thanks for responding
I have an OTBI report that is returning rows containing the "Person Details.Employee Number" and "Person Work Permit.Expiration Date" from the WFM- Person Real Time subject area.
I need to be able to return a single row for each person that contains the latest expiry date.
I have seen solutions on here involving use of the max function in a new column such as MAX(
"Person Work Permit.Expiration Date" by "Person Details.Employee Number") and adding a filter where the value retuned in the new column is equal to the value in the Expiration date column
But this doesn't seem to be working
Thanks for sharing the details, Alan. What issue you are facing with this approach? Is it not giving any data or gives incorrect data?
Thanks for the update, Alan. What was the issue previously? Just curious to know.
The method involving creating a new column containing the MAX function to compare against, produced null values for some rows and is clunky in comparison to using the max function directly in the filter, it is similar to the ROWNUMBER() over partition method when in SQL
We have built an OTBI report intended for Line Managers to view their subordinates’ emergency contact details. The report uses the Workforce Management - Person Real Time subject area. When a Line Manager runs the report, the emergency contact fields (e.g. EC Phone Type, EC Phone Number, EC Email Type and EC Email Address)…
Hi All, Good Day. We have created one custom OTBI report and it has "Business Unit" as of one of the parameter. The related list of values is showing all the business units and we want to restrict it to the user who has the related Business Unit data access set access. Please let us know how to achieve this in OTBI report.
I have a Leave Details Report with the condition below to restrict the report based on the logged-in user's AOR. However, the report is not returning any data for any legislation, when users run it under the 'About Me' context.
Our Requisition OTBI analysis is having many values Requisition Number Requisition Title Requisition Status Hiring Manager Full Name Recruiter Full Name am able to see values for all. When am passing Prompt(Hiring Manager Full Name or Recruiter Full Name) am not able to see the data. Am sure we have values/data for passed…