Oracle Cloud Reporting Tips and Tricks
Oracle ERP Cloud, HCM Cloud and SCM Cloud are becoming more and more popular and mainstream ERP applications. With the number of increasing users, there are a lot of questions on how to get data our of Oracle ERP Cloud. How do you use OTBI? What are the gotchas with OTBI and BI reporting in Oracle ERP or HCM Cloud.
In this article, we are going to share some important tips, things to remember and how to effectively use Oracle OTBI/BI for ERP Cloud applications. Just to remind, you can always use SQLConnect to query data from your Oracle SAAS Cloud applications.
What is the real-time subject area?
Real time subject areas in Oracle OTBI will only bring the latest record. These are not useful if you want to build trend reports or comparisons.
How can you make custom reports or standard HR dashboard available to only some employees based on their roles, without them going into OTBI?
There are many ways in which you can accomplish this. Here are a few:
- Create an OTBI dashboard that is accessed via a new menu option that is secured to a role.
- Create an OTBI dashboard or just one analysis and embed them into HCM Cloud – There is an example Line Manager dashboard with instructions on how to add it to My Team.
- Create OTBI infolet analyses and embed them into Infolets (Infolets can also be secured to roles). These can then include drills to full-size analyses or a dashboard.
- Email reports to workers using Agents.
How can I get my pivot table to display the number zero instead of leaving the cell blank?
Use a custom format of “#,##0;-#,##0;0” – the first part is for positive numbers, the second for negative and the third for null.
How can I make parameters dependent on each other?
Use the Limit Values by under Options when you create a dashboard prompt. If you select this check box, you can select any/all the columns that must restrict the values from the current column.
OracleCloudTools SQLConnect offers bind variables feature that will allow you to create ad-hoc queries and run them with different variable values against your Oracle HCM Cloud, Oracle ERP Cloud, Oracle SCM Cloud, Oracle PPM Cloud right from your desktop.
How can I conditionally format one column based on a value in another?
Ensure that Value Suppression is set to Repeat in the column properties of the column in which you want to apply the conditional format. More information here.
What are Request Variables used for?
A Request Variable is used to override the value of session variable in a particular session. For example: if there is a session variable named ABC which renders value as ‘New’. You can set the value of this session variable as ‘Old’ for that specific session using the following syntax SET VARIABLE ABC = ‘OLD’; in Advance tab > Prefix column. The request variable overrides the value of session variable only for this request, the session variable will have the actual value ‘New’ outside of this request.
Does a re-usable filter need to be from the same subject area as the analysis it is applied to?
A: Not necessarily. If it is coming from other subject areas, instead of ‘Is Prompted’, you need to pass values using presentation variables.
Where is the best place to save filters and columns?
They can be saved anywhere, but if you save them under the subject area name then they are not available in queries that are not using that subject area.
Does OTBI deliver the dates with time in the timezone of the user or the server?
The server.
What circumstances would Selection Steps be useful?
Filters are applied before the query is aggregated meaning that it could affect calculations and measures because the query is affected. Whereas selections steps are applied after the query is aggregated meaning that the only thing that is affected is what the user will see, this will not affect calculations.
What does DESCRIPTOR_IDOF(“Workforce Management – Worker Assignment Real Time”.”Position”.”Position Active Flag”) = ‘A’ mean?
This is a feature of the OBIEE Admin tool, i.e the RPD. Certain columns can be specified as ‘Double’ columns which basically holds the code and value. This helps in queries and filters in improving the performance and passing values irrespective of what it is as actual value.
Can I use OTBI to burst output to many users?
Yes. OTBI provides bursting via agents. Create an analysis that includes the lowercase company email address or username. This column can then be used in the agent to split the delivered report so that each email/username only receives the relevant rows.
Can I report current and previous values side by side?
Yes. One common example is reporting the current assignment values and the previous ones after an assignment change. This report must be written in Logical SQL. More information on Logical SQL can be found here: https://docs.oracle.com/en/cloud/saas/business-intelligence/index.html.
How do I query Oracle ERP/HCM Cloud data from my desktop using a tools like toad or SQL developer?
You use SQLConnect !
Can I report a list of workers who don’t have a goal (or absence, or something else)?
Yes.
How can I export a whole OTBI dashboard to Excel?
