How to Create a Pivot Table Widget – MSPbots.ai

This article shows how to create a pivot table widget.

An example of a pivot table widget:

pivot table.png

How to Create a Pivot Widget

Navigate to Widgets on the MSPbots app menu.
On the Widgets tab, click My Widgets.
Next, click the New Widget button.
Select the Pivot Table widget type on the New Widget window.
When the Widget Builder window opens, click Info on the menu.
Provide the following under Basic Setting.
- Name - Give the widget a name.
- Description - Give the widget a short description.
- Role - Select or edit the roles that will have access to the widget. The roles selected by default are Admin, User, and Dashboard Only.
  
  If you want to create or edit a Pivot Table widget using the New Widget Builder mode, refer to How to Create a Pivot Table Widget Using the New Widget Builder.
Click Apply when done.
Next, go to Dataset on the menu.
Under Data Source, click the button.
Select New Layer when the Add New Layer window opens. This action will bring up the Dataset window.
On the Dataset window,
1. Select the Dataset containing the information you want to use. You can also give a name for the Datasource (Optional).
2. For Columns Display,
  1. Go to the Column Name dropdown and select the field you want to show in the drill-through.
    
    Click to add more rows.
  2. Give the column an Alias and select a Business Type for it. Do this for every row that you create.
  3. If the selected Business Type is Number, read the article Options for Custom Formatting in Your Widgets and Dashboards for help with formatting numbers in your widgets.
3. For Filter,
  1. Click the button and choose between Add Condition and Add Group.
  2. Select a filter from the first dropdown list.
  3. Next, set a condition or logic for each field using the next two dropdowns. Refer to the article What Filter Conditions and Formats are Available for Creating Widgets for the descriptions of each condition.
  4. Repeat Steps 11.3.1 to 11.3.3 to add more conditions.
  5. When you have added all the conditions you need, select the logical operator AND or OR for the filter group.
4. (Optional) For Measure,
  1. Click the button to show the Measure window.
  2. On the Measure window, select a Summary Type.
  3. Select a Field to use in the computation.
  4. Enter an Alias for the measure.
  5. Select a Format Type.
  6. Then click Add.
5. (Optional) For Dimensions,
  1. Click the button.
  2. Click on Fields and select an option for grouping the data.
  3. Next, enter an Alias for the measure.
  4. Select a Format Type.
  5. Then click Add.
6. (Optional) For DrillThrough Order By,
  1. Click the button.
  2. Select a parameter to automatically sort data according to your selection.
7. (Optional) For Order By,
  1. Click the button.
  2. Select a parameter to sort the data based on your selected field.
8. (Optional) Next, select a Row Limit to set a limit to the number of rows on your widget.
9. (Optional) Tick the checkbox for Remove duplicate data to delete duplicate data based on your set conditions.
10. (Optional) Then select an option from the Materialized dropdown to materialize data based on the actual conditions.
11. Click Save to keep the configuration.
  
  Other optional settings:
  The following options are also available on the Dataset tab. When applied, these two settings reflect on the pivot table instead of the drill-down.
  * Order By - Sorts the data based on the selected field.
  * Row Limit - Sets a limit to the number of rows that appear on the widget.
  After adjusting the settings for these, click Apply to save the setup.
Move on to Config on the menu.

Do the following:
1. Widget Type - You can switch this widget to different widget types. If you want to learn more, please refer to How to Switch Widget Types.
2. Show Grouping Bar - This is the toggle for hiding or showing the row fields, column fields, value fields, and filter fields in the pivot table.
  - When turned on, it will display the row fields, column fields, value fields, and filter fields.
  - When turned off, it will not display them.
3. Collapse By Default
  - When the toggle is turned on, collapsible column fields will automatically be collapsed when you open the widget or the dashboard/report that contains the widget, and only non-collapsible column fields will be displayed.
  - When the toggle is turned off, all column fields in the table will automatically expand when you open the widget or the dashboard/report that contains the widget.
  - The Collapse By Default toggle is turned off by default.
4. For Empty Cells Show Zero
  - When this toggle is turned on, empty values will be displayed as 0.
  - When it is turned off, empty values will be displayed as blank.
5. Click on the located at the top right corner of the Preview page to access the Field List pop-up.
  1. The fields listed under "All Fields" are sourced from the fields set in the Data Source. Please check the fields you want to display in the pivot table under "All Fields". These fields will be automatically displayed in the Filters, Columns, Rows, or Values section respectively.
  2. If you want to adjust the position of each field, drag the in front of each field. Place the mouse cursor over it until the cursor icon changes to Drag, then drag the field to the area where you want it to be displayed.
  3. Click Apply.
6. In the action column above the pivot table, you can edit the format of the table.
  1. Clicking on the Show table button displays the pivot table in table style.
  2. Clicking on to switch the widget to another type.
    1. Column
    2. Bar
    3. Line
    4. Area
    5. Scatter
    6. Polar
    7. If none of the above chart types meet your needs, please click "More" and set the chart type you need in the "Chart Type" within the Chart Type Settings.
    8. Multiple Axis: If selected, you can configure the "Multiple Axis Mode" with options such as "Stacked" or "Single".
    9. Show Legand: If checked, the pivot table is displayed as a line chart. Clicking on "Show table" button allows you to return to table style.
  3. Click on to export the table as PDF, Excel or CSV format.
  4. Click on to set sub totals.
    1. Show sub totals
    2. Do not show sub totals
    3. Show sub totals rows only
    4. Show sub totals columns only
  5. Click on to set grand totals.
    1. Show grand totals
    2. Do not show grand totals
    3. Show grand totals rows only
    4. Show grand totals columns only
  6. Click on to set formatting.
    1. Number Formatting
    2. Conditional Formatting
Finally, click Apply. This will show a preview of the pivot table.

Related to

How to Create a Pivot Widget

Related articles