2 Define Totals

A total is a summation or count of two or more entities. For example, a total can be a summation of the quantity of items sold in the store on a particular store day.

You can use the Totals Definition Maintenance module of Sales Audit to define the totals, these totals can either be imported as transactional data from the POS/OMS system into Sales Audit through a RTLOG file or calculated by Sales Audit.

Source data can include transaction data and external data, such as money order totals or lottery ticket sales. You can choose the tables and columns for the total, build query statements to yield the desired information, and associate these totals with categories, such as stores or POS values.

Create Totals

Totals are a tool in Sales Audit that allows you to define saved procedures that create sums or counts of data within a Store Day. These totals can then be used during further processing for various purposes. Examples include totals for different types of tenders for export to the general ledger, counts of transactions that meet specific criteria to be used in audit rules, or for performing store balancing over/short analysis within Sales Audit.

A total in Sales Audit can be calculated from raw transaction data, or a total that comes from the POS/OMS through the RTLog. Totals can be based on raw transaction data, or on existing totals.

The Total Maintenance wizard allows you to create and update a total definition. It involves a series of steps where various details such as, total type, total entity, data source, roll up level, total usage can be specified. Each window in the wizard has a text box to the right that displays help text for each field. You should understand the following concepts before you create or update a total:

  • Revision Number

    Each total definition is assigned a revision number because updates to the total definitions are saved as new revisions and the existing definition record is not updated. This allows you to maintain a full audit trail and ensures that if an export is restarted after the total definition has changed, the system still has the total definition of the original export.

  • Status

    Each total definition is assigned a status. Based on the status of the total, the values it produces are stored in respective tables. For example, if a total definition is in Worksheet status, the values it calculates are written to the specific tables. This allows you to test total definitions to ensure the definition meets the functional need before introducing the total into the main test or production system.

Define Basic Information for Totals

Figure 2-1 Total Window


The Overview window allows you to enter basic information for a total. You can enter the following information on the Overview window:

Field Description
Total A unique ID and a description for the total. It is recommended that you add a description that is relevant to the objective you are trying to achieve with this total.
Description A description for the total, any alpha numeric value is acceptable in this field.
Start Date and End Date The period for which the total is active.
Update Date/Time The system time when the total was last updated. This field is system generated and you cannot edit it.
Total Category A grouping mechanism that helps to organize totals for display on the Over/Short and Miscellaneous Total windows. It does not influence the total in any way. It only helps to organize the total values for display. It determines the screens on which the total values are displayed. For over/short totals, you must specify an Over/Short Group and an Over/Short Operator.
Over/Short Group Determines if the total values are ’Accounted For' (actual in hand money) or ’Accountable For' (based on transaction data, money the store should have).
Over/Short Operator Used to determine whether the values produced by the total are added or subtracted from either the ’Accounted For' or ’Accountable For' groups.
Raw Data or Existing Total A raw data total is defined using transaction data in the database. A combined total is defined by combining existing totals. While creating a raw data total, the total definition involves building an SQL statement.

Define Attributes

Figure 2-2 Attributes Window


The Attributes window allows you to identify from where can the values for this total come from. The following are the options on the Attributes window:

Field Description
Corresponds to a value from the RTLOG Determines whether the total is uploaded from the POS/OMS through an RTLOG file.
System calculated value Determines whether the total is calculated by the system. For system calculated totals, you must either build a SQL statement or define a total through total maintenance wizard.
Count or sum Determines whether the total values represent a number of occurrences (count) or an amount resulting from them (sum).
Store or system balancing level Determines whether one value for the total is produced per store, or if one value is produced per cashier/store (if the balancing level is cashier) or per register/store (if the balancing level is register).
Created with the wizard Determines whether the wizard is used to actually write the code for the total definition.

Note: Selecting No is supported for on-premise implementations of Sales Audit only.
Required by the system Determines whether the total is system required. The only totals that are required by the system and must be set up before production usage are:

  • OVRSHT_S - Used for the store level over/short total.

  • OVRSHT_B - Used for the balancing level over/short total (this is only used if the system option for Balance Level is either Cashier or Register).
Display Group Determines the order in which the total appears on the Over/Short and Miscellaneous totals page.

Define Realms and Joins

Figure 2-3 Realms and Joins Window


The Realms section allows you to add existing realms (tables) to the total. The list of available realms is displayed in the window.

Availability of realms is determined by whether possible joins exist with the realms that are already added to the total definition (the wizard does not allow incomplete joins that create Cartesian products).

In general, you should add the general/parent tables, and then add the specific/child tables. If at any time, a realm that you want to use is unavailable, you should remove the realms already added to the total, and try again in another order.

The Joins section displays how the realms are joined together. These joins are based on metadata defined in the system.

Define Parameters and Roll-Ups

Figure 2-4 Parameters and Roll-Ups


The Parameters section allows you to choose the parameters for the total definition. You can only choose parameters (columns) that are available in one of the realms that were previously added to the total definition.Begin by choosing one of the realms. The parameter list then displays a list of all of the parameters that belong to the selected realm. You must choose the parameter that you need to count or sum and any other parameters that may be used to limit the data set.

The Roll Ups section allows you to define the parameter that is counted (or summed). It also allows you to define up to three parameters to group by. Only parameters that were previously added to the total definition can be either counted (summed) or grouped by. The system by default rolls up the totals to either store or balancing level (cashier/register) level based on whether the total is defined at the store level or cashier/register level

Define Restrictions

Figure 2-5 Restrictions Window


The Restrictions window allows you to limit the data set that is included in the total values. You can only restrict with parameters that were previously added to the total definition.

Restrict Results By Table Values

Restricting allows you to make numerical as well as logical comparisons on the set of parameter values that have been added earlier to the total definition.

Restrict Results By Constant Values

Restricting by a constant value allows you to define totals that only apply to a simpler data set that can be identified by a constant.

Define Combined Total Details

Figure 2-6 Combined Total Details


The Combined Total Details window allows you to combine two existing totals. In order to be combined, the totals must be compatible. To be compatible, totals must be defined at the same balancing level and have the same roll-ups.

Define Location Traits

Figure 2-7 Location Traits Window


The Location Traits window allows you to associate total definitions with stores. The totals are calculated for each store that has the location trait. If multiple traits are added and have overlapping stores, the total is calculated once per store. Location Traits are defined in RMS. It is recommended that when Sales Audit is used, you create an All Stores location trait and associate that trait with all available stores. While some regional or loss prevention totals can be associated with limited groups of stores, you must associate other totals (such as, Over/Short) with every store that uploads data to Sales Audit.

Define Usages

Figure 2-8 Usages Window


The Usages window allows you to define what should be done with values calculated by the total definition. There is a usage for each export. Total definitions that have export usages flag their resulting total values so that these values are selected and written to the export files. There is a usage for the Flash Totals Report and a Flash Sales Year to Date report too.

Create a Total Definition

To create a total definition:

  1. From the Tasks menu, select Create Total Definition. The Total window appears.

  2. In the Total field, enter the ID and description of the total definition.

  3. In the Start Date and End Date fields, enter the dates for which the total definition is effective.

    Note:

    If you leave the End Date field blank, the total is calculated indefinitely.

  4. Select a category for the total in the Total Category field.

  5. Click Next to navigate through the wizard. Help for the selected field and button appears in the section on the right side of the window.

    Note:

    Select Raw Data or Existing Total from the Total Overview panel to indicate how the total is defined. If you select Raw Data, you create a completely new rule. To do this, you need a thorough knowledge of the tables and columns in the database. If you select Existing Data, you create a sum of existing totals.

    You must associate all totals defined with a specific location list. For more information, see Define Location Traits

  6. Click Save to create the total definition.

Create Combined Totals

You can combine existing totals to form a combined total using the Combined Total Details window.

To create a combined total:

  1. From the Tasks menu, select Foundation Data > Create Total Definition. The Total Maintenance window appears.

  2. In the Total field, enter the ID and description of the total definition.

  3. In the Start Date and End Date fields, enter the dates for which the total definition is effective.

    Note:

    If you leave the End Date field blank, the total is calculated indefinitely.

  4. Select a category for the total in the Total Category field.

  5. Click Next. The Attributes window appears.

  6. In the Display Group field, select the order in which the total appears on the Over/Short and Miscellaneous totals window.

  7. Click Next. The Combined Total Details window appears.

  8. Select Actions > Add. The Add Totals window appears.

  9. In the Operator field, select the operator.

  10. In the Total field, select an existing total.

    Figure 2-9 Add Totals Window


  11. Click OK to add the total. You can add more totals by clicking OK and Add Another.

    Figure 2-10 Combined Total Details Window


  12. Click Next to navigate through the wizard. Help for the selected field and button appears in the section on the right side of the window.

  13. Click Save to create the combined total.

Manage Totals

You can manage totals using the following procedures:

Edit Totals

To edit a total definition:

  1. From the Tasks menu, select Foundation Data > Manage Total Definitions. The Total Search window appears.

  2. Search for and select the total definition you want to edit.

  3. From the Action menu, select Edit or click the Edit icon.

    The Total Maintenance window appears.

  4. Select the page you want to edit.

  5. Make the necessary changes and click Save to save the changes.

View Totals

To view a total definition:

  1. From the Tasks menu, select Foundation Data > Manage Total Definitions. The Total Search window appears.

    Figure 2-11 Total Search Window


  2. Enter additional criteria as desired to make the search more restrictive.

  3. Click Search. The totals that match the search criteria are displayed in a table.

  4. Select the total you want to view and from the Actions menu select View.

    The Total Maintenance window for the selected total appears.

Update Total Status

The default status of a defined total is Worksheet. You can set the status of a total definition to one of the following actions:

  • Submit a total definition for approval

    You can submit a total definition using the Submit option, this changes the status to Submitted.

  • Approve a total definition

    When a total definition is submitted for approval, you can approve it by changing the status of the total to Approved by using the Approve option.

  • Disable a total definition

    You can disable a total definition by changing the status to Disabled by using the Disable option.

  • Delete a total definition

    You can delete a total definition by changing the status to Deleted by using the Delete option. You cannot delete an approved total.

To change the status of a total definition:

  1. From the Tasks menu, select Foundation Data > Manage Total Definition. The Total Search window appears.

  2. Search for and retrieve the total definition in Edit mode. The Total Maintenance window appears.

    Note:

    If you want to submit a total definition for approval, restrict the search to the totals in Worksheet status.

  3. From the Submit menu, select a status for the total. You can select from Submit, Approve, Disable, or Delete.

  4. When the system prompts you for a confirmation of status, click Yes.

  5. Click Save and Close to save your changes and close the window.