Here you can find information about functionality Flexmonster Pivot offers to our end users:
- Display data in the pivot table to perform operations right on the grid or visualize data via pivot charts
- Use drag & drop Fields List interface to compose the report and add calculated measures
- Filter dataset, sort records or drill through to the non-aggregated data
- Use Toolbar to access features
Below you can see an animation showing all the main features and a detailed description of our UI functionality.
Flexmonster component offers lots of operations directly on the grid:
- Display on the grid as many hierarchies for rows, columns and report filters as you need.
- Drag the hierarchies between columns, rows and report filters.
- Show or hide totals and grand totals on the grid.
- In case there is more than one hierarchy in the rows or columns, expand or collapse the inner values by clicking the icon on the left of the cell.
- Drill up & down multi-level hierarchies by clicking the link on the right of the cell.
- Filter values by clicking on the hierarchy name.
- Sort values by using the controls on the right of each hierarchy member name or total cells.
- Click twice to drill through the cell to the non-aggregated data.
- Resize columns and rows by dragging the header boundaries; double-click on column or row headers to fit them to the default size.
- Select the cells on the grid, copy the content and paste it directly into, for example, Excel.
- Use the Toolbar to switch between compact, classic and flat view.
The Fields List can be used to define which data is shown on the grid or charts. This tool can be accessed by clicking the diagonal arrow in the upper right corner of the component or from the Toolbar.
On the left side of the Fields List, there are all hierarchies from the original data source. In case the list of hierarchies contains more than 50 elements, the search bar appears to facilitate the process of finding the necessary item. When the data source contains hierarchies with more than one level, ‘Expand All’ option is added to the top of the left part of the Fields List.
Drag & drop the hierarchies from the left part to Rows, Columns, Values or Report Filters boxes. The hierarchies can be dragged between these boxes. Each box can have as many hierarchies as needed. The order of the hierarchies inside the box can also be changed by dragging. Also, it is possible to select the hierarchy to the rows or columns starting from its specific level.
Values are typically placed in columns though it is possible to drag them to rows. Each value requires an aggregation function applied to it. Depending on the data source type, numeric hierarchies support from 7 to 13 types of aggregation. String hierarchies can also be selected for values, but only count or distinct count aggregations are available for them. By default, numeric hierarchies are aggregated using sum whereas the string hierarchies use count. Change the aggregation function by pressing the sigma icon and choosing the new one. Choose ‘Add calculated value’ at the top of the Fields List to add calculated value.
Flexmonster provides an interface for adding and editing calculated values. Click on ‘Add calculated value’ inside the Field List and see the pop-up window for adding the new value. Enter the name if necessary, compose the formula by clicking on operators and dragging the existing values to the ‘formula’ box. Select ‘Calculate individual values’ checkbox to calculate the formula using raw values. Click APPLY and the new value will be added to the Fields List. It will already be selected in the values box and ready to be displayed in the component.
All calculated values are gathered to one folder “Calculated Values” in All Fields section. Edit the calculated value by pressing the sigma icon next to the value name. The pop-up window for editing the value will be opened. Change the formula, update the name if necessary and click APPLY to save the changes. Remove the calculated value by clicking the recycle bin icon at the top of the editing pop-up.
Each hierarchy on the grid has a gear icon on the right. It provides access to the Filter with options for the selected hierarchy. The Filter can be accessed through the context menu of the hierarchies as well.
Under the hierarchy name, there are alphabetical and reverse alphabetical controls. They define the order of hierarchy members. For OLAP data sources, alphabetical order is the order which was defined inside the cube. The reverse alphabetical order is just the reversed order of the one that was defined inside the cube. For all other data sources, these controls apply actual alphabetical and reverse alphabetical orders accordingly. Deselect both controls and members will be displayed unsorted.
The next feature in the window is TOP 10. It performs filtering by values where only N top (or bottom) elements are displayed on the grid. Clicking on TOP 10 will open the submenu with additional options: the first field for entering N of the top or bottom values, top or bottom values selection and drop-down with available measures.
‘Select All’ checkbox helps to select or deselect all members at once. Also, members can be selected or deselected manually. A search bar helps to find the member by the name in case there are a lot of members in the hierarchy. After the manipulations press APPLY to see the changes on the grid.
A specific row or column on the grid can be sorted using sorting arrows. Find these arrows on the right of each hierarchy member name or total cells. Click for the first time to sort descending, for the second time to sort ascending. Also, these options are accessible through the context menu of each hierarchy member or total cell. Remove sorting via the context menu.
Each number on the grid or element on charts represents one or more records from data. Click twice to drill through the cell or graphic element to the non-aggregated data. A pop-up will be opened with the list of records from which the value is composed. At the top of this window, there are the details of the value: column name, row name, and the value itself.
The Fields List is available for this pop-up as well. Use it for adding or removing fields from the drill-through view without changing the slice for the grid. Another way to open the drill-through view is through the context menu of the element. Drill-through is not available for calculated values.
Reports based on CSV, JSON, and OCSV data offer editing feature. Change the raw data entries by clicking on them and entering new values.
Pivot charts provide the same analysis functionality as the grid:
- In the upper left corner choose active measure from the drop-down list. If ‘Multiple values’ option is selected, the drop-down list allows choosing of more than one measure.
- Filter values for hierarchies selected inside the Filter window.
- Click twice on the graphic elements to drill through and see the raw data.
- Use the Toolbar to easily switch between different chart types.
- If there are multiple hierarchies in the rows, expand or collapse the inner values by clicking the plus sign left to the hierarchy name on the x-axis. For multiple hierarchies in the columns use the plus sign on the legend.
- Drill up & down the hierarchy from the rows by clicking the plus sign left to the hierarchy name on the x-axis. To drill down the hierarchy from the columns click the plus sign on the legend.
The Toolbar is an extra free addition to the component. The Toolbar is the combination of the most commonly used API calls provided in the convenient for usage form:
- The first tab is called Connect and provides connection to various data sources. Depending on the edition there can be the following options: To local CSV, To local JSON, To remote CSV or To OLAP (XMLA).
- The second tab, Open, allows opening of the local or remote report.
- Save tab saves the report with current configurations to the local file system.
- Use Export tab to print current content or export it to HTML, CSV, Excel, Image, and PDF. When Drill Through pop-up is opened, Export tab gets the raw data.
- Grid tab switches the view to the grid.
- The submenu of Charts tab shows all kinds of pivot charts and has ‘Multiple values’ checkbox for enabling the selection of multiple measures for charts.
- Format tab provides two options: Format cells for number formatting and Conditional formatting.
- Click Options to see the pop-up with layout options such as grand totals and subtotals. Use Layout section to switch between compact, classic and flat view.
- Fields tab opens the Fields List.
- Click Fullscreen and switch to the fullscreen mode. Exit this mode by clicking Esc.
Number formatting can be applied through the Format cells pop-up. To open this pop-up click Format in the Toolbar and choose Format cells. Select from the drop-down list the value to which the format should be applied. Then you can modify any of the following properties: text align, thousand separator, decimal separator, decimal places, currency symbol, currency align, null value, format as percent.
Conditional formatting pop-up can be opened by clicking Format in the Toolbar and choosing Conditional formatting. In the pop-up click plus icon to add a new condition. You can apply the condition either to ‘All values’ or to the specific measure from the drop-down list. Select condition type and value, font family and size. Click the A-letter icon to replace the default font and background colors. Choose the new colors from the color picker or enter the hex codes. Click ‘Apply’ to add these colors to the condition. Check out the result inside the box on the right. To see the result on the grid click APPLY. Remove the condition by clicking the recycle bin icon on the right of the condition.
In case some feature is missing from the component, please check whether it is available for your data source in the technical specifications.