Home > Alerts > Configuring Alerts > Result Exports

Alert Result Export

Each alert may contain multiple Result Exports – each one is passed the results of the SQL query when the schedule is fired. The types supported are described in the following sections.

Figure: Configuring a Result Export

Notes:

Chart Export Type

Each chart requires a description, a file name and size parameters for the output image. The target amount, upper limit and axis interval values are optional and defaults will be used if they are omitted.

The final option Orientation has relevance only for the bar chart types (see sections below).

Different types of chart are supported as indicated in the following sections.

Pie Chart

The pie chart expects the SQL results to contain 2 columns per row. The first is the name of the wedge; the second is the wedge amount.

Bar Chart

The bar chart expects the SQL results to contain 2 columns per row. The first is the name for the bar; the second is the bar amount. The Orientation option governs whether the chart is drawn with the bars rendered horizontally or vertically. The default setting is Horizontal and care should be taken when selecting the Vertical option since the chart must then be configured to be wide enough to allow the column headings to show correctly. (When the bars are rendered vertically, the label is shown underneath the bar; when horizontally, the label appears to the left of the bar.)

Bar Chart (Integer)

A variation on the Bar Chart is also available. The values in the second column displayed in the bar chart are amounts and are formatted as currency values.  If the user selects the Bar Chart (Integer) type, the values are assumed to be counts and are formatted as integers.

Hourly Sales Bar Chart

The hourly sales bar chart expects the SQL results to contain 2 columns per row. The first is the hour (value between 0 and 23) and the second is the amount.  Currently the chart is set up to create a bar for each hour between 08:00 and 20:00 – if the SQL results do not contain an amount for an hour 0 is used.

The Orientation option governs whether the chart is drawn with the bars rendered horizontally or vertically.  The default setting is Horizontal and care should be taken when selecting the Vertical option since the chart must then be configured to be wide enough to allow the column headings to show correctly.  (When the bars are rendered vertically, the label is shown underneath the bar; when horizontally, the label appears to the left of the bar.)

Meter Chart

The meter chart expects the SQL results to contain only 1 column and 1 row. This is the amount to display on the “meter”. An example of the meter chart is shown below.

Figure: Meter chart example

Currency Meter Chart

This is the same as a meter chart but the value reported is assumed to be a currency amount and formatted as such – for example £1,234.56.

The target amount can be used with the meter and currency meter charts to identify something of significance on the output:

Figure: Chart with target amount

Setting Target Amount Dynamically

The target amount included on some of the charts can be set explicitly when defining the result export or dynamically using a script. To set the amount dynamically, click on the Add Script for Target Amount link and the following screen is displayed:

Figure: Editing Target Amount Script

The SQL script entered here should return a single value – the required target amount. If the script returns more than 1 value the extraneous ones will be ignored; if the first value returned is not a valid decimal, the target amount will not be set and an error will be shown if you try to update the target amount.

As with the script for the alert, there is a link underneath the input box to let you test your SQL against the database. You can also specify a schedule for when this target amount script is run – similarly to the schedule definitions for generating alerts.

If you specify a script to get the target amount, the result export screen will show the SQL entered and a red button to let you delete the entered script.

Figure: Result Export with Target Amount Script

A new link is shown next to the Target Amount input field – Run Script for Target Amount. This will run the SQL script currently showing in the line above and put the result in the Target Amount field. 

Note that as with all changes, the SQL script entered and the target amount resulting will not be committed to the database until you have saved all your changes for this alert (on the edit / create alert page).

HTML Export Type

Each HTML export requires a description and a file name. The following sections cover the different types of HTML export supported.

Custom HTML

The SQL results for a custom HTML export may contain any number of columns. The only requirement is that a Field is configured for each column – see Fields for more information.

Custom HTML exports may also include information about the layout of the HTML file created:

  • the justification of the HTML file within the portal component

  • the percentage of the component taken up by the HTML file

  • whether or not to add margins.

Figure: Custom HTML screen

The justification field affects the alignment of the HTML file created within the portal component and its default is ‘centre’. The screen percentage value (% of available space) must be in the range 10 – 100 and the default value is 100. If the screen percentage is 100, then the justification will not have any effect.

Each margin percentage specified may be in the range 0 – 35; with 0 as the default. Adding a margin adds a new (empty) column as either the first or the last column in the HTML file created.

Application Alerts

The application alert HTML export expects the SQL results to contain 4 columns per row. The expected columns are (in order):

  1. Date – the date the alert was created.

  2. Priority – the severity of the alert (1 = Low, 2 = Medium, 3 = High).

  3. Description – the alert description which can contain Java message format tags (e.g. {0},{1},), each of these tags is replaced with the corresponding token in the MessageData column.

  4. MessageData – a comma-separated string of tokens used in the description column.

Hourly Sales

The hourly sales HTML export expects the SQL results to contain 4 columns per row. The expected columns are (in order):

  1. Hour – the hour this row represents.

  2. Sales Quantity – the count of sales performed in the hour.

  3. Return Quantity – the count of returns done in the hour.

  4. Amount – the total sales amount (including returns) for the hour.

Currently the export is set up to create a row for each hour between 08:00 and 20:00 even if the amount is 0.

Customising HTML Output

This section covers the formatting of the HTML file created for an HTML export. The HTML file created uses elements of the portal.css cascading style sheet which can be altered to change the look and feel of the displayed data. 

Note: Changes made to the style sheet may have ramifications beyond those indicated below so you should be very careful and should always take a back-up copy of the style sheet before making any changes.

The following elements in the style sheet affect the entries added for the HTML data:

tr.headerRow                   tr. standardRow                   tr. footerRow

td columnHeadingCell        td. itemValueCell                   td.footerCell

The table of entries created for the specific export are enclosed within other table elements for the portal components. 

XML

Each XML export requires a description and a file name. The SQL results for an XML export can contain any number of columns. The only requirement is that a Field is configured for each column (see Fields for more information).

SMS

Each SMS export requires a description.  If a message to be sent via SMS is longer than 160 characters it will be truncated automatically.

Multiple protocols for SMS messaging are supported – Retail POS, AT&T, TextAnywhere and a TEST format which simply logs the message to the trace output window.  The options for configuring each of these are in the properties file.

Different types of SMS export are supported as indicated in the following sections.

Custom SMS

The custom SMS export expects the SQL results to contain only 1 column per row. This should be the message to be sent to all SMS contacts set up for this export.

TextAnywhere™ Support

The client reference and billing reference fields are used in the TextAnywhere implementation of SMS. These fields are for informational / tracking purposes and hence are optional but as they are required for the messages to be sent a default value will be used if one is not supplied by the user. 

The originator field set up in the application properties file may be either a telephone number or an alphanumeric field – for example TOREXAlert. The configuration code will validate this field as follows:

  • If an international format telephone number is specified - +447123456789 the originator field will be used as read from the properties file.

  • If a telephone number is specified without a country code but with a leading 0 (01611234567) the leading 0 will be replaced with the country code for the UK - +441611234567.

  • Anything else is assumed to be a non-telephone number, alphanumeric code and will be truncated to 11 characters if necessary.

TextAnywhere messages to multiple contacts for a single export will be grouped together (up to 150 numbers at a time) into a single call to the TextAnywhere SendSMSEx routine.

The address field for a contact must be a telephone number and the TextAnywhere web-site says that the telephone numbers ought to be in the international format – +4471234567890 – however this does not seem to be a restriction of the TextAnywhere code and hence this rule is not enforced. Invalid telephone numbers will generate an error return code from the TextAnywhere send attempt but this will not be reported to the user unless the debug option is set (in the properties file) in which case the error message will be traced in the debug window.

Application Alerts

The application alert SMS export expects the SQL results to contain 4 columns per row. The expected columns are (in order):

  1. Date – the date the alert was created.

  2. Priority – the severity of the alert (1 = Low, 2 = Medium, 3 = High).

  3. Description – the alert description which can contain Java message format tags (for example {0},{1},), each of these tags is replaced with the corresponding token in the MessageData column.

  4. MessageData – a comma-separated string of tokens used in the description column.

Customer SMS

The customer SMS export expects the SQL results to contain 2 columns per row. The first is the phone number to which to send the message; the second is the message to send. 

Note: Any SMS contacts set up for this export will be ignored for this type.

Retail POS Ticker Tape

Retail POS ticker tape exports should return 1 column per row. This is the message to be sent to all Retail POS ticker contacts set up for this export.

Converted from CHM to HTML with chm2web Pro 2.85 (unicode)