E-BIP/XMLP: Running BI Publisher Reports via the REST webservice BIP_RUNREPORT_REST_GET Fail with the Error "Excel cannot open the file 'Filename.xlsx' because the file format or extension is not valid." (Doc ID 2850146.1)

Last updated on JULY 23, 2023

Applies to:

Symptoms

When attempting to test the ability to run BI Publisher Reports via the REST webservice BIP_RUNREPORT_REST_GET - primarily as a REST Drilling URL in PS Query, the following error occurs on PIA page after clicking 'Preview' button:

The REST service should exist before attempting to build the PS Query. Starting with PeopleTools 8.58+, it is delivered out-of-the-box the REST service for running BI Publisher reports – BIP_RUNREPORT_REST_GET, this service will be used in the steps below:

1. Navigate to People Tools > Integration Broker > Integration Setup > Service Operations
2. Open the Service Operations you plan on using:
3. Ensure the ‘REST base URL’ is correct (hostname, port, default node), the Default Service Operation is ‘Active’ and that the ‘*Req verification’ is set correctly (in this example, for simplicity’s sake, using no verification):
4. Define the Query definition:
  4.1. Navigate to Reporting Tools > Query > Query Manager > Create New Query
  4.2. Add a record and fields:
E.g.
SELECT A.REPORT_DEFN_ID, A.DESCR
  FROM PSXPRPTDEFN A
  4.3. Define a REST Drilling URL expression:
 Expressions > Add expression > Drilling URL > REST URL
Select the REST service you want to use (BIP Run in this case): BIP_RUNREPORT_REST_GET
 
And fill in the the drilling URL details:
- The URI Template is based on the REST Service you are using. The one presented here contains multiple URI Templates used to run BI Publisher reports. The simplest one only needs the BIP report name: %A.REPORT_DEFN_ID%
- The URI primitives are the values which will be substituted in the URI Template string at runtime. These can be constants (you can enter a static report name, for example) or based on a query field (current example).
- You can Map the URL to an existing query field: A.DESCR. This is the ‘hyperlink’ field that the users will click to drill into the BI report
E.g.
'/rest_s/BIP_RUNREPORT_REST_GET/[%A.REPORT_DEFN_ID%]:A.DESCR'

  4.4. Click Ok > Ok
  4.5.Navigate to the Run tab and test the drilling URLs:
 
In this example, clicking the report name will actually run that specific BI report and open the results in a new browser window:
https://:/PSIGW/RESTListeningConnector/E920SAT2/BIPRunReport.v1/%A.REPORT_DEFN_ID%
5. Create the BI Definition
  5.1. Navigate to Reporting Tools > BI Publisher > Register BIP Data Sources
  5.2. Create a new BI Publisher data source based on the new query created
  5.3. Navigate to Reporting Tools > BI Publisher Create BIP Report Definitions
  5.4. Create a new BIP Report Definition
  5.5. Go to Properties tab and set the property 'psxp_excel_outputformat' either to xls-html or xls-mhtml
  5.6. Save after uploading the RTF template
6. Run the BI Publisher report using the navigation Reporting Tools > BI Publisher Create BIP Report Definitions, then go to Template tab
7. Select 'Preview' button
8. The error occurs.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.