Advanced Setup - Widgets

This page covers how to use functions to build widgets with advanced features in the widget builder stages and the chart designer .

Advanced Setup for Bar Graphs and Line Charts

Setting up Constant Lines in Bar Graphs and Line Charts

You can set up horizontal constant lines that run across your chart. They allow you to see at a glance which measurements exceed or fall short of specified levels.

1. In the Chart Properties panel, expand the valueAxis property. Each integer under the valueAxis property represents a different value axis. Usually, there will be only one value axis (denoted by a ‘0’.) per chart.

2. Expand the option of the value axis to which you want to add constant lines.

3. Expand the constantLines property. Each integer under the constantLines property represents a constant line. There is one set up by default (‘0’).

4. For each constant line that you want to add, click the copy button of the constantLines property and then click ‘Yes’ when prompted to confirm. New constant lines are listed under the default. Each constant line is a copy of the default and is denoted by its own integer.

5. For each constant line, expand its option and specify where on the value axis to draw the line, its color, and the line width.

6. When you’re finished, click ‘Save’. The chart is redrawn to display the constant lines.

Setting Up Overlapping Bar Charts

To turn a bar chart into an overlapping bar chart, i.e., where two or more metrics for the same argument (e.g., projected earnings vs actual earnings for 2017) are shown side by side, add another series to the widget through the chart properties panel. When setting up an overlapping bar chart, it’s best not to put more than three series side-by-side.

Prerequisite

To create an overlapping bar chart, go through the widget data preparation steps and then create a bar chart. The widget’s data set must have other fields that can be added as additional series.

Steps

1. In the Chart Properties panel, expand the series property and then expand the ‘0’ option representing the series whose values are on the bar chart.

2. For each series whose measurements you want put beside the measurements of the first series, click the copy button of the series property and then click ‘Yes’ when prompted to confirm. New series are listed under the default. Each series is a copy of the default and is denoted by its own integer.

4. For each series, do the following:

   1. In the valueField dropdown, select the field that has the values for this series.

   2. In the name property textbox, type the label that will appear for this series on the widget.

   3. Click the ‘+’ of the series and add the color property.

   4. In the color property color picker, select the color to represent this series.

5. When you are finished, click ‘Save’. The chart is redrawn as an overlapping bar chart.

Setting Up Stacked Bar Charts

You can turn a bar chart into a stacked bar chart or a full stacked bar chart by adding new series and changing the chart type.

Prerequisite

To create a stacked bar or full stacked bar chart, go through the widget data preparation steps and then create a bar chart. The widget’s data set must have other fields that can be added as additional series.

Steps

1. In the Chart Properties panel, expand the series property and then expand the ‘0’ option representing the series whose values are on the bar chart.

2. Change the series’s type value to the preferred chart type, either ‘stackedbar’ or ‘fullstackedbar’.

3. For each series whose measurements you want to stack on top of the measurements of the first series, click the copy button of the series property and then click ‘Yes’ when prompted to confirm. New series are listed under the default. Each series is a copy of the default and is denoted by its own integer.

4. For each series, do the following:

   1. In the valueField dropdown, select the field that has the values for this series.

   2. In the name property textbox, type the label that will appear for this series on the widget.

   3. Click the ‘+’ of the series and add the color property.

   4. In the color property color picker, select the color to represent this series.

5. When you are finished, click ‘Save’. The chart is redrawn as a stacked bar chart.

Adding a ‘Total Value’ Label to Stacked Bar Charts

You can add a ‘Total Value’ label to a stacked bar chart. The label displays the total of all bar values in a stack. This allows you to easily compare your total value to each individual value within the stack.

Pre-requisites

To add a ‘Total Value’ label to your stacked bar chart, you should have a stacked bar chart widget already set up. Before making any changes to your live widget, make a copy of it.

Steps

1. In the Chart Properties panel, remove and re-add the following chart properties:

   1. commonSeriesSettings > label > customizeTextWidgetFunction

   2. seriesTemplate > customizeSeriesWidgetFunction

2. Next to customizeTextWidgetFunction, select SERIES LABEL FORMATTER.

3. Next to customizeSeriesWidgetFunction, select DISPLAY TOTAL FOR DYNAMIC SERIES.

4. When you’re finished, click save.

Combining Bar and Line Charts into a single Visualization – Dual Axis

You can create a dual axis chart that shows two different series of data superimposed on top of each other.

Pre-requisites

• A bar chart widget already set up to show at least one of the series you want in the dual axis chart.

• Have planned which series are going to be shown on the value axes and argument axis.

In this procedure, you set up a left axis and a right axis, give to them titles and identifying names, and map them to the series whose values they show.

Creating Dual Axis Chart

1. Go to Widget Builder.

2. From the Widgets List, select the bar chart widget that has at least one of the series that you want to show in the dual axis widget.

3. Copy the bar chart widget. The copied widget will be modified to become the dual axis widget.

4. Select the copied widget and click ‘Design Chart’.

5. In the Chart Properties panel, open the valueAxis property, and copy axis ‘0’. A copy of axis ‘0’, axis ‘1’, is created.

6. Make sure that both axis 0 and 1 are set up with the following child properties. If they don’t have a required child property, add it.:

ValueAxis Properties for Dual Axis

Property	Description	What You Enter…
name	Used to map axis to series.	A unique name for each axis.
position	Controls whether the value axis is the right vertical axis or the left vertical axis.	To the axis you want displayed on the right side of the widget, enter the value ‘right’ and to the other ‘left’.
title	Used to set up the axis’s title. NOTE: Don’t use the label property for this purpose. ‘Label’ controls the text displayed on chart data.	Under title, add another child property, ‘text’. For the text value, enter the title you want displayed for the axis on the widget.

7. Make sure that each of the two series is set up with the following child properties:

Series Properties for Dual Axis

Property	What You Enter…
axis	Name value of the axis that applies to this series.
type	Chart type to use to display the series data. (e.g., Bar, spline, etc.).

8. Click ‘Apply’. You have created a dual axis widget.

Splitting Dual Axis Widget into Two Panes

You can split the two series of a dual axis chart so that each series’s data is displayed in its own pane. The widget shows both panes one on top of the other.

1. Go to Widget Builder.

2. From the Widgets List, select the dual axis widget whose series you want to split into two panes.

3. Click ‘Design Chart’.

4. In the Chart Properties panel, open the panes property, and copy pane ‘0’. A copy of pane ‘0’, pane ‘1’, is created.

5. Make sure that each of the two panes is set up with the following child properties,

Series Properties for Dual Axis

Property	What You Enter…
name	unique name value of the pane.

6. Make sure that both axis 0 and 1 are set up with a ‘pane’ child property. Pane value should be the name value of the pane where the axis is to appear.

7. Make sure that both series 0 and 1 are set up with a ‘pane’ child property. Pane value should be the name value of the pane where the series values are to appear.

8. Click ‘Apply’. The dual axis widget has been split into two panes.

Setting Up Axis Titles

1. In the Chart Properties panel,expand the valueAxis property by clicking it.

2. Add the ‘title’ property.

3. Add the desired title’s child properties: font, margin, and text.

4. Set up the child properties as needed:

   Property	Description	What You Enter…
   widths 30	30	30
   font	Specifies font options for an axis title.	The font’s child properties and their value: color, family, opacity, size, weight.
   margin	Specifies a margin for an axis title in pixels.	The margin’s number.
   text	Specifies the text for the value axis title.	The title’s name.

5. Click ‘Save’.

Formatting Appearance

How do I set up a Circular Gauge to be a half circle?

By default, a circular gauge’s values are spread over an arc that is almost a full circle so that it resembles a car’s speedometer. If you want to modify the arc so that it’s a half circle, do the following.

1. In the Chart Properties panel, click the top ‘+’ button, which is aligned with the Chart Properties label. A modal form appears that shows the top-level properties that can be added.

2. Click ‘geometry’ and then click ‘OK’. A geometry property is added to the list of properties.

3. Click the ‘+’ button of the geometry property. A modal form appears that shows the properties that can be added as children of this property. Two properties are available: endAngle and startAngle.

4. Hold down the CTRL button and then click both properties.

5. Click ‘OK’. startAngle and endAngle are added as child properties of geometry.

6. In startAngle’s textbox, enter ‘180’.

7. In endAngle’s textbox, enter ‘0’.

8. Click Save. The gauge is redrawn so that the arc is a half-circle.

Setting Up Color Ranges for Gauges

By default, a gauge’s scale is a drab gray line. To apply colors to ranges of values along the scale, you have to create ‘ranges’ and then apply a color to each range.

1. In the Chart Properties panel, expand the rangeContainer property and then expand its child property, ranges. Under the ranges property, there’s a default range, which is denoted by a ‘0’. Each range is denoted by an integer with ‘0’ denoting the first range.

2. For each range that you want to apply to the scale, click the copy button of the ranges property and then click ‘Yes’ when prompted to confirm. New ranges are listed under the default range. Each range is a copy of the default range and is denoted by its own integer.

3. For each range, expand its properties and modify it so that it has the start point, end point, and distinctive color that you want to be displayed.

4. When you’re finished, click ‘Save’. The gauge is redrawn to display the specified color ranges.

Adding Colors to Grid Widgets

This section goes over the grid properties you can use to customize the background or the text color of a grid’s elements like headers, cells, rows, etc.

Here are the colors and their background and text color class names.

The below lists in bold each grid property in which you can use a color class, a brief description of the property, and what you do to apply the desired color on the grid’s element.

Grid Properties and Color Classes

Property	Description	What You Do
columnDefs > headerClass	Class to use for the header cell. Can be string, array of strings, or function.	Enter the desired color class value.
columnDefs > cellClass	Class to use for all a column’s cell. Can be string, array of strings, or function that returns a string or array of strings.	Enter the desired color class value.
columnDefs > cellClassWidgetFunction	Specifies which custom function to apply to a column’s cells.	Select the custom function that uses the color class.
rowClass	Class to use for all a grid’s rows. Can be string, array of strings, or function that returns a string or array of strings.	Enter the desired color class value.
getRowClassWidgetFunction	Specifies which custom function to apply to each row individually.	Select the custom function that uses the color class.

Setting up Card Widget – Best way to display a single value

You can set up a Card widget so that it displays a single data point, like an important metric, on your dashboard.

A Card Widget is a good choice when there’s one important value that doesn’t have an upper or lower limit. For values that have an expected range, creating a gauge could be a good option.

Steps

1. Go to Widget Builder.

2. From the Widgets List, select the card widget.

3. Click ‘Design Chart’.

4. Select which value field to use using the dropdown.

5. Click ‘Done.’ You have a created a card widget.

Card Widget Chart Properties

The Card Widget’s chart properties allow customization over background color, font, and format (fixed, decimal, percentage, dollar, thousand or million).

Card Widget Chart Properties

Chart Property	Description	Example
BackgroundColor	Background color for value. Default: #fff
Font -> Color	Font color of the value. Default: #535357
Font -> Family	Font family used to display value Default: Roboto
Font -> Size	Font size of the value. Works only if ‘responsiveSize’ is false. Default: 65px
Font -> Weight	Font weight of the value. Default: Bold
Format -> Type	Value format (e.g., decimal, dollars, percentage):Default: fixed
Format -> Precision	Number of decimal places to show. Will not work with Type = ‘Fixed’ Default: 2
Format -> showSeparators	Whether to put commas between every three digits.Default: true
hideValue	Lets you display only a label. Default: false
Label -> Color	Font color of the label. Default: #A0A0A4
Label -> Family	Font family of the label.Default: Roboto
Label -> Position	Position of the label: (Bottom or Top) Default: bottom
Label -> Size	Font size of the label. Only works if ‘responsiveSize’ is false. Default: 65px
Label -> Text	Label text.
Label -> Weight	Font weight of the label. Default: normal
responsiveSize	If set to true, Rebus dynamically sizes the content to fit in the widget Default: true
Theme	A theme defines a background and a font color. Selecting a theme will override ‘backgroundColor’ , ‘font.color’ and ‘label.color’ Default: None
valueField	Which field to display as the value. This is set during the widget creation.

Parameters, Grouping, and Record Selection

Grouping Rows by Default in Grid Widgets

You can set up a grid widget so that it groups rows by default. I.e., rows that share a common value are collected under a summary row that you can expand if you want to see the underlying details. You can set up multiple levels of grouping so that the widget’s rows are organized like an expandable, multi-level menu. For columns that hold numeric data, you can set up aggregation functions that show you the sum of all that column’s values for each grouping.

In the example below, the following grid widget is changed so that it’s pre-grouped first by Client ID and then by Warehouse ID. Before grouping:

After grouping:

Prerequisites

• Decide how the widget’s rows are going to be grouped by default. If you want the rows grouped by multiple columns, decide in what order the groupings should happen.

• Identify those columns holding numeric data that you want to be summed at the group level.

• Decide whether you want the widget’s groups to be expanded or closed by default.

Steps

Setting Up Default Grouping

1. Go to Widget Builder.

2. From the Widgets List, select the widget that you want to modify.

3. Click ‘Design Chart’.

4. 4. Open columnDefs property. Each number represents a column with 0 being the first column and each incrementation of 1 representing the next column to the left.

5. For each column by which you want rows grouped by default, add two child properties:

Grouping Child Properties

Property	Description	What You Enter…
rowGroup	Flags columns for whose values you want numeric values in the rows grouped by default.	Make sure the checkbox is checked.
rowGroupIndex	Determines the order of the grouping. Columns with a lower index value are grouped before columns with higher value.	Enter the index number. Start with ‘0’ for the grouping to be done first.

Setting up Default Expanded Property

1. Under ‘Chart Properties’, add a new top level property, ‘groupDefaultExpanded’.

2. As the property’s value, enter the number of the level at which you want the expandable menu to be open by default. E.g., If you enter ‘2’, two levels will be by default expanded when a user opens the widget.

Adding Group Level Sums

In each column for whose numeric value you want to show a group level sum, apply an aggregation function. Do the following:

1. Add child property ‘aggFunc’.

2. In the property’s field, enter ‘sum’.

3. Click ‘Save’.

Result

You have created grid widget whose rows are grouped by default into an expandable menu and that shows group level sums of numeric values.

Adding Parameters through the Display Field and Group Stages

In the widget builder, you can use the param() function to add parameters through the code editor on the Display Field and Group stages. The param() function creates a parameter for the widget and specifies its default value:

divide(param(PARAM1,33),77)

CODE BLOCK

divide(param(PARAM1,33),77)

In the example above, a parameter, PARAM1, is created. The divide function populates the widget with the value of PARAM1 divided by a constant, 77. PARAM1’s default value is ‘33’, but a user can change PARAM1’s value by opening the widget’s Properties menu. To continue with this example, if you wanted to make both the numerator and the denominator parameters, you could use the following function:

divide(param(PARAM1,33),param(PARAM2,77))

In the example above, both PARAM1 and PARAM2 could be changed through the widget’s Properties menu.

Selecting Records based on Patterns in String Values

The regexp() function lets you create a widget that selects records that have a string of characters that match a specified ‘regular expression’ or ‘regex’ pattern:

FIELD_NAME = regexp(expression(REGEX_PATTERN),options(REGEX_FUNCTION_OPTIONS))

For each record, the regexp() function evaluates the specified field’s value (a.k.a. the input string) and selects the record if the value matches the regex pattern. You can also specify an options argument that provides options about how an input string is evaluated.

Example of Regexp() function:

JC_DESCRIPTION=regexp(expression(^LOAD\*),options(i))

The above function selects all records where JC_DESCRIPTION starts with ‘LOAD’ and doesn’t consider the case of the letters. The function’s case insensitivity is set by the i options() argument.

The regexp() function can be used when adding a custom expression at the Filter stage Filter stage.

Note

When setting up a regexp() function, make sure that the field that it’s evaluating is a string field. If it isn’t, you’ll get an error message when you try to save the stage.

Overview of Regex Patterns

A Regex pattern comprises a string and special characters that specify a pattern of characters and how you want input strings to be evaluated. At its simplest, a Regex pattern is a string. Records that have values that contain that string are selected. Unless specified otherwise, the search will be case sensitive. I.e., ‘a’ and ‘A’ are considered to be separate characters.

Regex Basic Examples

Regex Pattern	Matching Values
abc	abcdefg
234	123456

Quantifiers

In a Regex pattern, a quantifier lets you specify how many instances of a character in the input string constitute a match. Put a quantifier after the character in a Regex pattern:

Regex Quantifier

Quantifier	Matching Values…
*	Have this character zero or more times.
+	Have this character one or more times.
?	Have this character only one time or not at all.

Quantifier Examples

Regex Quantifier Examples

Regex Pattern	Example Matching Values
23* 4	1245, 12345, 123345, 1233345
23+4	12345, 123345, 1233345
23?4	1245, 12345

Special Characters

A Regex pattern can have ‘special characters’ that each represent a set of matchable characters in the input string.

Regex Special Characters

Special Character	Represents…
. (dot)	Any single character.
\n	A new line character.
\t	A tab.
\d	A digit [0-9].
\D	A non-digit.
\w	An alphanumeric character.
\W	A non-alphanumeric character.
\s	A whitespace character.
\S	A non-whitespace character.

Special Character Examples

Regex Special Character Examples

Regex Pattern	Example Matching Values
1.3	123, 1z3, 1A3
1.* 3	13, 123, 1abcdefg3
\d\d	01, 03, 88
\w+@\w +	a@a , email@domain.com

Character Classes

A character class is a set of characters between square brackets. Like special characters, a character class in a Regex pattern represents a set of matchable characters in the input string.

Regex Character Classes

Character Class	Represents…
[abc]	Either ‘a’ or ‘b’ or ‘c’.
[^abc]	Any character other than ‘a’, ‘b’, or ‘c’.
[a-z]	Any character between ‘a’ and ‘z’.

Anchors

Anchors let you specify whether the beginning or the end of an input string is evaluated against the Regex pattern.

Regex Anchors

Anchor	The Input String is a Match…
^	When the start of the string matches the Regex pattern.
$	When the end of the string matches the Regex pattern.

Anchor Examples

Regex Anchor Examples

Regex Pattern	Example Matching Values	Example Non-Matching Values
^1.*3$	13, 123, 1abcdefg3	x13, 123x, x1abcdefg3x
^\d\d	01abc	a01abc
\d\d$	xyz01	xyz01x

Assertions

Regex Assertions

Assertion	Description	Example	Input string will be a match if it…
?=	Lookahead	a(?=b)	Has an ‘a’ followed by a ‘b’.
?!	Negative Lookahead	a(?!b)	Has an ‘a’ not followed by a ‘b’.

Example of Negative Lookahead

The negative lookahead assertion can be the best choice if you want to look at all the records in a set but exclude some whose values match a certain pattern. For example, the following regexp() retrieves all order records except those where the CUSTOMER_NAME starts with ‘AMAZON’:

CUSTOMER_NAME=regexp(expression(^(?!AMAZON)?),options(i))

Regex Function Options

The regexp() function can also pass an options() argument, which supplements the usual Regex pattern options and gives you greater control over how an input string is evaluated. To not use any of these options, pass a blank options() inside regexp():

JC_DESCRIPTION=regexp(expression(^LOAD?),options())

Regex Function Options

Option	Description
i	Insensitivity to case when matching letters. E.g., No distinction made between ‘A’ and ‘a’, which would usually be treated as different characters.
m	When evaluating strings with multiline values, anchors (i.e. ^ for the start, $ for the end) will match at the beginning or end of each line . Without this option, these anchors match at the beginning or end of the entire string.
x	Ignore all white space characters in a pattern unless escaped (/) or included in a character class. Also ignore all hash/pound characters (#) and all characters enclosed between them, which lets you include comments in complicated patterns.
s	Let the dot character (.) match all characters including newline characters.

Formatting Data on the Widget

Formatting Numbers in Grids

You can have grid widgets present numbers according to a specified format (e.g., rounded to two decimal places or shown as dollars). To do this, go to the widget’s Chart Properties and apply a valueFormatterWidgetFunction to the column whose values you want formatted.

1. Go to Administration -> Widget Administration -> Widget Builder.

2. If the widget you want to edit is still ‘Active’ (denoted by a green circle), make sure that it’s deactivated.

3. Select the grid widget whose data you want to format and then click ‘Design Chart’ at the bottom of the screen. You are at the widget’s Chart Properties. The widget’s data is displayed as a grid.

4. Note the column whose data you want to change and the numerical order in which it’s placed relative to other columns. The first column on the left is considered to be column ‘0’, the next one to the right is column ‘1’, and so on.

5. In the Chart Properties menu, click the ‘columnDefs’ property. The property expands and a set of numbers appears. Each number represents a column.

6. Select the number representing the column whose data you want to format. The column’s customizable properties appear.

7. If there is no ‘valueFormatterWidgetFunction’, manually add it.

8. From the valueFormatterWidgetFunction’s dropdown, select the formatting function that you want to apply:

Note

Your environment may not have all the formatting functions shown in the screenshot above. If you would like a custom formatting function added to your Rebus environment, please contact Rebus Customer Support.

9. Click ‘Save’. The widget is saved. The data in the updated column is re-formatted according to the logic of the specified formatting function.

Adding Hyperlinks to Grid Widgets

In the widget builder, you can set up a grid widget to display a hyperlink.

1. In the Selecting the Display Field stage, set up a calculated field using the ‘concatenate’ function and enter the link that you want to add.

2. Finish configuring your widget and make sure you select ‘Grid’ in the chart wizard.

3. Go to the widget’s chart properties panel and expand the ‘columnsDefs’ property. Each column of the grid is identified by an integer with ‘0’ representing the first column. These child properties help configure the way the columns in the chart look and behave.

4. Expand the drop-down menu for the column containing the calculated field that you created by clicking on the arrow next to its number.

5. Click the ‘+’ sign next to the column’s number.

6. Select the ‘cellRenderedWidgetFunction’ property and click ‘ok’.

7. From the drop-down menu, select one of the two following functions:

Functions to display Hyperlinks

Function	When to Select It
hyperlinkCell	For URLs that include https://
hyperlinkCellWithoutSecureProtocol	For URLs that don’t include https://

8. Click ‘Save’.

Result: Your widget will show a hyperlink that will open in a new tab.

Replacing Database Values with Descriptive Labels

In the widget builder, you can create a widget that shows descriptive labels instead of the raw, single-character or abbreviated values stored in the WMS: Create a calculated field in which an equals function and a condition function turn values into descriptive labels in the widget. The equals function evaluates whether the field’s value matches a specific value you want to replace with a descriptive label. If it’s a match, the function will return a value of ‘TRUE’. Otherwise it will return ‘FALSE’. The equals function passes either TRUE or FALSE to a condition function, which shows a specified label for TRUE instances and takes another action for FALSE instances. For a description of the equals function and the condition function see Widget Building Function Reference.

Example: Replacing a Single Value with a Descriptive Label

In the following example, we’ll set up a calculated field that shows “PICKED” whenever the PICK_STATUS value is “P”.

1. In the Selecting the Display Fields stage, set up a calculated field called PICK_STATUS_DESCRIPTION that has a condition function that determines what the field displays. Nested in the condition function is an equals function that returns TRUE when the field’s value matches the value, “P”, that you want to be replaced by a descriptive label.:

   condition(equals(PICK_STATUS,"P"), "PICKED", "NOT PICKED")
   

The condition function ensures that PICK_STATUS_DESCRIPTION shows “PICKED” whenever the PICK_STATUS value is “P”. Otherwise, it shows “NOT PICKED”; That’s what the condition function is configured to return if the nested equals function returns FALSE.

Example: Replacing Multiple Values with Descriptive Labels

In the previous example, the calculated field shows one of two descriptive labels, “PICKED” or “NOT PICKED” depending on whether the PICK_STATUS value is “P”. If you want to replace a field’s entire range of values with descriptive labels, set up a series of nested condition functions, one for each value that you want to be replaced. In the following example, we’ll be setting up a series of nested condition functions to replace all PICK_STATUS values with descriptive labels:

Example - Replacing Values with Descriptive Labels

PICK STATUS value	Descriptive Label
P	PICKED
R	READY
S	STAGING

–

1. In the Selecting the Display Fields stage, set up a calculated field called PICK_STATUS_DESCRIPTION that has condition functions that determine what the field displays for each possible value that PICK_STATUS can have.

The condition functions are nested within each other so that the field value is evaluated against each condition function in sequence. If a condition function evaluates to FALSE, the value will then be evaluated against the next condition function until one evaluates to TRUE or all the options are exhausted and “NOT RECOGNIZED” has to be returned.

condition(equals(PICK_STATUS,"P"), "PICKED", condition(equals(PICK_STATUS,"R"), "READY", condition(equals(PICK_STATUS,"S"), "STAGING", "NOT RECOGNIZED")))

1. In the example above, PICK_STATUS is first evaluated to see if it’s equal to “P”.

• If TRUE, the condition function will return “PICKED”, which will then be shown in PICK_STATUS_DESCRIPTION. Otherwise,

2. PICK_STATUS will be evaluated to see if it’s “R”.

• If TRUE, the condition function will return “READY”. Otherwise,

3. PICK_STATUS will be evaluated to see if it’s “S”.

• If TRUE, the condition function will return “STAGING”. Otherwise, the function returns “NOT RECOGNIZED” because PICK_STATUS must have a value that’s outside what’s expected for PICK_STATUS.

Removing Grid Pagination

You can remove pagination in grids so that they appear as one page on a dashboard. To remove grid pagination, follow these steps:

1. In the Chart Properties panel, clear the Pagination property’s checkbox.

2. Click ‘Save’.

You have removed pagination in the desired grid.

Renaming Grid Columns

You can rename a grid’s column headers to a desired value. To do so, follow these steps:

1. In the Chart Properties panel, expand the columnDefs property by clicking it.

2. Click the number corresponding to the column you want to rename its header (i.e., 0, 1, 2, etc.).

3. Next to headerName, enter the new column name.

4. Click ‘Save’.

You have renamed the desired grid’s column headers.

Dates and Times

Setting up Widgets to be Filtered with Fiscal Date Ranges

This procedure goes over how to set up widgets to filter them with relative fiscal date ranges.

The following table outlines the scenarios in which you can set widgets with relative fiscal date ranges and the corresponding results:

Fiscal Date Scenarios

Scenario	Result
Creating or editing widgets in the Widget Builder.	The relative fiscal date range becomes the widget’s default date filter setting.
Filtering widgets on a dashboard.	You can turn on fiscal dates for individual widgets on your own dashboards, which only affects those widgets.
Applying the date global filter to widgets on dashboard.	All widgets on the dashboard are filtered with the selected relative fiscal date range.

Fiscal date ranges are applied through the Relative Range date filter.

Setting Up Fiscal dates as the default in the Widget Builder

At the filter stage, click the ‘Fiscal’ button. The relative range options list changes to the list of relative fiscal date ranges, from which you can select any of the available options.

Widgets on a Dashboard

From the Widget Properties form, just turn on the ‘Fiscal Date’ toggle. The relative range options list changes to the list of relative fiscal date ranges, from which you can select any of the available options.

Date Global Filter

From the Global Filters panel, turn on the ‘Fiscal Date’ toggle. The relative range options list changes to the list of relative fiscal date ranges, from which you can select any of the available options.

Converting Dates into Other Formats

You can have widgets display dates in formats other than UTC. By default, timestamps, which contain date and time of day information for events, are stored and formatted in UTC format. To have a widget display a date in a custom format, in the Selecting the Display Fields stage, set up a calculated field that has the following characteristics:

• Has a substring function for each type of date information (e.g., year, month, or day) that you want to extact from the UTC timestamp.

• Has a concatenate function within which the substring functions are nested and arranged in the desired order.

In the example below, we’re using substring functions nested within a concatenate function to create a calculated field that expresses the ORDER_ADDED_DATE field in UK date format (dd-mm-yyyy):

concatenate(substring(dayofmonth(ORDER_ADDED_DATE),0,2),"-",substring(month(ORDER_ADDED_DATE),0,2),"-",substring(year(ORDER_ADDED_DATE),0,4))

Date Sorting in Grid Widgets

If you’ve re-formatted dates as strings and now the dates aren’t sorted in the proper order in the widget, do the following to enforce the proper sort:

1. Open the widget’s Chart Properties.

2. Open the columnDefs properties and then the properties of the date column.

3. Add a pivotComparatorWidgetFunction.

4. In the function’s dropdown, select ‘GRID PIVOT SORTATION MM-DD-YYYY’.

Subtracting Weekends From Date Range Calculation

In the Display Fields stage, to create a calculated field that subtracts weekends from a date range calculation, use the below expression. In this example, we calculate the age of an open order between Sysdate(0) (i.e., today) and the ORDER_ADDED_DATE. You can replace these date fields with the ones you’re using.

subtract(subtract(sysdate(0),ORDER_ADDED_DATE),multiply(add(multiply(floor(divide(add(floor(divide(subtract
    (sysdate(0),ORDER_ADDED_DATE),86400000)),1),7)),2),condition(greaterThan(add(absolute(subtract(dayOfWeek(sysdate(0))
    ,dayOfWeek(ORDER_ADDED_DATE))),dayOfWeek(ORDER_ADDED_DATE)),7),1,0),condition(equals(dayOfWeek(ORDER_ADDED_DATE),1),1,0)
    ),86400000))

This expression returns a value in milliseconds. You can then convert this expression to hours, days, weeks, etc., by nesting it within a divide() function.

You can then continue reading for a detailed explanation of the following:

• Expression that subtracts weekend days from a date range calculation

• Expression that calculates the number of weekend days

Explanation of Expression that Subtracts Weekend Days from a Date Range Calculation

The expression subtracts the number of weekend days (in green) from the total date range (in yellow). The multiply() function (in pink) is used to convert the number of weekend days into milliseconds.

Explanation of Expression that Calculates Weekend Days

The expression to calculate the number of weekend days is the following:

add(multiply(floor(divide(add(floor(divide(subtract(sysdate(0),ORDER_ADDED_DATE),86400000)),1),7)),2),condition
    (greaterThan(add(absolute(subtract(dayOfWeek(sysdate(0)),dayOfWeek(ORDER_ADDED_DATE))),dayOfWeek(ORDER_ADDED_DATE)),7),1,0),
    condition(equals(dayOfWeek(ORDER_ADDED_DATE),1),1,0))

To explain this expression, we will break it down into three major steps:

1. Find the number of full weeks between the two dates.

2. Find the number of full days between the two dates.

3. Calculate the number of weekends based on:

   • The starting day of the week;

   • Ending day of the week;

   • And the number of weeks between the two dates.

Each of the above steps are explained in more detail below while keeping the same example in mind.

1. Find the number of full weeks between the two dates.

1. Get the number of days between the starting date and ending date:

   add(floor(divide(subtract(sysdate(0), ORDER_ADDED_DATE),86400000)),1)
   

   Take away points from this expression:

   • The subtract() function is used to calculate the date rage between the two dates.

   • The divide() function is used to convert this date range into days.

   • The floor() function is used to get the full number of days between the two dates.

   • The add() function is used to add one more day: if you are on the same day, you are still within one day difference between the two dates.

2. Take the value from above, and get the number of full weeks between the two dates. For simplicity, the above expression is referred as DAYS_BETWEEN.

   floor(divide(DAYS_BETWEEN,7))
   

   Take away points from this expression:

   • The divide() function is used to divide the number of days by 7 to get the number of weeks.

   • The floor() function returns the larget integer less than or equal to the result.

2. Find the number of full days between the two dates.

1. Get the day of the week for the two dates using the dayOfWeek() function:

   • The starting day of the week:

     dayOfWeek(ORDER_ADDED_DATE)
     

   • The ending day of the week:

     dayOfWeek(sysdate(0))
     

2. Get the remaining full days based on starting and ending days of the week calculated in the preivous step. For simplicity, these dates will be referred as START_DOW and END_DOW.

   add(absolute(subtract(END_DOW,START_DOW),START_DOW))
   

   Take away points from this expression:

   • In summary, this expression allows us to determine, based on the starting and ending days of the week, how many weekdays and weekends we can calculate in the date range.

   • The subtract() function is used to calculate the difference between the ending and starting days of the week.

   • The absolute() function is used to get the absolute value of the difference between the ending and starting days of the week.

   • The add() function is used to add up the absolute value and the starting day of the week

3. Calculate the number of weekends between the two dates.

By combining the above expressions, we can formulate the below expression to calculate the number of weekends between the dates. For simplicity, the expressions covered in the previous steps will be referred to as the following:

• Expression from step 1.2: WEEKS_BETWEEN

• Expression from step 2.1: START_DOW

• Expression from step 2.2: REMAINING_DAYS_BETWEEN

  Add(multiply(WEEKS_BETWEEN,2),condition(greaterThan(REMANING_DAYS_BETWEEN,7),1,0),condition(equals(START_DOW,1),1,0))
  

Take away points from the overall expression:

• The multiply() function is used to multiply the number of weeks by 2, since a full week contains two weekend days.

• The subexpression “condition(greaterThan(REMANING_DAYS_BETWEEN,7),1,0)” is used so that, if in addition to the full weeks, you are starting on Friday, Saturday, or Sunday, add 1 to account for starting from the day that falls into a weekend date.

• The subexpression “condition(equals(START_DOW,1),1,0))” is used so that, if you’re starting on a Sunday, add 1 to account for the weekend.

• Both the condition() functions are used for the following logic:

  • If the REMAINING_WEEK_DAYS between the two dates is greater than 7, the dates overlap through a weekend and therefore contain two weekend days.

  • If the REMAINING_WEEK_DAYS between the two dates is exactly 7, there is one weekend day between Monday and Saturday, or two weekend days if the ending day of the week is Sunday.

  • If the REMAINING_WEEK_DAYS between the two dates is less than 7, there are no weekend days between Monday and Saturday, or there is one weekend day if the ending day of the week is Sunday.

Building Widgets that Refer to the System Date

In the widget builder, you can build widgets that use the system date of the WMS as part of the query that populates the widget. Two functions, sysdate() and sysdatetz(), return the WMS’s current system date or a date in relation to the system date. You can use the output of these functions as arguments for other functions that take date arguments.

Sysdate () function

The sysdate() function returns the system date in Coordinated Universal Time (UTC) i.e., Greenwich Mean Time.

You can use it:

• With DATE fields ,

• To manipulate data from warehouses in different timezones,

• In the filter stage of the widget builder.

Note

Using the sysdate() function in other stages than the filter stage will display the result in Coordinated Universal Time.

Using Sysdate()

Date You Want Returned…	What you Enter as an Argument…	Example
Today’s date	0	sysdate(0)
A date in the past. E.g., yesterday	The number of days counting back into the past from today’s date expressed as a negative integer.	sysdate(-1)
A date in the future. E.g., the day after tomorrow	The number of days counting ahead into the future from today’s date expressed as a positive integer.	sysdate(2)

Example of Sysdate()

You can use the sysdate() function in custom expressions on the Filter stage to use your system’s date as an argument to select data. It must be entered in the format illustrated by the following example:

BATCH_ADDED_DATE>sysdate(-2)

The condition above selects all records whose BATCH_ADDED_DATE value is within the past two days counting back from today’s date.

Sysdatetz () function

The sysdatetz() function returns the system date in a specified time zone.

You can use it:

• With DATE_LOCAL fields,

• To manipulate data from facilities in the same timezone,

• To display data according to a specified timezone,

• In the display fields, group, or sort stages of the widget builder.

Using the sysdatetz() function in the filter stage will return incorrect values if your warehouses are in different timezones.

Sysdatetz() Function Examples

Date You Want Returned…	What you enter as an argument…	Example
Today’s Date	0 and the time zone	sysdatetz(0, America/New_York)
A date in the past. E.g., yesterday	The number of days counting back into the past from today’s date expressed as a negative integer and the time zone.	sysdatetz(-1, America/New_York)
A date in the future. E.g., the day after tomorrow	The number of days counting ahead into the future from today’s date expressed as a positive integer and the time zone.	sysdatetz(2, America/New_York)

The list of time zones that can be supplied as arguments for sysdatetz() come from the tz database, which is described on the following webpage: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

Examples of Sysdatetz()

The dateDifferenceInMs() function below would return the time difference in milliseconds between the REPORT_LOCAL_DATE of warehouses in the same timezone and today’s date:

dateDifferenceInMs(sysdatetz((0),AMERICA_NY), REPORT_LOCAL_DATE)

Totals and Counts

Getting a Daily Total of Values

In the widget builder, you can set up a widget that shows you a daily total of values. To get a daily breakdown of a specific field’s number value, you can use the substring() function to create a calculated field that shows just the first ten characters (i.e., the day-month-year portion) of a date’s timestamp, and then group the data by that calculated field:

1. Go to Administration -> Widget Administration -> Widget Builder, click ‘New’ and set up the Filling in the Basics stage.

2. In the Filtering Data stage, make sure that one of the display fields is a date field with a timestamp.

3. In the Selecting the Display Fields stage, set up a calculated field that uses the substring function to get the first 10 characters of the date field value. In the data set, each record’s calculated field shows the date of the day taken from the record’s timestamp. In the example below, we’re using the substring function to create a calculated field that has only the day, month, and year values of the PICKED_DATE field:

   substring(PICKED_DATE,0,10)
   

4. In the Grouping the Data stage, group the records by the calculated field showing the day-month-year. Add another calculated field that uses the sum() function to return the sum of all the number values in the specified field. In the example below, we are showing the pick quantity per day:

   sum(PICK_QUANTITY)
   

The data set now has a row for each day and each row shows that day’s sum for the specified field.

Getting an Hourly Total of Values

In the widget builder, the hour() function and the sum() function let you set up a widget that shows how a field’s values break down per hour of the day. The hour() function extracts the hours from a date time stamp. Below are the general tips to setting up such a widget:

1. Go to Administration -> Widget Administration -> Widget Builder, click ‘New’ and set up the Filling in the Basics stage.

2. In the Filtering Data stage, make sure that one of the display fields is a date field with a timestamp that includes an hour value.

3. In the Selecting the Display Fields stage, set up a calculated field that uses the hour function to return the hour value of the date field. In the data set, each record’s calculated field shows the hour of the day taken from the record’s timestamp.

   hour(PICKED_DATE)
   

4. In the Grouping the Data stage, group the records by the calculated field showing the hour. Add another calculated field that uses the sum function to return the sum of all the number values in the specified field.

The data set now has a row for each hour of the day and each row shows that hour’s sum for the specified field.

Getting a Count of Records that meet Specified Criteria

In the widget builder, you can set up a widget that shows how many records in the data set meet specified criteria. Do the following:

1. In the Selecting the Display Fields stage, set up a calculated field that has a condition function that returns a ‘1’ if the record meets specified criteria and ‘0’ otherwise.

2. Add a Grouping the Data stage.

3. In the Group Fields column, select the fields by which the rows will be grouped. A group will be created for each set of rows that have values in common across all selected fields. The count of records will be broken down for each group.

4. Create a calculated field that uses a sum () function to add the values of the condition function field that you set up in the Display Fields stage. This field has a ‘1’ for each record that meets the function’s criteria so adding them all together gives you a count of records that meet the criteria.

5. Click ‘Save’ and then click ‘Apply’. You see a count of all records in the data set that meet the criteria specified in the condition function broken down by the group you specifed in the Grouping the Data stage.

Example - Count of Records that meet Specified Criteria

In the following example, we’ll prepare the data for a widget that shows a count of all orders over the past week that had a SHIPPED_QUANTITY of over 100. The count will be broken down by date:

1. In the Selecting the Display Fields stage, set up a calculated field called ‘HIGH_ORDER_QTY’ that has a condition function that returns a ‘1’ if the order’s SHIPPED_QUANTITY is greater than ‘100’ and ‘0’ otherwise:

   condition(greaterThan(SHIPPED_QUANTITY, 100),1,0)
   

–

2. Add a Grouping the Data stage.

3. In the Group Fields column, click ‘DATE’.

4. Create a calculated field, COUNT_HIGH_ORDER_QUANTITY, that uses a sum () function to add the values of the HIGH_ORDER_QTY field that you set up in the Display Fields stage. This field has a ‘1’ for each record that meets the function’s criteria so adding them all together gives you a count of records where the SHIPPED_QUANTITY is greater than 100:

   sum(HIGH_ORDER_QTY)
   

5. Click ‘Save’ and then click ‘Apply’. You see a count of all records in the data set that meet the criteria specified in the condition function.

Labor Module Widgets - Advanced Setup

Manipulating Multi-Steps Activities In A Widget

You can set up a labor module widget that calculates only the unique values in multi-step activity and ignores the rows containing repeated values.

Each step of a multi-step activity has information about the whole activity. In the following example, both steps of the activity number 43 and 94 display the UNMEASURE_TIME_SECONDS for the entire activity:

Building a widget that sums the UNMEASURED_TIME_SECONDS for this data sample will result in inflated values, as the measure for the whole activity is added twice. The total UNMEASURE_TIME_SECONDS for the ICLEAN activity returns 5454 seconds instead of the expected 2727 seconds.

To prevent repeated values from being added twice, a grouping stage must be added after the filter stage and before the grouping stage in which the sum function is used. Before you set up a grouping stage, make sure that you know the unique value that identifies the activity.

1. In the widget builder, using non-guided mode, add a grouping stage and select the activity’s unique identifier as well as the fields you wish to display in your widget.

   The example below shows the same data sample after a grouping stage was added to eliminate duplicate values:

   

2. Add a second grouping stage that uses the aggregate function. The total UNMEASURE_TIME_SECONDS for the ICLEAN activity now returns the expected value of 2727 seconds.

Related Links

• Planning Custom Widgets - General Points

• Creating Widgets

• Favorite Widgets

• Managing Widgets

• Widget Properties