Create a Cross Subject Query with a Date Prompt

Here's how you can create a cross subject area query that includes a date prompt.

Create a Workforce Management - Worker Assignment Real Time analysis.

Create a Cross Subject Query with a Date Prompt

1. Start in Oracle Transactional Business Intelligence

2. Click Create and select the Workforce Management - Worker Assignment Real Time subject area.

3. Expand the Worker subject area. Add the Person Number and Name columns.

4. Select Name and First Name.

5. Select the Filter icon.

   You only want to view active workers, so add the Assignment Status Type Code column.

6. Choose More Columns.

7. Choose Assignment Status Type Code and click OK.

8. Set the Operator to is not equal to/is not in. Then, add a filter so only Active workers are displayed.

9. Set the Value to Inactive and click OK

10. Click the Results tab.

11. Click the Table Properties icon. Enable Content Paging to show the scroll bars on your report, then return to the Criteria tab.

12. Select Content Paging and Click OK.

13. Return to the Criteria tab.

14. Collapse the Worker Subject Area. You must cross subject areas to include a measure from every subject area. So, in this case, pull in the Assignment Count column.

15. Expand the Worker Assignment Subject Area and select Assignment Count.

16. Return to the Results tab. Now view the results.

17. Return to the Criteria tab. The assignment count is all 1’s. We don’t need to see that, so we will hide it.

18. Select the Settings icon in the Assignment Count column

19. Choose Column Properties.

20. On the Column Format tab, choose Hide.

21. Click OK.

22. Click the Results tab. Return to the Results tab. Although the criteria is still considered for the report, it is no longer displayed.

23. Click the Criteria tab. Return to the Criteria tab.

24. Collapse the Workforce Management Worker Assignments Subject area

25. Click the Add/Remove Subject Area icon Now add the second subject area.

26. Add Compensation - Salary Details Real Time

27. Click OK.

28. Expand Compensation Salary Details Real Time, because you need a measure for every subject area, add Annualized Full-Time Salary.

29. Expand Salary and choose Annualized Full-Time Salary.

30. Click the Results tab. The result is a cross-subject area query that includes annualized full-time salary.

    Note that some people do not have a Salary.

31. Click Advanced tab. Next, set the query to be effective as of a specific date.

32. Paste the following code in the Advanced SQL Clauses Prefix area:

    SET VARIABLE PARAM_EFFECTIVE_DATE='@{ P_DATE }{@{system.currentTime}[yyyy-MM-dd]}'; 

    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.

33. Click Apply SQL.

34. Click the Results tab. View the results. Notice that the report, displays the same results because it's using the same effective date.

35. Click the Prompts tab. So, set up a prompt to provide the effective date.

36. Choose Create a Variable Prompt.

37. Edit the new prompt as follows:

    1. Prompt variable: P_Date

    2. User Input: Choice list

    3. Choice list values: All Column Values

    4. Column: Select any date value such as Worker Person Date of Birth.

    5. In the Default Selection SQL results statement area, enter: select current_date from “”Workforce Management – Worker Assignment Real Time” Assign a variable prompt called P_DATE and label it Effective Date.

38. Click OK

39. Edit the prompt again to change the User Input from Choice List to Calendar and click OK. Here is what the Effective Date Prompt will look like.

40. Click Save to save your prompt as Date Prompt.

41. Choose Date Prompt.

    Click OK.

42. Click the Catalog tab.

    Now test the date prompt.

43. Click Date prompt. The report is updated to reflect the new date.