In this blog, we'll take a look at the typical steps to be followed to create a custom BI Publisher report in Oracle ERP Cloud.
- Navigate to List of Values section and click + to create a new LOV
- Let's create a LOV for Book Type using below query
select distinct book_type_code from fa_distribution_history order by 1
- Similarly, let's create one more LOV for Asset Type using below query
select distinct asset_type from fa_additions_vl order by 1
- Now, let's assign the LOVs to our parameters
- Navigate to parameter p_book_type, select Parameter Type as Menu and assign the Book_Type_LOV under List of Values section
- Similarly, navigate to parameter p_asset_type, select Parameter Type as Menu and assign the Asset_Type_LOV under List of Values section
- Let's navigate to View Data section
Conclusion:
By following the above steps, we can create a data model and RTF template based BI Publisher Reports in Oracle ERP Cloud. To enhance the functionality of the reports further, more features such as Bursting, ESS Jobs, report schedules can be implemented as well.
Part 1: Create a Data Model
- Login to Oracle ERP Cloud
- Navigate to Reports and Analytics under Tools section.
- Click on Browse Catalog to open BI Publisher.
- Alternatively, you can directly navigate to BI Catalog by appending /analytics to your ERP Cloud base URL as shown below -
- Click on Browse Catalog to open BI Publisher.
- Alternatively, you can directly navigate to BI Catalog by appending /analytics to your ERP Cloud base URL as shown below -
- Now, let's create a New Data Model
- Navigate to New -> Data Model.
- Click + and select Data Source as SQL Query.
- Let's define the SQL query to show Fixed Asset details in our case.
- Provide a relevant name to the query.
Select the correct data source: FSCM in our case
Type of SQL would be Standard SQL
- Here, in the where clause of the query, I'm going to mention two additional conditions using :p_book_type and :p_Asset_Type.
- Provide a relevant name to the query.
Select the correct data source: FSCM in our case
Type of SQL would be Standard SQL
- Here, in the where clause of the query, I'm going to mention two additional conditions using :p_book_type and :p_Asset_Type.
This step would automatically create these parameters for us which we will refine in later steps.
- Click OK
AND d.BOOK_TYPE_CODE = NVL (:p_book_type, d.BOOK_TYPE_CODE)
AND a.ASSET_TYPE=NVL(:p_Asset_Type,a.ASSET_TYPE)
- Click OK
- Click Data -> View to preview the output of the Data Model.
- We would see the default output like this.
- If desired, we can change the number of Rows from the drop down (Max 200) and select Table View instead of Tree View and click View again to see a tabular output of our query.
- We would see the default output like this.
- If desired, we can change the number of Rows from the drop down (Max 200) and select Table View instead of Tree View and click View again to see a tabular output of our query.
- Now let's configure the Parameters
- Let's first define the LOVs (List of Values) for our parameters.
- Navigate to List of Values section and click + to create a new LOV
- Let's create a LOV for Book Type using below query
select distinct book_type_code from fa_distribution_history order by 1
- Similarly, let's create one more LOV for Asset Type using below query
select distinct asset_type from fa_additions_vl order by 1
- Now, let's assign the LOVs to our parameters
- Navigate to parameter p_book_type, select Parameter Type as Menu and assign the Book_Type_LOV under List of Values section
- Similarly, navigate to parameter p_asset_type, select Parameter Type as Menu and assign the Asset_Type_LOV under List of Values section
- Let's navigate to View Data section
- Now we will be able to see the LOVs for both of the parameters
Book Type:
Asset Type:
- Once the desired values are selected, we can click on View to see the data filtered based on the parameter selection.
- Let's click on Save As Sample Data button to save this sample data. This basically saves the structure of the data model and sample data which comes handy in building the report.
Book Type:
Asset Type:
- Once the desired values are selected, we can click on View to see the data filtered based on the parameter selection.
- Let's click on Save As Sample Data button to save this sample data. This basically saves the structure of the data model and sample data which comes handy in building the report.
- At this stage, we have completed the build of our data model.
- Let's click Save and save the Data Model in the desired folder
Part 2: Create a BI Publisher Report
Navigate to BI Publisher Home
- Click -> New -> Report
- Click Cancel for the default pop-up
- Click on the magnifying class button next to Data Model
- Select the Data Model created in Part 1.
- Click -> New -> Report
- Click Cancel for the default pop-up
- Click on the magnifying class button next to Data Model
- Select the Data Model created in Part 1.
- Click Generate button to automatically generate the first draft of the RTF based report.
- Provide a name for the layout and click Generate
- Provide a name for the layout and click Generate
- At this stage, we should see the new report automatically created as shown below.
- Let's click on View a list option and set the Output format and Default format to Excel. There are a number of options that we can use here like PDF, CSV etc. but I'm using Excel for this use case.
- Let's click on View a list option and set the Output format and Default format to Excel. There are a number of options that we can use here like PDF, CSV etc. but I'm using Excel for this use case.
- Now, let's click Save to save the report with the relevant name in the desired folder.
- If we wish to further customize/modify the RTF template then we can click on Edit option under the template to download the RTF file.
- Once the RTF file is downloaded, we can open it in MS Word and modify it as needed.
- If we wish to further customize/modify the RTF template then we can click on Edit option under the template to download the RTF file.
- Once the RTF file is downloaded, we can open it in MS Word and modify it as needed.
- To upload the modified RTF template in the report, we first have to Delete the original one and upload the new one as a fresh template. There's no Replace option to overwrite the old template at the Report level.
- Finally, let's navigate back to the Catalog, locate our report and click Open to run it.
- We can select the Book Type and Asset Type parameter values and click Apply
- This should produce a Excel file which will contain the actual report data.
- Finally, let's navigate back to the Catalog, locate our report and click Open to run it.
- We can select the Book Type and Asset Type parameter values and click Apply
- This should produce a Excel file which will contain the actual report data.
Conclusion:
By following the above steps, we can create a data model and RTF template based BI Publisher Reports in Oracle ERP Cloud. To enhance the functionality of the reports further, more features such as Bursting, ESS Jobs, report schedules can be implemented as well.
