Search TipsTables
Part 1: Plotting Fields
Example of Table Visualization
Column
Drag and drop fields from the Fields pane that are to be columns in the table into the Column box of the Plotting Fields tab.
If the dragged-in field is of the URL data type instead of the String data type, the column’s text is displayed as a hyperlink. A field’s data type can be changed from the String type to the URL type via BI Catalog Builder. For details, please refer to the BI Catalog Builder reference guide.
Also, if a table is created using a special Advanced Analytics view, which is a special view that contains a column with links to each record’s screen, the column with the links, titled Entry Screen, is automatically added to the table. For details, please refer to the upcoming section, Create BI Springboards.
Column Line Aggregation 
Example of table visualization with a Number data type field
For columns of the Number data type, such as the Spent to Date column in the above screenshot, a down-arrow (
) is available to select an aggregate function for them, as shown below:
When an aggregate function is selected for a column, the aggregate function is used to calculate a single value for the column, for every unique line.
The uniqueness of a line is determined by the values of its non-aggregated columns, hence the columns to include in the table must take this into consideration.
For instance, in the following screenshot, the Inv Amt column is to be the aggregated column, so the Vendor Code, Vendor Name, Due Date: Year and Due Date: Month column values are used to determine the uniqueness of a line.
After selecting an aggregate function for the Inv Amt column, the aggregate value for each unique line is calculated, as shown below:
Column Label, Group Sort, Column Aggregation 
Label
Use this property to change the column’s title. If the column title is changed, hovering over it reveals its default title.
Alignment
Use this property to change the column’s alignment. Alignment options are “Left”, “Centre”, or “Right” aligned. This property is also applied to PDF and Excel exports.
Visible – Checkbox
Use this property to show/hide the column in ADF, PDF exports, and Excel exports.
Format
Use this property to specify the number format.
Sign
Use this property to change the final display of numbers without affecting calculations. Available options are "Default", "Reverse", and "Absolute". By default, the "Default" option is selected. Selecting "Reverse" changes the sign of numbers from positive to negative and from negative to positive, and selecting "Absolute" changes the sign of negative numbers to positive.
Conditional Formatting: Text Color, Background Color, Copy to All Columns
Use this property to turn conditional formatting for text color and background color on or off using the 'On' and 'Off' radio buttons. When either the text color or background color property is turned on, a Format Expression link appears below the property. This link is used to launch a pop-up window where format expressions are entered. The 'Copy to All Columns' box is used to copy the column's conditional formats to all columns. Refer to Conditional Formatting to learn more about how this property is used.
Grouping: Group Sort, Line Thickness, Line Style, Line Color (Group Sorting)
Tables provide the option of group sorting their rows by particular fields (columns).
To create a group sort, click the relevant field’s up-arrow () to launch the pop-up, shown above. In the pop-up, check the ‘Group Sort’ checkbox, and optionally, use the Line Thickness, Line Style, and Line Color properties to select the line’s thickness, style and color.
If more than one field (columns) is to be group sorted, a thick line can be set for the first group sort and a thinner line for the second one, as shown in the previous screenshot. A different line style and line color could also be used for each group sort to further distinguish them.
On the Sorts & Filters tab, fields that are group sorted appear at the top of the Sort box and they have an asterisk at the end of their name.
In the table, as shown in the previous screenshot, rows that are group sorted by fields only have their first row displaying the name of the group sorted fields. If the grouped rows continue to the next table’s page, the first row on the new page displays the field names in italics, as shown below:
Also, for the Visualizations Properties Control, dropping a field that is not group sorted between two fields that are, makes the dropped field a group sorted one.
NOTE: For the Visualization Properties Control, all fields added to the Column box on the Plotting Fields tab are automatically added to the Sort box on the Sorts & Filters tab. Also, if a field is added to the Sort box on the Sorts & Filters tab, it will automatically be added to the Column box on the Plotting Fields tab.
Total: Visible, Label, Calculate (Column Aggregation)
These properties are used to set an aggregate value to display at the bottom of the column, as shown below on the right.
The ‘Visible’ checkbox sets whether or not the aggregate value is displayed for the column, the Label field is used to provide a label for the aggregate value, and the Calculate field is used to select the aggregate function.
Drillthrough: Drillthrough Filter, Filter Name, Disable, Target Dashboard, Target Visual, Copy Targets to (Drill in Place)
Drillthrough is enabled for a table visualization when a column is marked as a drillthrough filter or has a drillthrough target defined (to a dashboard or a specific visualization on a dashboard).
The ‘Drillthrough Filter’ checkbox is used to mark a column as a drillthrough filter and when checked, enables the Filter Name property. In target dashboards, this drillthrough filter name is used to build context variables and substitution value expressions to reference this filter's selected value(s) as drillthrough filter value(s).
The ‘Disable’ checkbox is used to disable this column’s drillthrough. If checked, the related target drillthrough properties in the panel are disabled (i.e. hidden). If unchecked, drillthrough is enabled and the fields below are visible.
The Target Dashboard property is used to select a dashboard as a drillthrough target. The Target Visual property is used to further drill down into the dashboard to target a specific visualization. The ‘Copy Targets to…’ checkbox, when checked, replicates the drillthrough target defined for the column to all other columns.
NOTE: When a visualization does not have a drillthrough target defined, the current dashboard is the default target. When a drillthrough filter is enabled, it will automatically filter down on the current dashboard without needing to assign a target dashboard. Defining a target dashboard is only used to redirect to other dashboards.
When drillthrough is enabled, each regular cell (not including a subtotal) is clickable. When a cell with drillthrough enabled is clicked, the user is taken to the drillthrough target defined for the column. When the column does not have a drillthrough target defined, the current dashboard is the default target.
Each drillthrough filter cell value on the row is sent to the target as a parameter filter.
Pivot Items can also be marked as a drillthrough filter.
When Drillthrough is enabled for a table visualization, and the cells are clickable, the user can enable text selection by using the Visualization drop-down menu and selecting “Enable Text Selection”.
Crosstab View
Refer toCrosstab for more details on using this functionality.
New Calculated Column 
Calculated columns can be created in BI Dashboard Builder for a table visual by clicking the New Calculated Column icon () next to the Subtotals icon. Clicking on this icon launches the Create Column pop-up window, as shown below.
The drop-down lists are used to make selections and the Insert icon ( 
) is added to each list to explicitly insert the selected value. Every calculated column expression starts with the column name then an equal sign (=) followed by an expression. For example, the following expression creates a Total Cost calculated column by multiplying the Unit Cost to Quantity.
Total Cost = [Unit Cost] * [Quantity]
Calculated columns can participate in almost all existing features such as QBE, pivoting, visual filtering, card view, etc. However, it cannot be marked as a drill-down filter and it cannot be aggregated.
A calculated column may not be created from another calculated column. It can be created from number columns only. Therefore, a calculated column’s data type is always number. A calculated column is computed after the columns used in its expression.
Expression Operators and Precedence Rules
A calculated column expression supports the following arithmetic operators:
+ (addition)
- (subtraction)
* (multiplication)
/ (division)
Parentheses can be used in an arithmetic expression to change the order of evaluation. In an expression like:
New Amount A = ([Number Column 1] - [Number Column 2]) / [Number Column 3]
the part of the expression within the parentheses, ([Number Column 1] - [Number Column 2]), is evaluated first, and then the result is used in the rest of the expression.
Column Reference
A column reference is a column name enclosed in square brackets ([ ]) in the following format:
[Column Name]
Functions
A calculated column expression may contain functions. When a function is executed, a single value is returned, and then the value is used in the rest of the expression. The following functions are currently supported:
count
Returns the number of cells in a column that contain non-blank values.
Syntax:
count (<column reference>)
distinctCount
Returns the number of distinct cells in a column that contain non-blank values.
Syntax:
distinctCount (<column reference>)
maximum
Returns the largest number in a column.
Syntax:
maximum(<column reference>)
minimum
Returns the smallest number in a column.
Syntax:
minimum(<column reference>)
median
Returns the median of the numbers in a column.
Syntax:
median(<column reference>)
average
Returns the average of the numbers in a column.
Syntax:
average(<column reference>)
sum
Add all numbers in a column.
Syntax:
sum(<column reference>)
populationStandardDeviation
Returns the population standard deviation (square root of variance) of non-blank values in a column.
Syntax:
populationStandardDeviation (<column reference>)
sampleStandardDeviation
Returns the sample standard deviation (square root of sample variance) of non-blank values in a column.
Syntax:
sampleStandardDeviation (<column reference>)
subtotal
Returns the subtotal values that can be part of a calculated column expression.
Syntax:
subtotal(<subtotal code>, <column reference>)
Subtotals 
Example of table visualization with various subtotals set up
To create a subtotal line, click the Subtotals icon, shown below:
NOTE: Subtotals are not computed for columns that are group sorted.
In the pop-up, click the New icon, pointed to below:
In the Create Subtotal pop-up, define the subtotal line, using the following details.
Calculate
Use this field’s LOV to select the aggregate function that is to be applied to the field selected via the Place subtotal at each change in field.
of all data points 
By default, all numerical fields will have an aggregate value calculated. This pop-up is used to exclude a field from having its aggregate value calculated.
Place subtotal at each change in
Select the field that is to be aggregated.
For each unique value of the field (e.g., if the field is “Job”, then for each job), the calculated aggregate value for it will be displayed just after the last entry for that field.
Don’t display subtotal for a single row
If checked, single rows will not have an aggregate value calculated.
Label
Label for aggregate value rows.
Append Value – Checkbox
If checked, name of field being aggregated will be appended to label identified in Label field above.
Bold
If checked, text for aggregate value rows is in bold.
Font color
Color of text for aggregate value rows.
Background color
Background color for aggregate value rows.
Part 2: Sort & Filter
Tables have unique sorting options for their rows, explained here. For general details about sorting rows, please refer to Sort and Filter – Tab.
For tables only, the fields in the Sort box on the Sorts & Filters tab can be reordered by dragging and dropping them to their new positions, which changes the order by which rows are sorted (rows are first sorted by first field, then by second one and so forth).
The arrows next to the fields indicate their sort order. The up arrow (
) indicates that the ascending sort order is used, and the down arrow indicates that the descending sort order is used. Click the arrows to switch between the two sort types.
NOTE: All visualizations, including tables, can filter by aggregation. For table visualizations, the table must have at least one aggregate column (aggregation is not “None”).
Part 3: Format
Title
Visible – Checkbox: Used to show/hide the title.
Text: Used to set the title for the visualization, which is displayed along its header.
Text Color : Used to set the text color of the visualization's title.
Background Color: Used to set the background color of the title.
Export to PDF 
The following are options for the Export Table to PDF functionality. For details, please refer to the Export Table to PDF subsection.
Orientation
PDF’s page orientation. Options are “Landscape” or “Portrait”.
Size
PDF’s size. Options are “Letter” or “Legal”.
Hide Logo – Checkbox
Allows user to hide PDF/query logo.
Logo
File name of uploaded logo for exported PDF. To clear this field, use the Delete or Backspace keyboard key.
NOTE: If this field is left empty, the logo will default from the Dashboard Logo Image Path field on the Logo Path tab of the System Options screen (standard path: System > Setup > System Options – Logo Path tab).
Crosstab View
Launch as Crosstab View - Checkbox
Allows the user to set the visualization to launch in crosstab view by default.
Refer to Crosstab for more details on using this functionality.
Show Layer - Checkbox
Allows the user to show layer headers in crosstab view.
Drillthrough (Target Dashboard, Target Visualization)
Use the Target Dashboard property to select a dashboard as a drillthrough target.
Use the Target Visualization property to further drill down into the dashboard to target a specific visualization.
Other
Negative Format
Use this property to specify the format for negative numbers. By default, negative numbers are displayed with a minus sign, but there is also an option available to display them in parentheses.
For example:
Width
Shows the width of the visualization. This field is editable to allow the user to adjust the width.
Height
Shows the height of the visualization. This field is editable to allow the user to adjust the height.
Show Border – Checkbox
Check to apply border to visualization.
Rows Per Page
Available if ‘Enable Pagination’ box is checked, to set number of rows to display per page of table.
Enable Pagination – Checkbox
If checked, a table visualization’s rows span over pages, as shown below:
If unchecked, a scrollbar is used to view a table’s rows, as shown below:
Show Pivot Bar – Checkbox
If checked, the Pivot Items bar appears above the table’s columns, as shown above. Alternatively, during runtime, the Pivot Items bar can be made available by dragging and dropping a column header onto the table’s title bar, as shown below:
The Pivot Items bar is used at runtime to filter the table by entries in one or more columns.
For instance, using the above table as an example, if you want the table to display data for a particular vendor, drag and drop the Vendor Name column header onto the Pivot Items bar, as shown below:
Next, click the 
icon (framed below) to select one or more vendors by which to filter the table’s data. Hold the Ctrl or Shift keyboard keys to select more than one item.
As shown below, more than one column can be dragged and dropped onto the Pivot Items bar:
To clear a Pivot Item’s selected item, use the Delete or Backspace keyboard key.
Wrap Cell Text
If checked, when a column is not wide enough to fully display its text, the height of the row is increased to fully display the text. Note, however, text wrapping does not occur for columns with a subtotal defined, or columns with a group sort defined with the Line Thickness property set to anything other than “No Line”.
If not checked, when a column is not wide enough to fully display its text, the height of the row does not change, and the text is cut off.
Keep Line Breaks
This option is available if the ‘Wrap Cell Text’ box is checked, and it is used to preserve text formatting.
[Value Required] - Button
This button is used to launch the Value Required pop-up window, where available dashboard filters/substitution variables are listed. Refer to Value Required for more information.
Crosstab
This video provides an overview of the Crosstab visualization.
Example of Crosstab Visualization
The Crosstab visualization allows users to arrange row headings, column headings, and values using drag and drop, and the resulting arrangement is saved. This gives users the ability to change the edge position by moving or swapping.
NOTE: The ability to arrange the edges by drag and drop with the arrangement saved is also available in Crosstab view.
Hover over the row heading, column heading, or value until the Crosshair icon (
) appears. Click and drag the item on the Crosstab visualization and drop it in the desired location. Possible locations are highlighted in blue as the item is dragged across the visualization to help guide the user to a final position.
The following screenshots show examples of arranging a row heading, column heading, and value.
Re-arranging Row Heading in Crosstab Visualization
Re-arranging Column Heading in Crosstab Visualization
Re-arranging Value in Crosstab Visualization
Part 1: Plotting Fields
Refer to Part 1: Plotting Fields for details about dragging and dropping fields into this tab. This tab is used to select the fields to be used as the row headings, column headings, and values.
NOTE: For subtotal/total to be available, all “Values” fields must use one of the following aggregations: Count, Count (Distinct), or Sum.
The screenshot above shows an example of displaying available properties on the selected fields.
Part 2: Sort & Filter
Refer to Part 2: Sort & Filter for details about filtering.
Part 3: Format
Refer to Part 3: Format for details about formatting.
Tables at Runtime
Column Menu
To open a table column drop-down menu, hover your mouse over the column header and click on the down arrow.
Hierarchy
The Hierarchy menu option allows users to insert columns belonging to the same hierarchy to the table. A column’s hierarchy list can contain fields from folders that are not the column’s folder. A field hierarchy contains a root field and all its descendants.
NOTE: The hierarchies are defined in BI Catalog Builder, where the user can group any data type fields (same data type or mix) to form a hierarchy. For more information, please refer to the Editing Fields section of the BI Catalog Builder guide.
Data source security is applied to the fields in the Hierarchy submenu and the only fields displayed will be the ones to which the user has access.
When the user selects a field from the submenu, if the field was not previously selected for the table, it will be automatically added to the table.
A column’s field hierarchy list is created as follows:
-
Start with all fields in the column’s field hierarchy.
-
Remove all fields that the user does not have authorization to use.
-
Remove all fields that were already added to the table as columns.
-
Remove all fields whose folders are not selected in the Fields pane.
-
Sort Ascending
Each column can be sorted in ascending order using this menu option.
Sort Descending
Each column can be sorted in descending order using this menu option.
Remove Column
Use this option to remove a column from a table visualization.
Reordering Columns
As shown above, a column can be drag and dropped to a new position by clicking and holding the column’s header and then dragging it to its new position.
To reorder the position of a column, hover over its column header to reveal the Move cursor, then click and hold it and drag it to the desired position. Save the dashboard to save the column’s new position.
Resizing Columns
To resize a column, hover the cursor over the column’s border to reveal the Resize cursor (shown above), then click and hold the border and drag it to the desired position. Save the dashboard to save the columns new width.
Query By Example (QBE)
As shown above, a filter icon is available on the top-right of a table’s header to Query by Example.
When the icon is clicked, a row of fields appears above the table column headers for the user to enter QBE expressions for each of the table’s columns. Details on comparison and logical operators available to use in the expression fields for String, Number and Date columns are provided below.
Each table column can be queried for blank fields by clicking on the arrow button beside each expression field and selecting “is blank” or “is not blank” from the drop-down menu.
NOTE: The Value Case Hint field property, which is specified in BI Catalog Builder, can be used to generate SQL queries that may benefit from database column indexes. This field property is used to set whether values in a field are always uppercase or lowercase, which allows for a faster case-sensitive search in dashboard filters. However, users should only set this field property if the letter case of the column value is known, as inaccurate search results may be generated if set incorrectly. Refer to the BI Catalog Builder guide for more information.
After entering the QBE expressions, users can execute the query by pressing Enter on their keyboard. Invalid QBE expressions will not be included in the query.
NOTE: QBE is also applied to PDF and Excel exports.
Comparison Operators
The following comparison operators are supported:
|
Operator |
Description |
|---|---|
|
> |
Greater than |
|
< |
Less than |
|
>= |
Greater than or equal to |
|
<= |
Less than or equal to |
|
= |
Equal to |
|
!= |
Not equal to |
|
<> |
Not equal to |
Logical Operators
The logical operators “and” and “or” are supported. They are case-insensitive. Only one logical operator is allowed per QBE expression.
Columns
-
STRING: For a STRING column, a QBE value without a comparison operator prefix is implicitly assigned the startsWith operator.
-
NUMBER: For a NUMBER column, a QBE value without a comparison operator prefix is implicitly assigned the = operator.
-
DATE: For a DATE column:
-
The From date is implicitly assigned the >= operator.
-
The To date is implicitly assigned the <= operator.
-
When From date is empty, it is defaulted to the minimum date value for the column.
-
When To date is empty, it is defaulted to the maximum date value for the column.
-
Export Table to PDF
As shown above, a PDF icon is available on the top-right of a table’s header to export the table’s data to a PDF file.
Below is a screenshot of the above table’s exported data in a PDF file:
The following options are available for the Export to PDF functionality on the Format tab of the Visualization Properties control, as shown below. They are used to set the PDF’s page orientation and size, to upload a logo, and to specify subtitle and footer text for the PDF.
Example of the export to PDF functionality on the Format tab of the Table Visualization Properties control
As shown in the above screenshot, a logo can be displayed in the upper left corner of the exported PDF. If the Logo property is left empty on Format tab of the Visualization Properties control, the logo defaults from the Dashboard Logo Image Path field on the Logo Path tab of the System Options screen (standard path: System > Setup > System Options – Logo Path tab), as shown below.
NOTE: The URL to the image must be publicly accessible (no authentication) and start with https. The following image formats are supported: JPG, BMP, GIF, WBMP, and PNG. If the image is not supported (not shown) in the table PDF export, please use the PNG format.
Otherwise, the user can upload a substitute logo in the Logo property on the Format tab of the Visualization Properties control which will display on the PDF instead. To clear a substitute logo from the Logo property on the Format tab, use the Delete or Backspace keyboard key.
Custom subtitle and footer text may contain any of the available substitution value expressions. Click on the arrow next to the Subtitle or Footer Text fields in the Format tab to launch a pop-up window where substitution values can be selected. Click on the help icon in the pop-up window to show all substitution value expressions, including substitution variable value expressions, filter value expressions, and pre-defined variable value expressions.
For a list of available pre-defined substitution value expressions, please refer to the table in the following
The format for a page filter value expression is: #{@visualizationId}. The visualization Id can be obtained from the page filter’s About menu option (e.g. #{@e0}), as shown in the screenshot above.
An example of the resulting exported PDF is shown in the screenshot below.
Notes for Exporting Data to PDF
The following items are exported from a table to a PDF file:
-
Column sorts (ascending/descending)
-
Group Sorts
-
Group Sort Lines, including color & thickness
-
Subtotals, including font & background color
-
Grand totals
Latest data is exported, and latest page filter values are applied.
URLs in columns become hyperlinks 
in PDF.
When necessary, an export is queued, with a message stating position in queue as follows, "Please Wait. You are currently in position X."
Maximum amount of time a PDF export can run to completion is 5 minutes; after 5 minutes, a timeout message is displayed, asking the user to narrow the data selection; an incomplete PDF file is still downloaded, with the prefix "Incomplete".
Characters from most writing systems (languages) are supported; if unknown characters are found during the export process, the unknown characters are replaced with the character
.
Export Table to Excel
As shown above, an Excel icon is available on the top-right of a table’s header to export the table’s data to an Excel spreadsheet. Below is a screenshot of the above table’s exported data in Excel:
Notes for Exporting Data to Excel
CMiC BI is a data charting/analyzing tool that presents data in BI dashboards and is not designed to be used as a data export tool. While Excel export functionality exists in BI, it is only meant to assist users with formatting and printing dashboard data. For assistance with tasks specifically related to exporting customer data, we recommend that users contact CMiC Support.
The following items are exported from a table to an Excel file:
- Wrap Cell Text option
- Column sorts (ascending/descending)
- Group Sorts
- Group Sort Lines, including color & thickness (line thickness of 1 becomes an Excel thin line; line thickness more than 1 becomes an Excel thick line)
- Subtotals, including font & background color
- Grand totals
- Pivot items
Latest data is exported, and latest page filter values are applied.
URLs in columns become hyperlinks in Excel:
When necessary, an export is queued, with a message stating position in queue as follows, "Please Wait. You are currently in position X."
Maximum amount of time an Excel export can run to completion is 5 minutes; after 5 minutes, a timeout message is displayed, asking the user to narrow the data selection; an incomplete Excel file is still downloaded, with the prefix "Incomplete".
When a large amount of data is exported, the actual number of exported records varies depending on the system load at the time of export.
If export cannot be completed due to a timeout, try limiting the amount of exported data using filters.
Characters from most writing systems (languages) are supported; if unknown characters are found during the export process, the unknown characters are replaced with the character.
Shift Column Controls
The Shift Column Control icons (
) are used as an alternative to a horizontal scrollbar. These icons are a helpful feature in situations where there is no horizontal scrollbar (e.g., viewing a table visualization using an iOS browser).
Create User-Defined Log Based on Table
This feature is used to create a user-defined log in CMiC Field based on a table visualization, and it is only available to users with the system privilege BILOGCRT (CMIC BI: Allows the user to create User-Defined Logs). Also, this option is only available in BI Dashboard Builder – it is not available in dashboards at runtime.
NOTE: Only users with the system privilege will be able to see the Create User-Defined Log menu item; otherwise, the menu item will be hidden.
To create a User-Defined Log screen in CMiC Field, after a table is created, select the Create User-Defined Log option from the table’s Visualization Menu (shown above).
To have the User-Defined Log filtered for particular values, use Pivot Items, as detailed under : Part 3: Format.
If page filters, visual filters or ‘Current Project Only’ checkbox filter are defined in Dashboard Builder properties, these properties will be replicated in CMiC Field’s user defined logs.
Grant Security Access to New Log’s Treeview Link
When the new User-Defined Log is created, the user that created it is automatically granted rights to the Treeview link for the new log.
Security access to the log’s Treeview link for other users or for CMiC Field security roles is granted using the “Assign Menu Items” option in the Role Maintenance or User Access screen, as shown below.
After security access to the new User-Defined Log has been granted, the log may need to be manually added to the CMiC Field Treeview menu using the Menu Maintenance screen. Locate and drag the custom log from the Default menu into the Current Project Menu and click on [Save].
The new User-Defined Log will now be available under the User-Defined Logs Treeview node in CMiC Field:
Edit User-Defined Log
After the User-Defined Log has been created, like any other User-Defined Log, it can be edited using the User-Defined Log Types and User-Defined Logs screens:
Create User-Defined Folder Based on Table
Visualization drop-down menu
This feature is used to create a user-defined folder in the dashboard’s data source based on a table visualization/BI query table.
It is only available to users with the following system privileges:
-
BISECMNG (CMIC BI: Allows the user to modify security)
-
BICTLGBLDR (CMIC BI: Allows the user to access BI Catalog Builder)
-
BIUDFOLDER (CMIC BI: Allows the user to create User-Defined Folders)
This option is only available in BI Dashboard Builder and it is not available in dashboards at runtime.
NOTE: Only users with the required system privileges will be able to see the Create User-Defined Folder menu item; otherwise, the menu item will not be available.
Pop-up window launched from Create User-Defined Folder
Remove Group Sorts (Keep Subtotals)
This option is used to remove all Group Sorts set up for the table but to keep Subtotals for Group Sorts.
The Group Sort option for tables is discussed: Part 1: Plotting Fields.
Remove All Group Sorts
This option is used to remove all Group Sorts (including Subtotals for Group Sorts) set up for the table.
The Group Sort option for tables is discussed in the following subsection for tables: Part 1: Plotting Fields.
Sort Ascending
This option aligns the table’s sort order to the order of the columns and sorts the table’s columns in ascending order. The sort method selected, ascending in this case, is reflected in the Sort & Filters tab of the Visualization Properties control, as indicated by the up-arrow ( 
).
Example of Visualization Properties control showing ascending sort method
Sort Descending
This option aligns the table’s sort order to the order of the columns and sorts the table’s columns in descending order. The sort method selected, descending in this case, is reflected in Sort & Filters tab of the Visualization Properties control, as indicated by the down-arrow ( 
).
Example of Visualization Properties control showing descending sort method
Create BI Springboards
As explained in the overview, BI Dashboard Builder can also be used to create BI Springboards, which are dashboards with springboard (link-out) capabilities, as tables provide the ability to springboard to record screens to view further details, edit data, and to perform actions like replying to RFIs, approving requests, changing voucher compliance statuses, and updating equipment details, as shown below:
For a table to have this springboard ability, it must use a folder of the Advanced Analytics type, which is a special view that contains a column with links to each record’s screen. Some such views (i.e., folders) are included in the provided default data source (CMiC Default Data Source), and they are identified in BI Catalog Builder by the “BI” suffix in a view’s (i.e., folder’s) name, as shown below:
Date Fields
Date Calculated Fields
As shown below, every field in a folder that is of the date data type has three corresponding calculated fields: one for the year component, one for the month component, and one for the day component. As shown below, these corresponding calculated fields are added to the end of a folder’s list of fields:
These date calculated fields can be added to visualizations in order to sort or group their data by year, month or day.
Date and Number Formats
For fields of the date and number data type, the format to use to display their dates is set in BI Catalog Builder using the Default Format property in the Field Properties section of the Edit Data Source option, as shown below.
Example of Edit Data Source option of BI Catalog Builder tool for date type fields
Example of Edit Data Source option of BI Catalog Builder tool for number type fields
For table visualizations, the user can also modify the format to use for date and number type fields in Dashboard Builder, as shown in the screenshot below.
Example of modifying format for Number type field for a Table in Dashboard Builder
NOTE: For charts, number and date type fields are formatted as per the default format specified in Catalog Builder.
Using Calculated Fields to Launch Programs Using Targets
In BI Catalog Builder, a calculated field can be created to launch a program from a field using targets. For example, this feature allows users to create launch fields to open up other programs in their table visualizations.
For more information about launching a program in a table visualization using a target, please refer to the BI Catalog Builder guide.
To learn more about targets functionality, please refer to Making Dashboards Accessible via Console , as well as the Treeview Builder guide.
Conditional Formatting
This functionality is used to select the text color and background color of a table cell using a format expression.
The Conditional Formatting property is located in the Plotting Fields tab of the Table's Visualization Properties.
NOTE: Conditional text colors and background colors can also be applied to Excel/PDF exports.
Applying Text Colors
In this example, conditional formatting is applied to text color. Currently, the Conditional Formatting property is turned off, as shown in the screenshot above.
When the Conditional Formatting property is turned on for the selected column, in this case for the Invoice Amt column, a Format Expression link appears. Clicking on the link opens the Format Expression pop-up window, where format expressions are entered. The format expression supports comparison and logical operators.
Clicking the Help icon (
) displays a list of helpful examples for the switch function, which has been enhanced for format expressions only.
After the expression is entered in the Format Expression pop-up window and the window is closed, the conditional formatting is applied to the selected column.
Applying Background Colors
In this example, conditional formatting is applied to background color. Currently, the Conditional Formatting property is turned off, as shown in the screenshot above.
Similar to the last section where text color was applied, when the Conditional Formatting property is turned on for the selected column, in this case for the Invoice Amt column, a Format Expression link appears. Clicking on the link opens the Format Expression pop-up window, where format expressions are entered.
After the expression is entered in the Format Expression pop-up window and the window is closed, the conditional formatting is applied to the selected column.
Applying Date Functions
Date functions are also available in conditional formatting expressions.
For example:
date.today
Returns the current server date without the time portion.
Syntax:
date.today()
date.truncate
Returns a date truncated to a specified unit. The unit is optional. Without providing a unit, the function returns a date truncated to the day portion of <dateTime>. In other words, it returns <dateTime> without the time portion.
Syntax:
date.truncate([<unit>], <dateTime>)
<unit> supported values are “year” and “month”. “year” truncates <dateTime> to the first day of the year. “month” truncates <dateTime> to the first day of the month.
<dateTime> can be a date field reference or function that returns a date.
NOTE: This feature is also applied to the conditional formatting’s advance option for most charts.
Supported Comparison and Logical Operators
The format expression supports the following comparison operators:
| Comparison Operators | Description | Examples |
|---|---|---|
| == | Equal to | [Account Type] == “Equity” |
| != | Not equal to | [Account Type] != “Equity” |
| > | Greater than | [Invoice Date] > date(2017,12,17) |
| < | Less than | [Account Type] < “Equity” |
| >= | Greater than or equal to | [Invoice Amount] >= 1000 |
| <= | Less than or equal to | [Invoice Amount] <= 1000 |
The format expression also supports the following logical operators:
| Logical Operators | Description | Examples |
|---|---|---|
| && | The double ampersand performs a logical AND operation. It combines two expressions and returns true only if both expressions are true. | ([Account Type] == “Equity”) && ([Invoice Amount] <= 1000 |
| || | The double pipe performs a logical OR operation. It combines two expressions and returns true if one of the expressions is true. | ([Account Type] == “Equity”) || ([Invoice Amount] <= 1000) |
Switch Function
NOTE: The switch function is enhanced (for format expressions only).
The switch function converts an <expression>’s values against a list of values and returns one of multiple results.
Syntax:
switch(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
<expression can be a <field reference>, true, false
<value> can be an expression: a field reference, a number, a string, a URL, a date, a function (including another switch function call), combinations of arithmetic/comparison/logical expressions.
<result> can be a field reference, a number, a string, URL or a date.
<else> can be a field reference, a number, a string, URL or a date.
The data type of the <expression> must be the same as the comparison <value>’s data type.
Examples:
color = switch([Balance Amt], blank(),"#ff0000", 0, "#0000ff", "#00ff00")
color = switch(true, [Account Type] == "Assets", "#00ff00")
color = switch(true, [Account Type] <= "Expense", "#00ff00")
color = switch(true, trim([Account Name]) == "", "#ff0000")
color = switch(true, [Balance Amt] == blank(),"#ff0000", [Balance Amt] > 0, "#00ff00")
color = switch(true, (-15 *([Amount 1]-[Amount 2])/[Amount 3] + 29.76) > 0, "#00ff00", [Amount 4] == blank(), "#ff0000")
color = switch(true, [Account Type] != "Expense","blue", "#00ff7f", [Balance Amt] >= 50, "#b8860b", "#ed6647")
color = switch(true, ([Balance Amt] >= 0) || ([Account Type] == "Assets") || ([Account Type] == "Revenues"), "#00ff00")
color = switch(true, [Balance Amt] == blank(),"#ff0000", ([Balance Amt] > 0) && ([Account Type] == "Assets"), "#00ff00")
color = switch(true, [Balance Amt] == 0,"#ff0000", ([Balance Amt] + [Credit Amt]) > 0 && ([Account Type] == "Assets"), "green")
color = switch(true, [Balance Amt] < 0,"#ff0000", ([Balance Amt] + switch([Account Type],"Assets", 100, 0)) <= 10000, "#00ff00", "#0000ff")
color = switch(true, [Invoice Date] < date(2017,12,23), "blue", ([Invoice Date] >= date(2017,12,23) ) && ([Invoice Date] <= date(2020,12,27) ), "#ffff00"