In Oracle Fusion Cloud, BI Publisher reports are widely used for generating formatted outputs and documents. One of the unique business requirement is to invoke a web service from a BI Publisher report, say to fetch real-time data from a particular web service provider. So how do we achieve this ?
Let's see the step by step process to achieve this.
- For a sample purpose, I'm going to use a Public Web Service that provides details about various Countries. Since this is a public web service, it doesn't need any authentication.
http://webservices.oorsprong.org/websamples.countryinfo/CountryInfoService.wso
For example, this web service provides an operation named ListOfCountryNamesGroupedByContinent which is parameter-less and provides full list of Countries along with the Continents they belong to.
Output:
Another operation is FullCountryInfo which accepts a Parameter for Country Code and provides the full country information in return.
Output:
Now, let's see the steps for configuring and using this web service in our BI Publisher reports.
- Login to Oracle Fusion environment
- Add /xmlpserver at the end of URL and enter
- This will get us to the Analytics area
- Click on My Profile and navigate to Administration
- Navigate to Data Sources -> Web Service Connection
- Here, we are going to create a new Web Service Data Source
For this, we are going to refer to this WSDL provided by the web service.
http://webservices.oorsprong.org/websamples.countryinfo/CountryInfoService.wso?WSDL
WSDL typically provides details about all the operations offered by the given web service -
- Let's create a new Data Source as shown below:
Protocol: http in our case
Server: Use the base URL of the web service
URL Suffix: the remaining part of the WSDL URL
Set WS-Security to 2002
Authentication Type: Set to HTTP
I'm leaving Username and Password empty since the web service I'm using is a Public service and doesn't need any authentication. If you are using any web-service that needs authentication, then the credentials can be entered here.
- Click Apply to save the Data Source
Let's create two Reports (and hence two Data Models) showcasing various options.
List of Countries by Continent
- Let's create a new Data Model of type Web Service
- I'm going to name it CountryList_DataModel
- Here, I'm going to select Data Source as the Web Service Data Source (CountryWebService) we created in previous steps.
- It will automatically populate WSDL URL and Web Service name (CountryInfoService) from our data source.
- Under Method option, we need to choose which Operation we want to use. I'm going to select ListOfCountryNamesGroupedByContinent operation from the drop-down.
- Since this operation doesn't have any parameters, the Parameter section will show no options for configuration
- Click View to see the sample output data
- We can see the data provided by web service appears here
- Let's click Save As Sample Data and Create Report using this Data Model
- In the report layout builder, we will be able to see all the columns offered by our Web Service Data Model in left pane.
- Drag and arrange the desired columns in the layout area.
- Click Done and Save the Report
- Run the Report and we will be able to see the output based on the data provided by web service:
Report 2: Parametric Report
Country Info Report
- Let's create a new Data Model
- We are going to use FullCountryInfo operation from our Country Web Service.
Since this operation needs a Parameter for Country Code, let's first create a Parameter P_COUNTRY_CODE in our Data Model
- Now, let's create a Data Set of type Web Service
- I'm going to name it CountryInfo_DM
- Here, I'm going to select Data Source as the same Web Service Data Source (CountryWebService) we used in previous report.
- It will automatically populate WSDL URL and Web Service name (CountryInfoService) from our data source.
- This time, under Method option, I'm going to select FullCountryInfo operation from the drop-down.
- Since this operation needs a parameter, it will automatically show us the Name of the parameter and we can map it to our Data Model parameter P_COUNTRY_CODE as shown below:
- Let's Save it and navigate to View Data
- Here, we can enter a sample country code (e.g. US) in the parameter and click View
- We'll be able to see that the web service has returned the full country details for the given Country Code
- Let's click Save As Sample Data and Create Report using this Data Model
- In the report layout builder, we will be able to see all the columns offered by our Web Service Data Model in left pane.
- Drag and arrange the desired columns in the layout area.
- Click Done and Save the Report
- Run the Report and we will be able to see the web service output based on the Parameter we entered in report parameter:
Conclusion:
Oracle Fusion Cloud offers a robust and secure methods to invoke web services from within BI Publisher reports and consume the data for reporting purposes. This supports reporting based on a parameter-less or parameter-based web service calls which gives us a liberty of making a variety of HTTP or HTTPS web service calls to fetch real-time data for our reporting needs.
0 comments:
Post a Comment