To begin with, one will need to have either BI Developer or BI Administrator Role ( BI Administrator inherits BI developer) for BI Report development. Once you have the access, you are all set to develop your BI report.
Navigation:
First step: From home page, click on 3 horizontal parallel lines on the left and click on Report and Analytics. Click on “Browse Catalog”. You will land up at BI Page. On left hand side you will see a folder structure in which you will find a folder named “Custom”. This is the folder where all your custom reports have to reside. Everywhere else is Oracle’s field and your code will be removed with every refresh.
Once you have chosen the folder, click on the new icon and choose data model. A page will open like this:
Click on the “+” and choose SQL Query ( there are other options as well but this is the one you will use the most. we will talk about the others later).
Query
It will ask you to create a dataset, give it a meaningful name. in Data source field, choose the product you are implementing ( like ApplicationDB_FSCM in case of fusion finance). Enter your query. say
select hp.party_name supplier, po.segment1 PO_NUM
from po_headers_all po,
poz_suppliers supp,
hz_parties hp
where po.vendor_id = supp.vendor_id
and hp.party_id = supp.party_id
and hp.party_name = NVL(:supplier_name, hp.party_name)
** Don’t put the semi colon after the query.
Once you click ok, it will prompt you to create a bind variable. select the check box and click ok. Now this bind variable works as the input to the report. In next screen you can provide the default value of this parameter if required and alter the type of the parameter. Give it a name in “Display Label” field and click on the “View Data Button”. if needed, provide the value for the parameter or else just hit view. Query will return you some data if everything is fine. Click on “save as sample data” and then click on “save as”. A Dialog box will open to save the data model. save it in the folder of your liking but it has to be within the Custom Folder hierarchy or in “My Folder”.
BI Report Template
Now you can click on “Create Report”. You will get 2 options in “How do you want to create your report”. “Guide Me” option takes you step by step to create the template or you can choose “Use Report Editor” and click on finish. It will ask you to save your report. Again the same rule, save it within Custom folder or “My Folder”. Once report is saved it will ask you to either Create the template, or Upload a Template or Generate the template. Generate the template will work fine in this simple report. So we will use this option and click on it. Once done, it will ask you to name the template. Give it a name and click on Generate.
Now your BI Report is ready but need some fine tuning. Click on “View a list option” on right hand side and click on the drop down in “Output Formats”. All the options which you choose in this field, will be available in “Default Format”. Choose the default format from the drop down. This format will be used by the report to produce the output in unless chosen differently at run time.
Now click on the properties and deselect the Auto Run option. ( if this step is not done, your report will trigger as soon as it is opened without giving you a chance to enter the parameters. But if your report has no parameter, you can let this option remain selected.
Your very basic report is ready. Just click on “View Report” and see for yourself.
Related posts:
RTF- Looping Finite Number Of Times
Fusion Report Job Parameters
Report Security Query – Data Access On Specific Entities
How To Develop an RTF Template
Report Security
Report Scheduling – For Specific timings over the days
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com