Oracle Business Intelligence

Products Banner

Looking to get the aggregated value for Time in Jobs(Days) in Analytics Answers

Received Response

I have created a small report with few columns

Year, Job Title, First Name, Last Name, Start_Date, End_Date, Assignment_Type and "Total No of Days in Job"

for one job title there is a history with few lines of info

Need to generate the report with the Date First Assignment Started and The Date Assignment Ended (If not ended select current date) and the total number of jobs in days between this period.

I am attaching the sample.

Currently, with Time in Job Days(Measure) defined in RPD.

Need Min Assignment Date and Max Assignment End Date


Need assistance in how we can use the aggregate functions in rpd to achieve this.

Any help would be appreciated.





  • If you use the aggregation functions MIN and MAX in the RPD for a column without setting any "scope", they will work across the whole dataset returned by the analysis. This means that a MIN(date) will not be the minimum date for a given job number, it will be across all the rows retrieved.

    You can use content levels to force the aggregation to happen inside a fixed scope.

  • Hi Gianni thanks for your inputs,

    I tried by using Min() and Max() and used the content level as Job Title (Detail Level) and tested but when I ran the report I was not seeing any values for the dates with Min and Max. Only the Count was aggregated to complete data set.

    So I see that in the query there is no group by clause to aggregate at Job Title level.

  • Thanks Gianni, I implemented the min, max and Time in Job(Days) calculations in RPD and it is working fine now.

    Very much appreciate your inputs in resolving the same.