Managing Statistics Formulas

Administrator users can manage statistics formulas in the Statistics Formulas table in the Data Editor. This table corresponds to the PRO_VIEW_SYNTAX database table, which dictates the aggregation of data in records that are posted into the POS_STATISTICS_TAB database table

Statistics records contain summary data for a particular store on a particular day. The statistics data is calculated and summed through a stored procedure.

Statistics can be summed at several different levels depending on the key fields invoked. The four possible levels are:

§ By Register

§ By Cashier

§ By Store (hierarchy, such as region, district, and so on)

§ By Date

The table layouts for the POS_STATISTICS_TAB can be found in the Oracle Retail XBRⁱ Cloud Services Core Field Mapping Guide.

To modify statistics records:

From the Tools menu, choose Data Editor.
Double click the hyperlink for Statistics Formulas, or click the Available Actions icon and choose Maintain.

Each available statistics metric is displayed in a table with these columns:

ACTIVE_FLAG - This is set to N by default for core statistics metrics. You should set this flag to Y for Core statistics metrics that you are using. If you want to make modifications, create a new metric based on the core metric and set this flag to Y for the new metric and N for the core metric.

CUSTOM_FLAG - This is set to 0 by default for core statistics metrics. You should keep this flag set to 0 for Core statistics metrics. If you want to make modifications, create a new metric based on the core metric and set this flag to 1.

SYSTEM - This field is populated with STAT for all Loss Prevention aggregations that are associated with the POS_STATISTICS_TAB table. For Sales and Productivity aggregations, the field is populated with an abbreviation indicating the table with which the aggregations are associated. For example, SPOSLSPSN corresponds to the SPO_STATS_SLSPSN_TAB table. The POS_STATISTICS_TAB table is also used to formulate the aggregation of data for the Sales and Productivity tables.

SOURCE_FIELD - This field contains the formula for the source field, which will populate the TARGET_FIELD in the PRO_VIEW_SYNTAX table.

VIEW_NAME - The name of the field in the view that is used for generating the calculated value that is populated in the PRO_VIEW_SYNTAX table.

SYNTAX_ID - The system-generated identity column. This field is not editable.

POSTING_SOURCE - This is set to ST by default and is not editable.

TARGET_FIELD - The target field in the PRO_VIEW_SYNTAX table.

END_OF_DAY_FLAG - Indicates statistics that are only calculated and generated during end-of-day processing. This is set to N by default.

In the Actions column of the statistics formula you want to modify, click the Available Actions icon and choose Edit

This displays the Statistics Formula - Edit Record page, with the following fields:

SYSTEM - See definition above. This field is required.

VIEW_NAME - This field is required.

TARGET_FIELD - The target field in the PRO_VIEW_SYNTAX table.

SOURCE_FIELD - This field contains the formula for the source field, which will populate the TARGET_FIELD in the PRO_VIEW_SYNTAX table.

FROM_CLS - The From clause, if used.

WHERE_ CLS - The Where clause, if used.

GROUPBY_CLS - The Group By clause, if needed for the calculation of the statistics.

ORDERBY_CLS - The Order By clause, if needed for the calculation of the statistics.
POSTING_SOURCE - This field is set to ST by default and is not editable..

CUSTOM_FLAG - his is set to 0 by default for core statistics metrics. You should keep this flag set to 0 for Core statistics metrics. If you want to make modifications, create a new metric based on the core metric and set this flag to 1.

END_OF_DAY_FLAG - Indicates statistics that are only calculated and generated during end-of-day processing. This is set to N by default.

COMMENTS - Enter any additional comments about the statistics metric.

Enter the required changes, and click Save to save the changes.

The LAST_UPDATE_DATE and LAST_UPDATE_ID are updated in the General Tab.

_____________________________