How do I create an OTBI analysis with a subquery filter?
Introduction, title, and music.
In this video, you will learn how to create aquery that includes a subquery.
To begin, create a new analysis.
Click Create.
Click Analysis.
Select a subject area.
Click Workforce Profiles > Person Profile Real Time.
Now add columns to your analysis.
In Subject Areas, expand Workforce.
Click Person ID, Person Number, Name, and Assignment Status Type.
Filter the Assignment Status Type column to show only Active employees.
In the Assignment Status Type column click the Setup icon.
Click Filter.
In the New Filter Dialog, in the Operator field select is equal to/is in.
In the Value field select Active.
Click OK.
Add Count of Profiles to your analysis.
In Subject Areas, expand Person Profile, then Person Profile (again), then click Count of Profiles.
Click the Results 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.
Highlight the Count of Profiles column.
Click the Criteria tab.
In the Count of Profiles column click the Setup icon.
Click Filter.
In the New Filter Dialog, in the Operator field select is greater than.
In the Value field select 0.
Click OK.
Click the Results tab.
Turn on Content Paging to make it easier to scroll.
Click the Table Properties icon.
Turn on Content Paging and click OK.
Save the analysis.
Click Save.
Assign a Name and Description to the analysis.
Click OK.
Now, create an analysis that shows workers who do not have profile data. To start, create a new analysis.
Click Workforce Management > Worker Assignment Real Time.
Add the columns you want to include in your report.
Select the Subject Areas for the report.
Click Person ID, Person Number, Name, Assignment Number, Assignment Status Type.
Collapse Worker.
Expand Manager.
Click Manager Name.
Collapse Manager.
Expand Business Unit.
Click Business Unit Name.
Create a filter to find all of the users who have not created profile data. There are many Saved Analyses available, but for now choose the one created earlier.
In the Person ID column, click the Setup icon.
Click Filter.
In the Operator field select Is based on results of another analysis.
Browse for the analysis that you previously saved.
Click OK
In the Relationship field select is not equal to.
In the Use Values in Column field select Person ID.
Create a filter on the Assignment Status Type column so that only active workers are shown. Return to the Results tab.
In the Assignment Status column, click the Setup icon and then click Filter.
In the Operator field select is equal to/is in.
In the Value field select Active.
Click OK.
Click the Results tab.
Turn on Content paging again.
Click the Table Properties icon.
Click Content paging.
Click OK.
This is a list of workers who are active, but do not have profile data associated with them.
Scroll through the report.
Thanks for watching.
Oracle copyright, music.