How do I create a cross subject area query with a date prompt?
Introduction, title, and music.
In this video, you will learn how to create a cross subject area query that includes a date prompt.
To begin, create a Workforce Management - Worker Assignment Real Time analysis.
Start on the Home page in Oracle Transactional Business Intelligence (OTBI).
Click Create > Analysis.
Click Workforce Management > Worker Assignment Real Time.
Add the Person Number and Name columns.
Expand the Worker subject area
Click Name > First Name.
You only want to view active workers, so Add the Assignment Status Type Code column.
Click the Filter icon.
Click More Columns.
Click Assignment Status Type Code.
Click OK.
Then add a filter so only Active workers are displayed.
In the Operator field select is not equal to/is not in.
In the Value field select Inactive.
Click OK.
Click the Results tab.
Enable Content Paging to show the scroll bars on your report, then return to the Criteria tab.
Click the Table Properties icon.
Click Content Paging.
Click OK.
Click the Criteria tab.
You must cross subject areas to include a measure from every subject area. So, in this case, pull in the Assignment Count column.
Now view the results.
Collapse the Worker Subject Area.
Expand the Worker Assignment Subject Area.
Click Assignment Count.
Click the Results tab.
The assignment count is all 1’s. We don’t need to see that, so we will hide it.
Click the Criteria tab.
In the Assignment Count column, click the Settings icon.
Click Column Properties.
Click the Column Format tab.
Click Hide.
Click OK.
Return to the results tab. Although the criteria is still considered for the report, it is no longer displayed. Return to the Criteria Tab.
Click the Results tab.
Click the Criteria tab.
Now add the second subject area.
Collapse the Workforce Management Worker Assignments Subject area.
Click the Add/Remove Subject Area icon.
Click Add Compensation > Salary Details Real Time.
Click OK.
Because you need a measure for every subject area, add Annualized Full-Time Salary.
Expand Compensation Salary Details Real Time.
Expand Salary.
Click Annualized Full-Time Salary.
The result is a cross-subject area query that includes annualized full-time salary. Note that some people do not have a Salary.
Click the Results tab.
Next, set the query to be effective as of a specific date.
Click the Advanced tab.
This code sets a variable called Param effective date to a value of PA_DATE, which we haven't defined yet. If a PA_DATE is not set, then the current system date and time are used. If there's no parameter, it will use the code date time.
In the Advanced SQL Clauses Prefix area enter SET VARIABLE PARAM_EFFECTIVE_DATE='@{ P_DATE }{@{system.currentTime}[yyyy-MM-dd]}';
Click Apply SQL.
Highlight the newly added SQL statement.
View the results. Notice that the report, displays the same results because it's using the same effective date.
Click the Results tab.
So, set up a prompt to provide the effective date.
Click the Prompts tab.
Click Create a Variable Prompt.
Assign a variable prompt called P_DATE and label it Effective Date. For the User Input, choose choice list, and for the Choice List Values, choose All Column Values. Then select a column that uses the date format, such as Worker Person Date of Birth. In Options, choose SQL results as the default selection, and enter an SQL statement that will generate the list of values. Here is what the Effective Date Prompt will look like. Change the prompt to Calendar instead of Choice List.
Edit the new prompt as follows:
Prompt variable: P_Date
User Input: Choice list
Choice list values: All Column Values
Column: Select any date value such as Worker Person Date of Birth.
In the Default Selection SQL results statement area, enter: select current_date from “”Workforce Management – Worker Assignment Real Time”
Click OK.
Edit the prompt to change the User Input from Choice List to Calendar and click OK.
Notice that you now see a Calendar icon.
Highlight the Effective Date field and Calendar icon.
Save your Prompt as Date Prompt.
Click Save.
Click Date Prompt.
Click OK.
Now test the date prompt. I’ll select a date in the past.
Click the Catalog tab.
The report is updated to reflect the new date.
Click the Date prompt.
Select a date in the past.
Highlight places in the report to show they have changed.
Thanks for watching.
Oracle copyright, music.