6 Oracle Application Express Integration
Retail Home is capable of accessing data through Oracle Application Express (APEX).
This integration works by creating a REST service in APEX that returns data to be rendered in a Retail Home report. A Retail Home administrator then must configure the report in Retail Home to pull its data from the APEX service. For more information on how to configure Dashboards within Retail Home, see Dashboard Configuration. For more information about how to create REST services through the APEX UI, please refer to the Oracle APEX documentation.
Only certain reports within Retail Home support APEX integration, and the APEX REST service must return data in the correct format expected by the report for the integration to work. This document discusses how to integrate APEX with Retail Home reports.
Two Metric Tile State Report
Retail Home provides a common two metric tile state report layout. It renders up to two metrics with metric names, metric values, metric labels, and optional indicators, such as arrows showing an increase or decrease in the metric value.
Service Query Structure
To integrate with this report, first create an APEX service. This service must be implemented as a SQL Query that returns 1 to 2 rows of data. A UNION can be used to return up to two rows.
The service must be configured to return the results as JSON, and the query must return the following columns:
Table 6-1 Columns Returned by Query
Column Name | Required | Description |
---|---|---|
NAME |
Yes |
The name of the metric value. This is displayed above the metric value. |
VALUE |
Yes |
The metric value itself. |
VALUE_FORMAT |
No |
An optional metric value formatting code. See the table of valid format values. If no VALUE_FORMAT is returned, Retail Home will not format the metric value. |
VALUE_LABEL |
No |
An optional label that further describes the metric value (e.g. a currency code). This label is displayed below the metric value. |
INDICATOR |
No |
An optional indicator that is rendered before the metric value. See the table of valid indicator values. |
The following table lists the supported format values:
Table 6-2 Format Values
Format Value | Meaning | Example |
---|---|---|
PC |
Percent |
Formats 0.045 as 4.5% |
S |
Short Number |
Formats 9950000 as 9.95M |
N |
Number |
Formats 1234567.89 as 1,234,567.89 |
The following table lists the supported indicator values:
Table 6-3 Indicator Values
Indicator Value | Meaning |
---|---|
up |
An upward pointing arrow |
down |
A downward pointing arrow |
normal |
A normal severity indicator. |
info |
An info severity indicator |
important |
An important severity indicator |
critical |
A critical severity indicator |
Service Query Examples
The following examples show different valid queries that can be used to return data that is in a compatible format with the Retail Home Two Metric tile state report. The examples select constant values from dual to demonstrate the concept; an actual service implementation's query would select real values from a table.
The following example query shows all the possible columns being returned:
select 'Metric 1' NAME, 1234567890 VALUE, 'S' VALUE_FORMAT, 'USD' VALUE_LABEL, 'critical' INDICATOR from dual
The following example query shows only the required columns being returned:
select 'Metric 1' NAME, 1234567890 VALUE from dual
The following example query shows two rows being returned using a union:
select 'Metric 1' NAME, 1234567890 VALUE, 'S' VALUE_FORMAT, 'USD' VALUE_LABEL, 'critical' INDICATOR from dual union select 'Order Count' NAME, 8 VALUE, NULL VALUE_FORMAT, NULL VALUE_LABEL, NULL INDICATOR from dual
Retail Home Tile State Report Configuration
This tile state report can be configured on the Tile Configuration train stop of the Dashboard Configuration screen.
First, create a new tile state:
-
Check the Displayed checkbox.
-
Select Two Metric Summary in the Tile State Report field.
-
Select an Expanded Report if an expanded report should be accessible through this tile state.
-
Click OK.
Then, add a data source for the tile state that will connect to your APEX service:
-
In the Data For field, select Tile State.
-
Check the Active checkbox.
-
In the Name field, select RetailHomeReportDataService.
-
In the Type field, select REST.
-
In Source, enter the complete URL to your APEX service.
-
Click OK.
Supporting Retail Home Filters
Retail Home users can filter the data displayed in their dashboard by Merchandise Hierarchy, if this feature was enabled in the Retail Home installation.
Retail Home passes these optional filter values as query parameters to the REST Service endpoint that is configured in APEX.
In the SQL query of an APEX REST Service endpoint, reference these filter values using following variables:
-
:departmentId
-
:classId
-
:subclassId
The following query example shows how different rows can be returned depending on what departmentId
value
is passed in the REST service query parameter:
select 'Metric 1' NAME, 1234567890 VALUE, 'S' VALUE_FORMAT, 'USD' VALUE_LABEL, 'critical' INDICATOR from dual union select 'Order Count' NAME, 8 VALUE, NULL VALUE_FORMAT, NULL VALUE_LABEL, NULL INDICATOR from dual where :departmentId IS NULL union select 'Order Count' NAME, 10 VALUE, NULL VALUE_FORMAT, NULL VALUE_LABEL, NULL INDICATOR from dual where :departmentId = 1000
This idea can be extended to test for different values and to incorporate the classId and subclassId values as well.