SQL - Format Column / Format Title

tip

The Format Column (FORMATCOLUMN) function and Format Title (FORMATTITLE) allows you to customize the look of the fields when printing a report. It does not affect the values when being displayed in the Data Grid Preview.

Using the SQL Wizard

Select the field to be formatted from the right side window pane of the first page of the SQL wizard. Highlight the field and click the Format Column button the format the field.

Format Tab

• Hide Column - Check the box for hide column to not display the data. The data can still be used for sorting the report. This option only applies to reports. If labels are printed, the column will be included.

• Column Title - Type in the title you want for the column. This will appear on the report so keep in mind the length of the column title to match up with the data it is labeling. Avoid having a column title 20 characters long for data that is only two characters.

• Numeric Format Options - Check the box to Override Default Format. Select the number of integer spaces you want in the size field and the number of decimal places in the decimal field. For example, a size of 20 with a decimal setting of 5 would be able to display a result up 20 spaces to the left of the decimal and 5 spaces to the right of the decimal.

• Font Dialog - Click on this box to customize the font type, font style, and size by using the drop down boxes. The color can be changed by clicking on the color drop down box..

• Justification - Click the drop down box to change the justification for this field. Numbers default to right justified and labels default to left justified.

• Width/Height - Use these fields to increase the height and width of each field within the column. If the font has been increased, increase the height and width accordingly to display the data clearly. These fields are measured in pixels so 1000 width by 1000 height is 1 inch by 1 inch.

• Picture - This is only for numeric fields. Click the ellipsis button to display common numeric pictures. Choose the picture you want by double clicking on it. Click the box for the monetary symbol you want to use and also if you want it to show on the right or the left. Checking the box to Suppress if Zero will display a blank field for any zero results.

• Suppress if Duplicated - Check this box to display only one instance of each result on the report. For example, if reviewing information including room types, each room type will only appear in the column once, like a sub header.

After formatting the column you can change the value of the field or highlight specific data in the column.

Using Free-Form SQL Syntax

Format Column and Format Title extensions must be entered after any HIGHLIGHT statement and prior to any other SQL extension used. Format columns and titles does not override any highlighted column or change the bolding of a Total or Sub-Total.

Use FORMATCOLUMN to change the data - use FORMATTITLE to change the title.

FORMATCOLUMN column_number_of_field_to_format, ATTRIBUTE=VALUE

FORMATTITLE column_number_of_field_to_format, ATTRIBUTE=VALUE

-or-

FORMATCOLUMN column_number_of_field_to_format TO column_number_of_field_to_format , ATTRIBUTE=VALUE

FORMATTITLE column_number_of_field_to_format TO column_number_of_field_to_format , ATTRIBUTE=VALUE

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

         FROM FOLIODT 

         ORDER  BY Number,Sub

         SUBTOTAL TotalEntries,Balance ON Number

         FORMATCOLUMN 1, WIDTH=1000

         FORMATCOLUMN 1, HEIGHT=1000

         FORMATCOLUMN 1, FONTNAME=Tahoma

         FORMATCOLUMN 1, FONTSTYLE=8892  /* bold and underline */

         FORMATCOLUMN 1 TO 7, FONTSTYLE=8892 /* bold and underline columns 1 through 7 */

         FORMATTITLE 1 TO 7, FONTSTYLE=8892 /* bold and underline titles 1 through 7 */

Attribute	Value
FONTNAME	Font name of a valid font, and must exist on all workstations running the report. (IE: Verdana)

(Note: Normal size fonts use a height of 140. If you use any larger font greater than size 10, you would have to increase the height by 100 for each pitch. Example, use 240 for Pitch 12 and 360 for pitch 13, etc. | | FONTSIZE | Size of the font (ie: 10) | | FONTSTYLE | Style specifying the strike weight and style of the font. The style is a total value of all styles wanted, added together, as a numeric representation. Thin = 100 Regular = 400 Bold = 700 Italic = 4096 Underline = 8192 StrikeOut = 16384 | | FONTCOLOR | A LONG integer constant containing the red, green, and blue values for the color of the font in the low-order three bytes. |

| WIDTH | The width of the field, in DLU (Dialog Units) where 1000 equals 1 inch. | | HEIGHT | The height of the field, in DLU (Dialog Units) where 1000 equals 1 inch. | | JUSTIFY | Indicates the field justification. Valid values are Left, Right, Center | | SUPPRESS | A value of 1 (or TRUE) to indicate to suppress this field if it is duplicated from the last printed line. | | HIDE | A value of 1 (or TRUE) to indicate to suppress this column from being displayed. Note: The HIDE attribute WILL appear on the Data Grid and the report | | TOTALPICTURE | A picture value (@n-13.2) that formats all sub-total and grand-total columns differently than the detail columns. If not specified, the format of the column will match the detail columns. | | COLUMNPICTURE | A picture value (@n-13.2) that formats all columns in the grid as well as all columns in the report. The total columns can be overridden with the TOTALPICTURE attribute as well.

@n (numeric using x.y for number of digits and decimals)

@s (string using @s128 for number of characters)

@d (date using @d01 through @d017)

@t (time using @t01 through @t07)

Can also be used to format months and days of the week.

Use @MonthName to convert 1, 2, 3, 4 into January, February, March, April.

Use @SMonthName to convert to Jan, Feb, Mar, Apr.

Use @DayOfWeek to convert number values to Sunday, Monday, Tuesday, Wednesday, etc. using standard SQL DOW (1=Sunday, 2-Monday, etc.)

Use @TrueFalse to convert checkbox fields (1=True and 0=False) |