Aeros VISION Query Grid Custom Field Formatting

Owned by Denise Blackford
Feb 15, 2022
2 min read

Database table columns are the containers that store most of the data being returned in a query. Each column in each table is set to use a specific data type. Often, that data type is not representative of how most users would view data in the real world. 

Ex. Most users are expecting currency fields to return values similar to: $35.01 but often the value is stored in the database as 35.01002. VISION allows users to format the data in the queries and reports so information can be presented in a visually please and easy to understand manner.

Pre-Requisites

  1. A previously created query within VISION.

Step-by-Step Guide

Data can be formatted in the VISION QUERY grid or in the VISION report, 

These instructions will step through formatting data fields within the VISION Query Results Grid. 

Formatting Fields within the Results Grid:

  1. Log into VISION 

  2. Select and run the query that requires field formatting. 

  3. The VISION Results grid is displayed. 

  4. In the example below, the total standard unit cost field was included as well as the weight field, and a custom calculated field for cost times weight. 

  5. Right click on the column heading of the field to be formatted. In this case, the Std. Unit Cost field. 

  6. From the sub-menu that displays, select Display Format Editor option 2nd from the bottom.

         

    7. In the Display Format box, expand DisplayFormat. This will now allow you to select a FormatType as well as FormatString. In this example, select FormatType - Numeric

         

    8. Numeric precision will display 4 or more characters after the decimal point. To set the field to a currency format, in the FormatString field, enter: {0:c}  This will set the field to currency with the dollar sign added and precision set to 2.

 

         

 

    9. If you set the the FormatString to {0:n3}, the field will be numeric with 3 digit precision. To show as 2 digit precision, without a dollar sign, enter {0:n2}

         

     10. When formatting fields in the grid, the data in the underlying table is not formatted. Only the grid display is being formatted. Therefore, you must save these changes via the Grid View Layout. Each time the query is run, and the Grid View Layout that the format was saved to is selected, the column will display the data in the selected format.

 

         

  

    11. For formatting Date fields, Dates are have a variety of ways to be displayed. There are Date fields, DateTime fields, etc. In the FormatString Field of the Display Format of a date type field, you can enter simply a d for 6/12/2021 or D for long date as displayed below: