Virtual Columns

All new style reporting pages - both baseline and custom - now offer end users and administrators the ability to create "virtual" columns! Administrators can create Virtual Columns on a global level for all end users of a report, or end users can create them privately for their own pinned reports.

These virtual columns can be created directly on the reporting page at the time the report is executed. If the column you want to add is a combination of fields that already exist, you no longer need to modify the query or custom report, or request that Millennium add a new column to a baseline report - it's available right at your fingertips!

Virtual Columns can be used to create columns where a formula may be required, such as:

Total = Column A + Column B - Column C

Or they may also be used to combine or concatenate two columns into one, such as:

New Column = (Name, Position Title)

Virtual Columns are available across FAST applications and are created as follows:

Select and execute the report you wish to create a Virtual Column for. This can be any baseline or custom report.

To create a column that merges data from existing columns:

Select the two baseline columns you wish to merge or concatenate.

  1. Click the settings gear button above the report results

  2. Click Add Virtual Column from the drop-down:

  1. Populate the Virtual Column worksheet and complete all necessary fields:

 

Fields to complete include:

Title - This is the Title of what will become the new column.

Title (French) - Available where a second language is employed.

Width - Default is 100 pixels; can be customized to suit the new column data.

Sort Order - Column placement; Use the Sort Order Lookup to review current state of this report.

Format - Select from the available options or leave as "No Format" for text only.

Alignment - Select Left, Right or Centre.

Calculate Footer Total - Yes or No; this applies only to calculated column formulas.

Formula - Enter any required Operators and build your column formula based on the Available fields in the drop-down menu. Click "Add Field" and "Add Operator" to include. Non-numeric operators may include:

Available to all users? - Yes or No; this option will only display if you are an Administrator in this application; this will make the column visible globally and for all end users. It is strongly recommended that you only enable the Virtual Column for all end users once you have enabled it first for yourself and tested it sufficiently.

Clear Formula - Used to clear current formula and start over.

(R) = Restricted Field - This notation will indicate which columns on the current report (if any) are Restricted Fields

Click Save to complete and to test your Formula. If there are errors, FAST will notify you and you can correct this and re-save. If the errors cause the report to fail, FAST will notify you and allow you to manage the report using the gear button only. FAST will not run a report that will cause it break based on a flawed formula.

NOTE: End users who are not Application-Level Admins and/or do not have the role VIRTUALCOLUMN_EDITOR for the current schema will not be able to key a formula directly into the text box; they will be forced to use the drop-down menu and operator buttons.

Once your column has been rendered, FAST will automatically refresh the page to display the new column with the position and formatting you specified.

 

Formatting Tips!

For non-numeric Virtual Columns:

The way to specify a basic string literal is in single quotes 'like this'.

So if Column A = FAST and Column B = Training, then:

Format to create a basic if/then statement:

(case when GENDER = 'F' then 'WOMAN'

     when GENDER = 'M' then 'MAN'

     else 'Undefined'

end)

 

To create a column based on a formula or calculation:

The process is identical in terms of steps, however you will need to create a valid formula.

To do this, make sure you select the appropriate data type format and the following operators will be available to build your formula:

The parentheses and operators are for math operations to execute successfully, because (A + B) / (C - D) is different than A + B / C - D

It is therefore important to draft your formulas correctly for accurate execution.

Select from the Available Fields to include columns in your formula and use the Operators to complete the formula or equation to be used.

Tips! For numeric formulas, remember that this is a very powerful feature and you do have the ability to cause some serious damage to the reports if not done correctly. If in doubt, be sure to ask us first! Here are a few examples to be mindful of:

For help with your formula, please contact Millennium Customer Support.

 

To manage your Virtual Columns:

  1. Click the gear button on the Report Header.

  2. Select Manage from the available options.

  3. A text box will display showing you all of your personal Virtual Columns as well as the Public Virtual Columns that you have created for this reporting page:

  1. Notice the options:

To correctly manage, edit or delete your Virtual Columns will depend on whether you have saved them as Private or Public.

Any Virtual Columns that are created and set to Public will default to a column State of "De-selected", meaning they are available in the Advanced Options for end users to re-enable and use, but not on by default when the report is run. If an administrator would like for this column to be visible by default, they can modify the column State using the Datagrid Columns Tab under the report-level Administration.

Review Deleting Virtual Columns for the available options.

 

Related topics