5 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 5-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 5-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 5-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.