Background information
The new Widget Builder integrates all widget configuration options on a single page.
- By centralizing the management of all configuration options, users no longer need to switch between various pages, saving time and effort, enhancing user experience, improving work efficiency, and simplifying the operation process.
- This integrated design is easy to use and makes the configuration process more intuitive and efficient.
When to use the Column Chart widget
The Column Chart widget is typically used to display and compare data points in vertical columns. It is useful when you want to visually represent relationships between different categories or show trends over a period of time.
Required permissions
Only users with admin roles can access the New Widget Builder and edit the widget settings.
1. Access New Widget Builder
1.1 Create a new Column Chart widget using the New Widget Builder
- Navigate to Widgets on the MSPbots app menu and click New Widget.
- Choose the Column Chart widget type on the New Widget window.
-
1.2 Configure an existing widget using the New Widget Builder
- Navigate to Widgets on the MSPbots app menu.
- Search and click the widget to open it.
-
2. Add datasets
- In the Dataset tab, Click
.
- Select a dataset with the data you need, then click Add Selected.
Click Dataset name bar to show the fields in the dataset.
What else can you do in the Dataset section?
-
Search fields - Use keywords to search for the fields you need.
-
Order By - Sort all data from the added datasets based on the selected fields and sorting rules.
- Fill in the Field and Order By rules.
- Click Save.
- Fill in the Field and Order By rules.
-
Row Limit - Select an option to limit the rows of data for all added datasets.
-
Replace the dataset with another one you want.
- Hover over the name of the dataset.
- Click
, then select Select.
- Click your desired dataset and click Add Selected.Replace the dataset with one that matches the structure of the original dataset. For example, a dataset that only differs in field data values is essential to avoid error reports and widget malfunctions.
-
Rename, Clone, or Delete the dataset.
- Hover over the name of the dataset.
- Click
, then select Rename/Clone/Delete.
- When you hover over the dataset, it displays the original name of the dataset.
- If you have renamed the dataset, hovering over it displays both the original name and the name you renamed it to.
- When you hover over the dataset, it displays the original name of the dataset.
* When you add a dataset with fields next to the triangle icon, it indicates that the field is associated with other datasets.
* The fields in the associated datasets can be dragged or added to the widget configuration area for settings.
* If you configure fields from the associated dataset in Query, Drill Through, or Options, the Widget Builder mode will stay in the New Widget Builder mode and cannot switch back to the original mode. You can switch back to the original mode only after you clear the fields from the associated dataset.
3. Define Data Scope, choose fields, and create Query
1. Data Scope
This option is editable if the dataset has multiple layers. If the dataset has only one layer, skip this option and proceed to the next step.
For datasets with multiple layers, select the layer you want to configure.
How to check the number of layers in the dataset you are configuring?
- Search for the dataset you are configuring, and click it to open. For example, search ConnectWise Manage Time Entry - Layered dataset.
- Click
.
- Click the Please Select Layer dropdown list to see how many layers there are.
2. Field Chooser
- Click the Field Chooser bar to show the settings.
-
Field - Configure which fields are included in the widget.
- Drag the required fields from the Dataset column to the Field section.
Note: Ensure that all required fields from the Dataset column are dragged to the Field section. The fields available in Query will be the fields dragged to the Field section. - Click
to configure the following fields:
- Field - Select a field from the dropdown.
- Alias - Type an Alias.
-
Formula - Enter SQL to create a calculated value by using basic mathematical
operations on the dataset fields and any number. -
Format Type - Select a Format Type. For more detailed information, see Conditions and Formats Available for Creating Widgets.
- Text
- Number
- Date
- Date Time
- Yes/No
- User
- Hyperlinks
- Display Type - Select a Display Type. For more detailed information, see Conditions and Formats Available for Creating Widgets.
- Click Save.
- (Optional) When you set up Measure, click
or
to configure whether the fields in the Field Chooser are displayed in the Drill Through of the Measure, aside from the fields that are set as Measure.
- Drag the required fields from the Dataset column to the Field section.
-
Order By - Sort the data from drilling through a specific dataset based on the selected fields and sorting rules.
- Click
to add a field.
- Fill in the following fields:
- Field
- (Optional) Order By
- Ascending
- Descending
- Click Save.
- Click
3. Query
- Click the Query bar to show the settings.
- For Measure - Configure the fields to be calculated and the formula for calculating measures.
- Click
.
- Click
to configure the following fields:
- Field Name - Select a field from the dropdown.
- Alias - Type an Alias for the measure.
-
Formula - Enter SQL to create a calculated value by using basic mathematical
operations on the dataset fields and any number. -
Summarize Type - Select a Summary Type.
- sum
- count
- Average
- Maximum
- Minimum
- String_Aggregate
-
Format Type - Select a Format Type. For more detailed information, see Conditions and Formats Available for Creating Widgets.
- Text
- Number
- Date
- Date Time
- Yes/No
- User
- Display Type - Select a Display Type. For more detailed information, see Conditions and Formats Available for Creating Widgets.
- Remove duplicate data - If you want to remove duplicate data when calculating data, please check this option.
- Click Save.
- Click
- For Dimension - Configure the fields and format you want to be displayed as a group.
- Click
.
- Click
to configure the following fields:
- Field Name - Select a field from the dropdown.
- Alias -Give an Alias for the dimension.
-
Format Type - Select a Format Type. For more detailed information, see Conditions and Formats Available for Creating Widgets.
- Text
- Number
- Date
- Date Time
- Yes/No
- User
- Hyperlinks
- Display Type - Select a Display Type. For more detailed information, see Conditions and Formats Available for Creating Widgets.
- Click Save.
- Click
- For Filter - Add conditions for filtering data.
- Click
.
- Select a field you want to filter.
- Set a condition for the field using the subsequent two dropdowns.
For a guide on each option on the list, refer to the article Conditions and Formats Available for Creating Widgets.
- If you want to add multiple conditions, click + Condition to add.
- Select the logical operator AND or OR for the filter conditions.
- Select the logical operator AND or OR for the filter conditions.
- If you want to add multiple groups, click + Group to add.
- Select the logical operator AND or OR for the filter groups.
- Select the logical operator AND or OR for the filter groups.
- If you want to add multiple conditions, click + Condition to add.
-
-
-
-
-
-
Ignore Filter - When the Ignore Filter option is checked, the value of the condition will be grayed out. Once checked, you only need to fill in the field and query string. And only the slicer will be effective.
-
Ignore Filter - When the Ignore Filter option is checked, the value of the condition will be grayed out. Once checked, you only need to fill in the field and query string. And only the slicer will be effective.
-
-
- (Optional) SQL Preview - Click SQL Preview to check the filter settings by viewing the Filter Expression and Filter SQL.
- Click Save.
- Click
- For Order By - Sort the data of a specific layer in a certain dataset based on the selected fields and sorting rules.
- Click
.
- Configure the following fields:
- Field - Select a parameter to sort the data.
- Order By
- Ascending
- Descending
- Click Save.
- Click
-
Row Limit - Select an option to limit the data rows for a specific layer in a certain dataset.
-
Remove duplicate data - If you want to remove duplicate data, select this option.
-
Materialized - Select an option from the Materialized dropdown menu to materialize data based on the actual conditions.
4. Clear Query
When you want to clear all fields in Query and Drill Through at once, you can click Clear Query to avoid deleting them individually.
- Click Clear Query at the bottom of the page.
- Click Continue.
4. Set the Options for the widget
4.1 Chart Type
Switch widget types by clicking the icon of your desired widget type.
The available types for switching in New Widget Builder mode are:
- Card Number
- Grid
- Column Chart
- Line Chart
- Heatmap
- Pie Chart
- Progress Tracker
-
Pivot Table
4.2 Design
Click Design bar to show the settings. Fill in the following fields:
-
Chart Style - Select the Stacking Column, Column, or Bar from the dropdown list.
- Stacking Column
- Column
- Bar
- Stacking Column
-
Chart Title - Provide a title for the data being presented.
-
Dimension - Select a dimension as the x-axis (horizontal) of the bar chart.
Refer to the guide to set up the Dimension field.
- Drag fields from the Dataset to the Fields in the Field Chooser area.
- Set the Dimension in the Query. You can set one or more dimensions.
- Select a Dimension from the dropdown list as the X-axis (horizontal).
- Drag fields from the Dataset to the Fields in the Field Chooser area.
-
Measure - Select a measure as the y-axis (vertical) of the bar chart.
Refer to the guide to set up the Measure.
- Drag fields from the Dataset to the Fields in the Field Chooser area.
- Set the Measure in the Query. You can set one or more.
- Select a Measure from the dropdown list as the Y-axis (vertical).
- Drag fields from the Dataset to the Fields in the Field Chooser area.
-
Multiple Measure
- If not selected, the y-axis will have one measure.
- If selected, multiple measures will appear on the y-axis, such as the left y-axis and the right Y-axis.
- Measure Color - Select the color for the y-axis. When you set multiple measures, the colors of the measures are displayed sequentially.
- Column Limit - Set the number of measures to display. For example, setting it to 2 will display 2 measures.
-
Trend Line - Select the preferred trendline from the dropdown list, if necessary.
- linear
- exponential
- polynomial
-
Label Font Size - Set the font size for the label.
-
X-Axis Font Size - Set the font size for the x-axis.
-
Goal - Provide the preferred set goal, if necessary.
- Goal Color - Select a color for the goal.
-
Measure Line - Select the preferred measure-line from the dropdown list, if necessary.
- Dimension Group - Select the preferred dimension group from the dropdown list, if necessary.
- Dimension Name - Provide a name for the x-axis of the bar chart.
- Measure Name - Provide a name for the y-axis of the bar chart.
- Y-Axis Percentage - Selecting this option will display the percentage on the y-axis.
-
Right Y-Axis Coordinate - If selected, it will display the coordinates on the right x-axis.
- Right Y-Axis Measure - Select the measure for the right y-axis.
- Right Y-Axis Label Name - Enter the label name for the right y-axis.
- Right Y-Axis Percentage - Select to display the percentage on the right y-axis.
- X-Axis Name Location - Select Left, Center or Right as the location for the Dimension Name from the dropdown list, if necessary.
- X-Axis Name Gap - Provide the preferred number of gaps for Dimension Name, if necessary.
- X-Axis Name Rotate - Provide the preferred orientation for Dimension Name, if necessary.
- Y-Axis Name Location - Select Bottom, Middle or Top as the location for the Measure Name from the dropdown list, if necessary.
- Y-Axis NameGap - Provide the preferred number of gaps for Measure Name, if necessary.
- Y-Axis NameRotate - Provide the preferred orientation for Measure Name, if necessary.
- Show Label - The default is to show the labels. Remove the selection for the Show Label checkbox if you prefer not to show these.
- (Optional) Overlapping Columns/Overlapping Bars - Turn on this switch to make multiple columns/bars at the same X/Y-coordinate overlap with each other.
The overlapping column chart widget highlights overall trends, prominent outlier data, saves chart space, making it easier to display a large amount of data in a limited space, thus improving data visualization effectiveness.
- This switch still shows the column chart widget with only 1 dimension and 1 measure, but will not take effect if turned on.
- When the option Chart Style is selected as Column, the switch is named Overlapping Columns.
- When the option Chart Style is selected as Bar, the switch is named Overlapping Bars.
- This switch still shows the column chart widget with only 1 dimension and 1 measure, but will not take effect if turned on.
- Show Split Line - Select the checkbox for Split Line Show if you wish to show the split lines.
- (Optional) Stacked Statistics - Enable this switch to display the column chart using stacked statistics.
- (Optional) Stacked Statistics Sort - Select descending or ascending to sort the stacked statistics.
-
Label Format - You can configure the Label Format with the following tokens:
- {x_val}: The value of the x-axis.
- {x_val2}: The dimension value of the legend.
- {y_val}: The total value of the y-axis in the bar chart.
- {y_val2}: If a stacked bar chart is selected, this is the value of each part of the y-axis.
-
{measure_name}: The name of the y-axis.
-
Chart Margin (Grid Setting) -Turn on the switch, then you can customize and adjust the widget display size by entering pixel margins from the top, left, right, and bottom.
5. Conditional Formatting
If you want to change the text or background color when the fields meet specific conditions, or display as an icon, set a conditional formatting rule for your widget.
- Click Conditional Formatting bar to show the settings.
-
Click
.
-
Configure the following fields:
- Name - Set a name for this conditional formatting.
- Field - Select a field from the dropdown.
- Value - Set a condition that the field must meet, for example, the "dimension1" field != Custom.
-
Color Cell - Select the color or icon to be applied to a cell once the condition is met.
-
Background Color and Text Color
-
Icon
-
Background Color and Text Color
- Color Entire Row - Select the Background Color and Text Color to be applied to an entire row once the condition is met.
- Click Save.
6. Save your settings
-
Click Save in the upper-right corner.
-
When Apply Success appears, it means the save is successful.
New widgets are at the bottom of dashboards, scorecards, and reports. When you save a new widget, the screen will automatically scroll to show the new widget.
You can edit the dashboard design by modifying the widget's placement in the dashboard.
What other actions are available from the widget's screen in the new Widget Builder?
Switching to the original Widget Builder
If you want to switch back to the original Widget Builder page, click to open the Unsaved Changes pop-up window.
- If you do not want to save the settings in the New Widget Builder, click Leave without Saving.
- If you want to save the settings in the New Widget Builder, click Save Changes.
If you have clicked Save to keep the settings in the New Widget Builder, clicking will directly switch to the original Widget Builder.
Creating a bot from the widget
- Click
> Create Bot.
- For more details on creating a bot, see How to Create a Bot from a Widget in New Widget Builder mode.
Creating a filter for the widget
The filter is for secondary filtering of the data filtered in the Dataset.
- Click
> Filter.
- Select a field you want to filter.
- Set a condition for the field using the subsequent two dropdowns. For descriptions of each option, refer to What Filter Conditions and Formats are Available for Creating Widgets.
- If you want to add multiple filter conditions,
- Click the Add
icon and choose either Add Condition or Add Group.
- Add Condition - Add a filter condition.
-
Add Group - Add a condition group.
- Repeat steps 2 and 3.
- Click the Add
- Select the logical operator AND or OR for the conditions or filter groups.
- AND - Conditions within a group or groups are connected by an AND relationship, which requires that all conditions or groups must be fulfilled.
- OR - Conditions within a group or groups are connected by an OR relationship, which means that only one of the conditions or groups needs to be fulfilled.
- Click ok to keep your settings.
Cloning the widget
- Click the
button in the upper-right corner.
- Hover over Clone to and select from:
- Clone to "My Widgets"
- Clone to Existing Dashboard
- Clone to New Dashboard
- Follow the steps below:
- If you select Clone to "My Widgets",
- Fill in the following fields:
- Name
- Role
- Description
- Click Confirm to clone the widget as a new widget.
- Fill in the following fields:
- If you select Clone to Existing Dashboard,
- Select a existing dashabord from the list.
- Click Clone.
- The page will redirect to the editing mode of the existing dashboard. For more information on how to edit the dashboard, see How to Create and Use Dashboards in MSPbots.
You can also find the cloned widget in My Widgets category in Widgets.
- Select a existing dashabord from the list.
- If you select Clone to New Dashboard,
- Select Dashboard or Report.
- For more information on how to edit the dashboard or report, refer to the below articles.
- How to Create and Use Dashboards in MSPbots
-
How to Create and Use Reports in MSPbots
You can also find the cloned widget in My Widgets category in Widgets.
- Select Dashboard or Report.
- If you select Clone to "My Widgets",
Deleting the widget
- Click the
button, and then select Delete.
- Click Confirm.
Sharing the widget
- Click the Share
icon to copy the URL of the widget.
- The message Copy Success indicates that the copy was successful and you can share the URL with others who want to view the widget.
Adding the widget to My Favorites
Click the button in the upper-right corner to add the widget to My Favorites.
- When
changes to
, it means the addition was successful.
- After that, you can find the widget in the My Favorites tab.
- If you want to remove it from My Favorites, click
to change it to
.
Editing the basic settings of the widget
- Click the
button next to the widget name to edit the basic settings of the widget.
-
Fill in the following fields.
- Name
- Description
- More Info
- Tag
-
Role
- Click Save.
- When Apply Success appears, it means the save is successful.
Refreshing the widget preview panel
Click Refresh to manually refresh the widget preview panel.
For the Auto Refresh checkbox,
- The Auto Refresh checkbox is selected by default.
- When the Auto Refresh checkbox is selected, the widget preview panel automatically refreshes whenever you modify the widget's configuration.
- When you clear the Auto Refresh checkbox, the widget preview panel stops refreshing automatically and refreshes only when you manually click Refresh.
- Your setting for the Auto Refresh checkbox applies to all widgets.