IMOS - Adding Custom Columns and Formatting Columns

In the Report Designer, you can add a custom column to a report and specify its heading and value. Custom columns are added to the Columns pane and can be rearranged, grouped, sorted, and filtered like any other column. 

You can define or change column formatting for custom columns and existing columns. Formatting such as font attributes, field background, and alignment can be defined using a value, conditions, or a formula for an expression, similar to Excel.

For details and examples, see Report Designer Data Types and Expressions.pdf and Sample Custom Expressions.

Note: The Report Designer does not support exporting custom expressions at this time.

Adding a Custom Column

1. To create a custom column, click 

   

    in the Tables pane. The Properties pane takes its place; you can format the new column there.
   

   

2. Enter a Heading for the column.
3. Select a Type.
   • For Type Numeric or DateTime, select a Format.
4. Enter an Expression to define the value:
   1. Click in the field; the formula editor appears.
      

      

      
      
      Note: The formula editor is case sensitive. For example, VoyageStatus=="commenced" is different from VoyageStatus=="Commenced"
       
   2. Create a formula for the value, following the example formula. You can include:
      • Other custom columns
      • Selections from the Column, Color, and Font lists at the bottom of the formula box
        The Column list includes the following functions, which must be in UPPERCASE:
        • ABS(FieldID)
        • AVG(FieldID)
        • COALESCE(FieldID1, FieldID2, ...)
        • CONCAT(FieldID)
        • CONTAINS(FieldID, SearchString)
        • COUNT(FieldID)
        • COUNTDISTINCT(FieldID)
        • COUNTIF(FieldID, TestExpr)
        • DATE(Year, Month, Day)
        • DATERANGE(DateFieldID1, DateFieldID2)
        • DATETIME(Year, Month, Day, Hours, Minutes, Seconds)
        • DATETIME RANGE(DateFieldID1, DateFieldID2)
        • DAY(DateFieldID)
        • DISPLAYVALUE(EnumLiteral)
        • ENDSWITH(FieldID, SearchString)
        • HOUR(DateFieldID)
        • IF(Condition, true, false)
        • INDEX(FieldID, Num)
        • INLIST(Value, Target1, Target2, ...)
        • JOIN(“,”, FieldID)
        • JOINDISTINCT(",", FieldID)
        • LEN(FieldID)
        • MAX(FieldID)
        • MIN(FieldID)
        • MINUTE(DateFieldID)
        • MONTH(DateFieldID)
        • NOW()
        • NOWGMT()
        • ROWOFFSET(n, ORDERBY(FieldA, FieldB)).FieldID
        • SECOND(DateFieldID)
        • SPLIT(FieldID, Delimiter, Index)
        • SQRT(FieldID)
        • STARTSWITH(FieldID, SearchString)
        • SUBSTRING(FieldID, StartIndex, length(optional))
        • SUM(FieldID)
        • TIME(Hours (0-23), Minutes (0-59), Seconds (optional, 0-59), Milliseconds (optional, 0-999)))
        • TODAY()
        • TODAYGMT()
        • TOINT(FieldID)
        • TOLOCALESTRING(FieldID)
        • TOSTRING(FieldID)
        • YEAR(DateFieldID)

Formatting a Column

1. To format a column, open the Properties pane:
   • For a custom column, follow the steps above.
   • For an existing column, click it in the Columns pane.
2. Edit any of the following fields:
   • Bold, Italics:
     1. Click in the field; the formula editor appears.
     2. Type one of the following:
        • "Y"
        • "N" 
     3. Either click outside the box or press Enter.
   • Font:
     1. Click in the field; the formula editor appears.
     2. Select a value from the Font list at the bottom of the box.
     3. Click in the box.
     4. Either click outside the box or press Enter.
   • Font Color, Field Background:
     1. Click in the field; the formula editor appears.
     2. Select a value from the Color list at the bottom of the box.
     3. Click in the box.
     4. Either click outside the box or press Enter.
   • Alignment, Width, Sort:
     1. To change the default value, click to select it, then press Delete.
     2. Select a different value from the list.
   • Header Group: Group consecutive columns together under a single heading.
     

     

     • For each field to be grouped together, select the field in the Columns pane and add the same Header Group name.
   • Eval. After Filters: Y = Calculate the value for a column with a formula only after filters have been applied.
     Example:
     • Load Port Days is a custom column that will be populated from the Port Days column.
     • Port Function is filtered on Load.
     • Load Port Days column has Eval. After Filters enabled.
     • IMOS will apply the filter before calculating the value of the Load Port Days column.
   • Hidden: Y = This column will be hidden in the report output.
   • Hide if Empty: Y = If there are no values, this column will be hidden in the report output.
   • Is Multi-Line: Y = When the column's width is not sufficient to display the text in the column, it will wrap onto multiple lines.
   • Format: Enter the number or datetime format pattern. For examples on how these patterns work, see Formatting Codes for Numbers and Dates.