Learn to create a simple report in oracle cloud
In this article, we provide a detailed guide on how to create OTBI reports in Oracle Cloud, with step-by-step instructions. However, sometimes you may only need to quickly extract data from application for analysis. For example, you might want to extract a list of employees from a particular legal employer with their current absence plan balance, or retrieve data from a user-defined table. While you may have a SQL query ready to extract the data you need, the problem is that using Data Model (or for simple understanding you can also say SQL Query), you can only extract a maximum of 200 rows.
To help you overcome this limitation, we will outline the steps to create a simple report that can extract more than 200 rows.
Get SQL query to fetch required data
To complete this exercise you will need a SQL query that will fetch some data from the application, if you already have one that is great, if not, you can head over to this post » Oracle Fusion (Cloud) Reusable SQL Queries and copy sql query shared to get Employees Absence Plan Balance.
Create data model to fetch required data
Next we need to create a data model to fetch required data, for the same, please follow below navigation
Navigation : Tools » Reports and Analytics » Browse Catalog » Create » Data Model
Click on the Plus (+) icon under the Diagram tab and then select option SQL Query, which will bring a pop-up window, enter details as follows and then click on OK button
- Name : Give any name such as AVI_GET_PLAN_BALANCE
- Data Source : ApplicationDB_HCM
- Type of SQL : Standard SQL
- SQL Query : Paste the SQL query you got from this post or SQL query of your own.
Click on the Data tab, click on the Rows field and select value 200. (This is the problem we discussed initially that using the data model you cannot extract more than 200 rows. In the next step we will create a report based on this data model that will help us to get data beyond 200 rows. ) Click on the Views button and then click on the link Table View.
Now click on the button “Save As Sample Data“, which will bring a confirmation dialogue box, click on OK button to dismiss the dialogue box.
To save this Data Model click on Save button (floppy disk icon), which will bring a pop-up window. Search for the folder where you want to save the data model and then give it a name (AVI_GET_ABSENCE_PLAN_BALANCE)
Do not close window, your next activity will start from this page only.
Create report based on the data model
So far we have created a data model, which is incapable of retrieving more than 200 rows, now to overcome this problem we will create a report, which will be based on this data model. To create a report click on the button Create Report, which will open a pop-up window.
In the first step we barely need to change any thing. Just ensure that Use Data Model box is selected and in the Data Model filed our data model (AVI_GET_ABSENCE_PLAN_BALANCE) which we created in previous step is selected. Once you very all these details click on the Next button.
In the Select Layout stage again we dont really need to change anything. Default setup would be sufficient for us. Click on the Next button to proceeds to next stage.
We have now reached the Create Table stage. Here, you will see a list of all columns from our data model on the left-hand side of your screen. Simply drag and drop the desired columns into the empty area on the right-hand side to add them to your table. You may also wish to uncheck box “Show Grand Totals Row”.
Click on the Next button and then on the next stage click on the Finish button. Save your report where you saved your data model earlier and give it a name. Application will run the report immediately and fetch all records, without any limitation of 200 rows.
Usually the default output format of the report is PDF file, if you wish, you can also extract the data in excel, for the same click on the down arrow button near pdf file icon and select value Excel.
If you ever want to run this report again later, click on the Catalog button and then navigate to the folder you have saved your report, click on the Open link
Conclusion
In this article, we’ve shown you how to create a report that can help you extract more than 200 rows of data, even if your data model has a limitation on the number of rows it can extract. The steps we’ve outlined here can be applied to all scenarios, regardless of whether you need to extract more or less than 200 rows.
We hope you’ve found this information useful and that you’ve learned something new today. If you have any questions, please don’t hesitate to post them in our forum. We’re always here to help.
