SQL - Highlighting

The Highlighting function enables you to apply conditional highlighting to all report fields. You can highlight the column by specifying a condition based on that field's value or by a condition of other field values, or both. The highlight appears both in the browse grid as well as the printed report. It does not apply to Labels.

For example, if this condition is met, then highlight column 3 with Yellow.

The syntax for the highlighting condition must be valid Clarion Syntax.

Fields used in the condition must be either:

a) The name of the Column Title

-or-

b) The name Fieldx where x is the column number you are evaluating.

You can use a mix and match of either in your syntax

Note: By default, sub-totals and grand totals are included in highlighting.

Using the SQL Wizard

After selecting the field to be highlighted, click Format Column, then the Highlighting tab. Click on the Insert This Column Field button and the current column title will appear in the Conditional Highlighting Formula area. If the Column Title has been overwritten, the new name will show in the area. If the column title has not been changed, the field name will appear. A condition then needs to be defined to determine the data to be highlighted. Click here to learn more about conditions.

For example, to highlight any current folio balances greater than $1000, from the FOLIODT table, select the BALANCE field. Double click to expand it and select the Highlighting tab. Click on Insert This Column Name and BALANCE will appear in the Conditional Highlighting Formula. Type in > 1000 and then select Pick Highlight Background Color button to choose the highlighting color to use. The default color is yellow.

tip

Remember to select a color that will allow you to still see the data.

By default, all rows in the query are evaluated for highlighting. If you add the "IGNORETOTALS" attribute at the end of the line, then SUBTOTALS and GRANDTOTALS are ignored from being evaluated for highlighting.

Using Free-Form SQL Syntax

Highlighting extensions must be entered after any SUBTOTAL statement and prior to any other SQL extension used. Each highlight statement must appear on its own line, with a carriage return between each.

HIGHLIGHT field name/column number of field to highlight WHERE valid condition [BGCOLOR=color number]

-or-

HIGHLIGHT column number of field to highlight TO column number of field to highlight WHERE valid condition [BGCOLOR=color number]

SELECT Number,Sub,TotalEntries as 'Total Entries',Balance

         FROM FOLIODT 

         ORDER  BY Number,Sub

         SUBTOTAL TotalEntries,Balance ON Number

         HIGHLIGHT 3 WHERE Total Entries > 5 

         HIGHLIGHT 4 WHERE Balance > 1000 BGCOLOR=333333 IGNORETOTALS

         HIGHLIGHT 1 TO 4 WHERE Balance > 1000

Condition

A condition must be valid Clarion Syntax and can be up to 3,000 characters in length. The entire condition must be on one line of data without any carriage returns. Each column referenced in the conditional statement must be either the Column Title or in the form of FIELDx where x is the column number is the result set.

Click here for functions available in the Clarion Syntax. All numeric values should be as-is and all alpha-numeric values should be in single-quotes, such as: AccountType = 100 and AccountStatus = 'A'

Conditional examples:

The following will highlight if column 1 is more than 1000 and column two has an O value:

FIELD1 > 1000 and FIELD2 = 'O'

The following will highlight if the "Total Balance" column 1 is more than 1000 and column two has an O value:

Total Balance > 1000 and FIELD2 = 'O'

The following will highlight the Due Date column if in the future:

Invoice Date > Today()

The following will highlight the Description column if the second character is an a:

sub(Description,2,1) = 'a'

The following will highlight the phone column if there is no dash:

instring('-',Phone,1,1) = 0

NOTE: All field to field comparisons are done as STRING TEXT. So, if one column is 45.45 and the other is 1200, the condition could be incorrect because 4 is greater than 1 in string text, but for numeric values, 1200 is greater than 45.45 Evaluation statements on any Fields are only compared as string text only. To correct this, use this syntax for comparing numeric fields to compare values in the same way.

Use

 FORMAT(FIELD1,@N-013.2) > FORMAT(FIELD2,@N-013.2) 

instead of

 FIELD1 > FIELD 2

This will compare "0000000045.45" with "00000001200.00" and yield the proper comparison.

Highlighting Only Totals

By default, all entries are highlighted. However, if you are writing a query with sub-totals, usually field1 (column 1) is normally blank to produce the subtotals and totals. To only highlight totals, just add AND FIELD1 = ' ' to each evaluation statement.

select number,debit,credit from FOLIOTRN

order by number

subtotal 2,3 on 1

highlight 2 where field2 > 100 and field1 = ' '

Highlight Color (BGCOLOR)

The color chosen must be the numeric equivalent of the Red, Green, Blue components expressed as a whole integer number, or chosen from the Color Dialog box within the Wizard. You can manually enter the numeric value of the color or use the following equates:

COLOR:Black COLOR:Maroon COLOR:Green COLOR:Olive COLOR:Orange COLOR:Navy COLOR:Purple COLOR:Teal COLOR:Gray COLOR:Silver COLOR:Red
COLOR:Lime COLOR:Yellow COLOR:Blue COLOR:Fuchsia COLOR:Aqua COLOR:White COLOR:None

SELECT Number,Sub,TotalEntries,Balance

         FROM FOLIODT 

         ORDER  BY Number,Sub

         SUBTOTAL TotalEntries,Balance ON Number

         HIGHLIGHT 4 WHERE Balance > 1000 BGCOLOR=COLOR:LIME

SQL - Highlighting

Using the SQL Wizard​

Using Free-Form SQL Syntax​

Condition​

Conditional examples:​