Defining REST Drilling URLs
Report developers may use a REST (REpresentational State Transfer) drilling URL to allow the user to drill down from the query result set, or a report based on the query, into a PeopleSoft defined Rest Web Service URL. This feature and the newly introduced BI Publisher REST Web Service, would allow the user to drill down from a query, or even a formatted query-based report, directly into another formatted (BIP) report.
In general, you are able to drill down from the query results or a formatted query-based report into another query using the Query Drilling URL feature. However, if the parent query containing the drilling URL is used as a data source for a formatted report (such as an nVision report, BI Publisher report, or Pivot Grid chart), Oracle PeopleSoft recommends that you use the REST Drilling URL feature and the BIP Web Service so that the drilling URLs can drill into a formatted report. When defining a drilling URL to the BIP Rest Service, all supported report output parameters, such as the report definition ID, output format, template ID, and language code, can be defined as a part of the drilling URL. In addition, the required query prompt values for the drilling report can be mapped to field values in the parent query.
You can define REST drilling URLs to drill down into the BIP REST Service or any PeopleSoft REST Service that has a defined HTTP Get method. REST drilling URLs behave similarly to other types of drilling URLs that are available in Query Manager; in that they can be defined as separate fields in the query results or mapped to the output fields in the parent query to be used as links for the output fields.
Note that:
REST Web Service drilldown is available only for Service Operations using the HTTP GET method.
Only REST services with defined HTTP Get methods are available for users to select when they define REST drilling URLs because query drilling URLs are based on a simple HTTP Get Request URL.
The BIP REST Service is available only for non-bursted reports.
Only a non-bursted report is supported because a BIP REST service synchronously returns report output to the calling application or browser.
BIP REST Services are available only for query-based reports.
Reports based on an XML file-based data source (including the deprecated Rowset and XMLDoc data source types) require an XML file to be created and provided at runtime; therefore, reports based on XML file-based data sources are not supported using a simple HTTP Get Request URL.
Connected Query based reports cannot be used with the REST drilling URL feature.
Potentially, these reports can generate an extremely large result that may introduce performance issues if run synchronously with the intention of viewing the report output immediately.
Terminology
|
Field or Control |
Definition |
|---|---|
| REST Web Services |
PeopleSoft Defined Consumer or Provider REST (Representational State Transfer) Web Services |
Note: All references to the term REST Services in this documentation indicate a REST Get Service Operation specifically because the REST Drilling URL feature only supports REST Web Services that have a defined HTTP Get Method.
BI Publisher Report Web Service
The BI Publisher Report Web Service is a REST Web Service used to run a BI Publisher report using an HTTP Get Method (HTTP URL). You can use the REST Service to synchronously run a BI Publisher report and immediately view the output in the requested format. The combination of required and optional URL components provide the flexibility of running a BI Publisher report using either the report default settings or using specified overrides (for Template ID, Output Format, and Language Code). In addition, prompt values must also be specified if required by the underlying query used by the BI Publisher report.
Image: General page
This example illustrates the General page (PeopleTools, Integration Broker, Integration Setup, Service Operations) displaying the defined Get Method service operation that will service the REST URL requests. The URI section displays the available URI templates.
The simplest URI template
is: http://<server>
/PSIGW/RESTListeningConnector/QE_LOCAL/BIPRunReport.v1/{ReportID}.
The most complex URI
template is: http://<server>/PSIGW/RESTListeningConnector/QE_LOCAL/BIPRunReport.v1/{ReportID}/{TemplateID}?format={OutputFormat}&langcd={LangCode}&prompts={Query_PromptColl*}.
Note that:
The Report ID element is always required and denotes the Report Definition ID of the report to be run.
All other elements in the URL are optional. If any element other than Report ID is omitted, then the default value for that parameter as defined in the Report Definition is used when the system runs the report.
The Query_PromptColl* element is a collection element and represents query prompt values for the underlying query that the report definition is based on.
The query prompt values need to be specified as a comma-separated sequence of name-value pairs. For eg., “…&prompts=<prompt1>,<value1>,<prompt2>,<value2>,…”, where promptn is the Prompt Field Name as defined in the Query definition. In case there are any duplicate Field Names in the list of prompts, the Unique Prompt Name should instead be substituted for the Field Name for these specific prompt fields only.
These are two examples of the fully qualified URLs for the simplest and most complex URI templates mentioned previously:
This is the fully qualified URL when the simplest URI template is used:
http://<server>:80/PSIGW/RESTListeningConnector/QE_LOCAL/BIPRunReport.v1/XRFWIN.This is the fully qualified URL when the most complex URI template is used:
http://<server>:80/PSIGW/RESTListeningConnector/QE_LOCAL/BIPRunReport.v1/QE_FRA_TRANS/QE_FRA_TRANS_1?format=PDF&langcd=ENG&prompts=MESSAGE_SET_NBR,28.
The web service response content is the report output file in one of the four output formats (as per users request or the default).
Image: Report output file
This example illustrates the report output file returned in browser after executing the complex URL request from the previous example.
Security When Using BIP REST Drilling URLs
The BIP REST Web Service is associated with the PTPT1000 – PeopleSoft User permission list and can be accessed by any PeopleSoft user to run a BI Publisher report. However, the returned results depend on the Query Access List permissions and row-level security that are specific to the user making the request. That is, PeopleSoft Query security is still enforced for query-based BI Publisher reports run using the BIP REST Web Service.
In addition, BI Publisher REST URLs require PeopleSoft User Authentication if a request is being made outside an existing PIA session; it requires basic authentication with SSL.
Report developers use the Define a REST Service Drilling URL page in Query Manager to define the REST drilling URLs. They can locate and select any PeopleSoft REST Service Operation that is defined using the HTTP Get method and enter parameters for the REST drilling URL using either static text or by mapping to query field values. They can also map the REST drilling URLs to existing query columns or use them as standalone fields in the results, which is similar to other drilling URL types.
Navigation
Select
Open an existing query or create a new one.
Select the Expressions tab and click the Add Expression button.
Select the Drilling URL option from the Expression Type list and click the REST URL link.
Image: Define a REST Service Drilling URL page
This example illustrates the fields and controls on the Define a REST Service Drilling URL page. Definitions for the fields and controls appear following the example.
|
Field or Control |
Definition |
|---|---|
| Service Operation |
Click the search icon to search and select a PeopleSoft-defined REST service operation. Note: Only REST service operations using a HTTP Get method are available for selection. Other sections and fields in this page appear after you select an existing service operation in the Service Operation field using the search icon or after you enter a valid service operation and tab out of the Service Operation field. |
| Use Secure REST Target |
Select this check box to use the secure REST Target location (HTTPS) in the drilling URL. Note: Your selection of this option reflects in the REST URL field, which appears at the bottom of the page. If the selected service operation requires SSL as part of its Required Verification type, then this check box is selected by default and unavailable for modification. |
| REST URI Templates |
Use this section to select a URI template from the list of available templates. |
| URI Primitives and Collections |
Use these sections to qualify the selected template with either static text values or mapped query field values, which will be used to qualify the REST URLs at query runtime. |
| Map URL to Query Columns |
Use this section to map the REST URLs to existing columns and create drilldown links for those column values in the query results. |
| REST URL |
This field displays a preview of the REST URL qualified with values you enter into the URI Primitives section or the URI Collections section.
|
Note: This section discusses the steps used to define a REST Drilling URL to a PeopleSoft-defined REST Web Service Operation. However, when defining a drilling URL specifically with the BIP Web Service (BIP_RUNREPORT_REST_GET), certain optimizations are applied to the page in this scenario, and these optimizations are discussed in the Defining a REST Drilling URLs Using the BIP REST Service section.
To define a REST drilling URL to a PeopleSoft-defined REST Web Service Operation:
Select
Open an existing query or create a new one.
Select the Expressions tab and click the Add Expression button.
Select the Drilling URL option from the Expression Type list and click the REST URL link.
The Define a REST Service Drilling URL page appears.
In the Service Operation field, select a REST service operation; for example, select QAS_LISTQUERYFIELDS_REST_GET.
Image: Define a REST Service Drilling URL page
This example illustrates the Define a REST Service Drilling URL page displaying the QAS_LISTQUERYFIELDS_REST_GET value in the Service Operation field.
Select a URI template in the REST URI Templates section.
The URI Primitives section appears.
Use the URI Primitives section to qualify elements in the selected template by either entering static values or mapping to runtime query field values.
Image: Define a REST Service Drilling URL page
This example illustrates the Define a REST Service Drilling URL page displaying the QAS_LISTQUERYFIELDS_REST_GET value in the Service Operation field. The URI Primitives section includes the static values. The REST URL field displays the qualified REST URL using the values specified in the URI Primitives section.
In the URI Primitives section, click the Lookup icon in the Query Field column for element QueryName to map to a query field value.
The Select Mapping Query Fields page appears.
Image: Select Mapping Query Fields page
This example illustrates the Select Mapping Query Fields page.
Select a query field from the Record.FieldName list.
The Define a REST Service Drilling URL page reappears displaying the mapped query field name in the Value column for the QueryName element.
Image: Define a REST Service Drilling URL page
This example illustrates the Define a REST Service Drilling URL page. In the URI Primitives section, the Value for the element QueryName displays %A.EMPLID% after you select the A.EMPLID field from the Select Mapping Query Fields page.
Click the OK button.
The Edit Expression Properties page reappears with the REST drilling URL expression in the Expression Text field.
Image: Edit Expression Properties page
This example illustrates the Edit Expression Properties page displaying the REST drilling URL expression in the Expression Text field.
Click the OK button to return to the Expressions page.
Click the Use as Field link to use the REST drilling URL as a separate field in the query results.
Save the query and then select the Run tab.
Image: Run page
This example illustrates the Run page displaying the REST drilling URLs in the Drilling URL column.
Some URI templates may contain collection elements, which correspond with parameters on the URL that accept multiple comma-separated values. In these cases, you may add item values for the collection element using the Insert Item for Collection link at the top of the URI Collections section.
Image: Define a REST Service Drilling URL page
This is an example of using the URI Collections section to insert multiple values for a URI collections element while defining a REST drilling URL using the QAS_EXECUTEQRY_REST_GET service operation.
Image: Define a REST Service Drilling URL page - URL Collections
This example illustrates the Define a REST Service Drilling URL page - URL Collections section. Use the drop-down list next to the Insert Item for Collection link to select a collection element value to add to the URI collections. For example, you can select the FilterFields option from the drop-down list and click the Insert Item for Collection link twice to insert two rows under the FilterFields collection element in the URI Collections section, and then you use the inserted rows to define either static values or map to query fields.
When you define a REST Drilling URL using the BIP Rest Service Operation BIP_RUNREPORT_REST_GET, certain optimizations are applied to the page in order to better facilitate the process. Specifically, the following features are available:
Lookup functionality for URI template elements.
In the URI Primitives section, a Lookup icon is available for each element in the selected URI template. You can use the lookup icons to search and select the applicable values for these elements (such as the ReportID, TemplateID, OutputFormat, and LangCode) rather than having to enter the values manually.
Similarly, the Lookup icons are also available in the URI Collections section. If the selected report definition is based on a query containing prompts, these prompt name and value pairs are specified using the Query_PromptColl Collection element. The Lookup icons can be used to select from available values for the prompt, provided a prompt table has been defined for the prompt in the query definition.
Default values.
After selecting a value for the ReportID (BIP Report Definition ID), all other optional element values (such as the TemplateID, etc.) are automatically pre-populated according to the default values defined at the report definition level.
The following steps are the sample process of how to create a REST Drilling URL using the BIP REST Service:
Select
Open an existing query or create a new one.
Select the Expressions tab and click the Add Expression button.
Select the Drilling URL option from the Expression Type list and click the REST URL link.
The Define a REST Service Drilling URL page appears.
In the Service Operation field, select service operation BIP_RUNREPORT_REST_GET (BIP Rest Web Service).
Select a URI template in the REST URI Templates section.
Image: Define a REST Service Drilling URL page
This example illustrates the Define a REST Service Drilling URL page. The Service Operation field display the service operation BIP_RUNREPORT_REST_GET and a URI template is selected in the REST URI Templates section.
In the URI Primitives section, click the Lookup icon on the ReportID row.
The Select Value for ReportID page appears.
Image: Select Value for ReportID page
This example illustrates the Select Value for ReportID page. All report definitions that you have permissions to access appear in the list for your selection.
Select a report ID from the list of available report IDs and then the Define a REST Service Drilling URL page reappears.
In this example, select the QE_FRA_TRANS report.
Image: Define a REST Service Drilling URL page
This example illustrates the Define a REST Service Drilling URL page displaying the selected report ID in the ReportID row. In addition, other URI elements are populated with the default values for this report definition.
Note: If you manually enter a valid report definition ID into the Value field for ReportID (rather than using the Lookup icons) and then tab out of the field, the other URI elements are automatically populated with the default values as well.
Optionally, use the Lookup icons for the other URI elements to select any values other than the default values.
For instance, when you click the Lookup icon for OutputFormat, you are presented with all available output formats as defined in the report definition.
Image: Select Value for OutputFormat page
This example illustrates the Select Value for OutputFormat page when you click the Lookup icon for OutputFormat.
Use the Define a REST Service Drilling URL page - URI Collections section to populate the values for the Query_PromptColl Collection element in the URI Template, which requires name-value pairs for all query prompts that are defined for the underlying query used by this report definition.
In this example, there is a single prompt MESSAGE_SET_NBR defined for the query. The prompt name MESSAGE_SET_NBR is displayed in a column preceding the Value column, which is used for specifying the value for this query prompt.
Click the Lookup icon on the prompt MESSAGE_SET_NBR to access the Select Value for MESSAGE_SET_NBR page where you can search and select a value for the prompt, provided a prompt table has in fact been defined for this prompt field in the query definition.
Image: Select Value for MESSAGE_SET_NBR page
This example illustrates the Select Value for MESSAGE_SET_NBR page.
Click the number link for any of the listed values to select that message set number.
The Define a REST Service Drilling URL page reappears.
Image: Define a REST Service Drilling URL page
This example illustrates the Define a REST Service Drilling URL page listing message set number 27.
Note: When you define a REST Drilling URL using the BIP Rest Service Operation, you cannot manually add values into a collection element. The URI Collections section in this case is used solely for the population of the Query Prompt values; therefore, the Insert Item for Collection link and the drop-down list are not available as can be seen when you define the drilling URL using other service operations.
Expand the Map URL to Query Columns section and then select the fields to map the REST URL to query columns.
Image: Define a REST Service Drilling URL page - Map URL to Query Columns section
This example illustrates the Map URL to Query Columns section. The REST drilling URL is mapped to the A.DEPTID column.
Click the OK button to return to the Expressions page.
Optionally, click the Use as Field link to use the REST drilling URL as a separate field in the query results.
Save the query and then access the Run page.
Image: Run page
This example illustrates the Run page displaying the REST drilling URLs. The Dept (DEPTID) column is mapped to the REST drilling URL and the Drilling URL column displays the REST drilling URLs separately.
Click a REST URL link to display the report output.
Image: Report output
This is an example report output that appears in the browser after you click the 10200 link in the previous example.
