Create an OTBI Analysis with a Subquery Filter

Create an OTBI Analysis with a Subquery Filter

1. Start in Oracle Transactional Business Intelligence

2. Click Create and choose Analysis to create a new analysis.

3. In Select Subject Area, choose Workforce Profiles - Person Profile Real Time.

4. Expand the subject area and select the columns:

   1. Expand Person ID.

   2. Choose Person Number .

   3. Expand Name.

   4. Choose Assignment Status Type. Add columns to the report.

5. Click the Setup icon in the Assignment Status Type column. Filter the Assignment Status Type column to show only Active employees.

6. Choose Filter.

7. In the New Filter Dialog, set Operator to is equal to/is in, and set the Value to Active.

   Click OK.

8. In Subject Areas, expand Person Profile, then Person Profile (again) then choose Count of Profiles. Add Count of Profiles to your analysis.

9. Click the Results tab.

10. Click the Criteria tab. This analysis shows a count of profiles. This isn’t very helpful, so modify the query to filter only on workers who have at least one profile.

11. Click the Results tab.

12. Click the Table Properties icon.

    • Turn on Content Paging to make it easier to scroll and click OK.

13. Click Save. Save the analysis.

    Assign a Name and Description to the analysis and click OK.

14. Search for or scroll down to Workforce Management – Worker Assignment Real Time. Now, create an analysis that shows workers who do not have profile data.

15. To start, create a new analysis.

16. Select the Subject Areas for the report. Add the columns you want to include in your report.

17. Choose Person ID, Person Number, Name, Assignment Number, Assignment Status Type.

18. Collapse Worker and expand Manager.

19. Choose Manager Name

20. Collapse Manager and Expand Business Unit

21. Choose Business Unit Name

22. In the Person ID column, click the Setup icon. Create a filter to find all of the users who have not created profile data.

23. Click Filter

24. Set Operator to Is based on results of another analysis

    Browse for the analysis that you previously saved. There are many saved analyses. Choose the one created earlier.

25. Click OK

26. Set the Relationship to is not equal to

27. Set Use Values in Column to Person ID.

28. In the Assignment Status column, click the Setup icon, then choose Filter.

29. Create a filter on the Assignment Status Type column so that only active workers are shown.

30. Set the Operator to is equal to/is in

31. Set the Value to Active.

    Click OK.

32. Click the Results tab. Return to the results tab.

33. Click the Table Properties icon

34. Choose Content Paging to make it easier to scroll the report.

    This is a list of workers who are active, but do not have profile data associated with them.