8 Using BI Publisher for Custom Reports
The ability to utilize Oracle Business Intelligence Publisher (BI Publisher) for custom reports is available as part of your Merchandising suite service subscription and is the only option available for creating custom reports against the live production database. Other reporting tools can be used, but must be based on other data sources, such as the replicated data in the Retail Data Store (RDS) or the Data Access Schema (DAS).
Accessing BI Publisher
In a SaaS implementation, you will access BI Publisher using a URL like this, where the hostname is replaced with that which is relevant for your implementation:
https://<hostname>/<tenantname>/xmlpserver
In order to create reports, you will need to ensure you have the below privileges assigned to your user ID through IDCS:
-
<tenantId>-BIConsumer
-
<tenantId>-BIContentAuthor
-
<tenantId>-DVConsumer
-
<tenantId>-DVContentAuthor
Creating a BI Publisher Report
BI Publisher supports creating a number of different types of reports, including reports with charts, table-based report, and so on. For details on how to create reports in BI Publisher, see the Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher, especially Chapter 2 on creating and editing reports.
As you build your reports, consider the input parameters that are available based on where the report will be displayed. Details on the available parameters for dashboards and contextual reports for each of the Merchandising solutions are available in Appendix A: Dashboard and Contextual Parameters.
Note:
Custom reports in a user's My Folder will not be backed up by Oracle
but could be manually backed up by the user. Otherwise, all custom reports should be saved in the Shared Folders/Custom
folder to ensure that they are included in the backup/restoration processes.
Displaying a BI Publisher Report
Once you have created your report you'll need to identify the URL for the report. The basic URL structure will be:
http://<hostname>/<tenantname>/xmlpserver/<ReportDirectory>/<ReportName>.xdo
-
hostname
andtenantname
- will be the hostname and tenant ID for your Merchandising BI Publisher implementation -
xmlpserver
- this is a static string -
ReportDirectory
- folder path to the report -
ReportName.xdo
- the filename you gave the report; if the name has spaces, then use a + between words
You may also specify some BI Publisher formatting parameters for your report. The full list can be found in chapter 2 (Creating and Editing Reports) of the Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher in the section titled Specifying Parameters in the URL. But, some key ones you may consider to give your reports a similar look and feel to the Merchandising reports are:
-
_xmode
- it's recommended that you set this to 4, which hides the BI Publisher specific banners and parameters – especially for contextual reports -
_xpt
- this should be set to 1 so that the report doesn't launch a new window
Here's an example of the basic URL with some parameters specified, including the order number parameter, which is an input for this report:
https://<hostname>/<tenantname>/xmlpserver/~demouser/Custom/ORDER_CFAS.xdo?_xmode=4&_xf=html&_xpt=1&OrderNo=<Order>
Configuration in Merchandising
Use this URL to configure the report into an existing dashboard or contextual pane as described in the "Dashboards and Reports" section.
BI Publisher Reports Delivery Through Object Storage
In the next generation Merchandising Suite of solutions, object storage replaces the SFTP delivery channel.
Delivering scheduled reports through Object Storage
For details on how to set up reports delivery through object storage, refer to Set Output Options in Oracle Cloud Visualizing Data and Building Reports in Oracle Analytics Cloud.
While adding the destination for the reports delivery as Object Storage, you will need the following set of inputs that are required to push the file to object storage.
-
Server – The server is preconfigured as
OS
for any tenant.OS
must always be selected. -
Prefix – The prefix under the object storage bucket where the file will be uploaded
-
File Name – The file name with which the scheduled report output will be delivered to the object storage.
For example:
![Object Storage Configuration Example Object Storage Configuration Example](img/obj-storage-cfg-example.png)
Delivering Burst Reports through Object Storage
For details on how to create a burst query refer to Add Bursting Definitions in the Oracle Cloud Modeling Enterprise Data in Oracle Analytics Cloud.
After configuring the data model as you build your burst query by navigating to the bursting section you need to be aware of some of the key parameters to be supplied to the burst query
Sample Bursting Query for object storage as the delivery channel:
select i.DEPT KEY,
'<template_name>' TEMPLATE,
'RTF' TEMPLATE_FORMAT,
'en-US' LOCALE,
'PDF' OUTPUT_FORMAT,
'OBJECTSTORAGE' DEL_CHANNEL,
'<output_name>' OUTPUT_NAME,
'OS' PARAMETER1,
'<prefix>'PARAMETER2,
'<file_name>' PARAMETER3
FROM
ITEM_MASTER I
Key parameters to be noted here for object storage mechanism are:
-
DEL_CHANNEL
— This needs to be keyed in asOBJECTSTORAGE
. -
PARAMETER1
— UseOS
as the parameter value for this, because this is the preconfigured server name. The value for this should be kept asOS
and should not be changed. -
PARAMETER2
— Prefix under object storage bucket where the file will be uploaded. -
PARAMETER3
— File name.
Downloading the BI Publisher reports from Object Storage
Once the reports are sent to object storage,
use the createPar
service to download the files. This service is available in Retail Home; it generates a
PAR (Pre-authenticated Request) to download the file.
For more details on this, refer to the Retail Home documentation.