Adding and Creating Columns

As with tables, you need at least one column (or all columns) in a query to display meaningful information. The assumption here is that your query has at least one table selected. In the query builder you can add, rename, create new, group by, remove, and sort columns.

The processes below are the same for new queries, and for modifying existing queries (once opened in the editor). To modify, re-sequence, or remove columns, see the topic, Modifying Columns.

Select Existing Columns

NOTE:

By default, every time a file is added to a query all columns are selected and added to the query.

Steps

  1. Once in the Query Editor (see Creating a New Query for information) press the table icon for the table containing the columns you wish to work with.

    The Edit Table panel opens on the right showing all the columns in the table.

  2. Check or uncheck individual column names, or press the Select All or Deselect All buttons to add and/or remove columns.
  3. Press Save when finished.

Apply a Function to a Column

Steps

  1. For the column you wish to alter, simply click on the header of the column in the results section.

    The Column Setting panel opens on the right.

  2. Select a function from the drop-down list. If you hover over the function you will see a brief description and syntax.

  3. Once selected you will have to edit to make sure the starting field is placed inside the function correctly.

  4. Press OK when finished.

Add (create) a New Column

New columns can be created by applying function to existing columns, or modifying columns using numeric or character functions.

Steps

  1. Press the Add Column button.

    The Column Settings panel opens on the right.

  2. Enter a Name for the new column.

  3. Select one of the following Format types based on the type of data being created:

    None: This option will not apply any formatting to the value.

    Number: Select for non-currency numeric values. Options are available to select Scale (number of decimal points) and Separator.

    Currency: Select for currency values. Options are available for Scale, Separator, and Currency Symbol.

    Custom: Select if you want to apply a custom pattern (or mask) to the value similar to a telephone or social security number.

    Date: Select for date values. Options are available to set the format (pattern) of the date.

  4. Optional: Change the Content Type to Status if you are creating this column for a dashboard Status Icon data decoration. Leave this value as None for all other expressions.

    If using type Status, then a CASE expression is required that returns any of the known values (0=Good, 1=Info, 2=Warning, 3=Bad, 4=Error, 5=Error) required by the Status Icon.

  5. Select a Function from the drop-down, and replace the 'expression' with a column name.
  6. Press OK when finished.

 

Related Topics