OTBI Simple Aggregate Question - Count Does Not Match Details

Bart S

I have a very simple OTBI Analysis using the Worker Assignment Event Real Time where I show the number of hires using the following columns:

1. EXTRACT (MONTH FROM "Assignment Event Details"."Effective Start Date")
2. EXTRACT(YEAR FROM "Assignment Event Details"."Effective Start Date")
3. COUNT("Worker"."Person Number")

There is a filter on the Action Code = HIRE.

This query returns a count of 36.

HOWEVER, If I remove the COUNT function on the Person Number column, 33 Person Numbers return.

When I write a SQL query against the data base using the same constraints in place for the OTBI query, I get 33 as the aggregate count. 33 is the correct number. 33 Person Numbers will return from the SQL if I remove the aggregates and simply list Person Numbers and those 33 person numbers are the same ones from the OTBI analysis when I remove the aggregate count.

If I redo the OTBI query and simply use the # of Hires measure from Assignment Event facts, I get 36. Anytime I use an aggregate on OTBI, the number is off from SQL or OTBI when I simply list the row data with no aggregate.

Any ideas why this is?

OTBI COUNT HIRE1.GIF

OTBI COUNT HIRE2.GIF

OTBI COUNT HIRE3.GIF

SQL COUNT HIRE.GIF

Raghavendra Raghunath-Oracle

Hi @Bart S in your OTBI query, can you add the following columns along with the 3 that you have mentioned above

• Assignment Event Details.Assignment Number
• Assignment Event Details.Assignment Type
• HR Action.Action Type Code

You may notice that for 3 of your workers there are 2 assignments, Pending Worker and Employee

Can you please share the report output?

Raghu

Bart S

Hi @Raghavendra Raghunath-Oracle,

Thanks. The Assignment Type was the issue. I should have known that as I did account for it in my SQL but totally missed it in OTBI. I use SQL mostly and just am getting acquainted with OTBI. And we don't typically use the Pending Worker step here so I wasn't even thinking about that, surprised we had a few. We used to routinely run through the Pending Worker setup, but for a variety of reasons we just don't now.

Really, thanks again. I appreciate the responsiveness.

Bart