Data tables and grids have become staples in modern UI design. They allow our users to easily view large data sets, compare values, and make decisions based on empirical data. However, sometimes the data sets that we’re working with aren’t easily viewable in a standard table, or there may be instances where we want certain data excluded to make it easier to see correlations between data points. Thankfully, software engineers and computer programmers have developed a more advanced form of a data table that allows us to do just that: pivot tables.
A pivot table is a data table that aids users in tasks such as data discovery; it allows users to perform complex analytical calculations on the data set that you’ve loaded, determine what data you want to display, and determine the orientation of the data. This allows users to see trends commonly missed if you’re using a traditional data table.
Luckily, Wijmo has you covered with PivotGrid, the best Vue pivot table control available. It's simple to implement, easy to customize, and even has an extensive list of features that allow you to integrate it easily into your application.
In this article, we’ll be outlining how to do the following using Wijmo’s Vue pivot table, PivotGrid:
- Loading Data with the PivotEngine
- Setting Up the PivotPanel and PivotGrid
- Creating Custom Fields
- Using PivotCharts to Display Data in Chart Form
If you’d like to download the finished project to aid you in following along, you can find the source code for the application here. Now, let’s get to work!
Loading Data with the Pivot Engine
Before we implement Wijmo’s Vue pivot table, PivotGrid, we’ll need to load all of the required resources into the Vue application. The first thing that we’ll need to do is install the Wijmo UI component library, which we’ll do via NPM. Inside your Vue application, run the following command:
npm i @grapecity/wijmo.vue2.allThis will install the Wijmo library in your application. You may have noticed that we specify a vue package in the installation path; Wijmo supports Angular, React, and Vue along with its core JavaScript library, so we’re specifying the vue package so that we don’t install any unnecessary files. If you’re using Vue 3, don’t worry. This package also supports Vue 3; that’s what we’re using to build this application!
Next, we’ll need to include Wijmo’s CSS file and import the correct Wijmo modules into our application. Open up the App.vue file and include the following:
<script>
import '@grapecity/wijmo.styles/wijmo.css';
import { PivotEngine } from '@grapecity/wijmo.olap';
import * as wjcOlap from '@grapecity/wijmo.vue2.olap';
export default {
name: 'App',
components: {},
setup() {}
}
</script>Great! We’ve included all of the relevant Wijmo files we’ll need. One final thing that we’ll do is generate some data, which we’ll use to populate the PivotGrid. Modify the App.vue file as follows:
export default {
name: 'App',
components: {},
setup() {
var products = [
{ product: 'Wijmo', platform: 'Web' },
{ product: 'ActiveReports', platform: 'Desktop' },
{ product: 'ActiveReportsJS', platform: 'Web' },
{ product: 'ComponentOne', platform: 'Desktop' },
{ product: 'Spread', platform: 'Desktop' },
{ product: 'SpreadJS', platform: 'Web' },
{ product: 'GCDocs', platform: 'Desktop' }
];
var agents = [
{ agent: 'Ashlyn Dunlop', region: 'East' },
{ agent: 'Keith Vang', region: 'East' },
{ agent: 'Bobbi Rodrigues', region: 'West' },
{ agent: 'Charli Medina', region: 'West' },
{ agent: 'Kaitlin Salt', region: 'West' },
];
function randomInt(min, max) {
return Math.floor(Math.random() * (max - min + 1) + min);
}
function getOrdersList(count) {
var year = new Date().getFullYear(), data = [];
for(var i = 0; i < count; i++) {
let productIdx = randomInt(0, 6);
let agentIdx = randomInt(0, 4);
data.push({
orderId: randomInt(1, 10000),
platform: products[productIdx].platform,
product: products[productIdx].product,
agent: agents[agentIdx].agent,
region: agents[agentIdx].region,
date: new Date(year - randomInt(0, 2), randomInt(0, 11), randomInt(0, 27)),
sales: randomInt(10, 50),
downloads: randomInt(10, 200),
revenue: randomInt(500, 3500)
});
}
return data;
}
return { }
}
}The data here isn’t really relevant; it's just mock data to show the components' functionality, so I’m not going to spend much time on it. What’s important to know is that we’ve got a large set of data, which will be used to populate the components.
Alright, now, with the application set up, we can finally get into implementing Wijmo’s Vue pivot table, PivotGrid.
Modify your application’s App.vue file and add the following code:
export default {
name: 'App',
components: {},
setup() {
var ng = new PivotEngine({
itemsSource: getOrdersList(1000),
});
...
}
}As you can see, it's only a few lines of code. But we must explain what’s going on here. In the file, we’re initializing a Wijmo component called PivotEngine. The PivotEngine handles the management of the data, and without the PivotEngine Wijmo’s PivotGrid and PivotPanel controls wouldn’t function. You can think of the PivotEngine as the foundation of our pivot table; without it, the table would have no data to display to the users.
Along with initializing the Wijmo component, we’re also setting its itemsSource property. This tells the engine, “Hey, this is the data we want you to manage.” In the case of this application, we’re calling the getOrdersList() function from our service, which will return 1000 rows of data to load.
Now that we’ve got the PivotEngine loaded with data, it’s time to display it on-screen.
Setting Up the PivotPanel and PivotGrid
Now that the PivotEngine has been set up, it's time to use it. As we mentioned in the last section, the engine manages our data. So, for users to interact with the data, we need to connect it to our UI elements: the PivotPanel and PivotGrid.
We’ve previously mentioned Wijmo’s PivotGrid, but we haven’t talked much about the PivotPanel. The PivotPanel is how users interact directly with the data being displayed. This includes determining what data fields are column, row, filter, and value fields, as well as access to the Field Settings menu to further customize the data of the selected field.
Instead of any further explanation, it would be better to show you what you can do with the PivotPanel and PivotGrid. To add these to your application, add the following to your App.vue file:
<template>
<div>
<div class="flextable">
<wj-pivot-panel :items-source="ng"></wj-pivot-panel>
<wj-pivot-grid :items-source="ng"></wj-pivot-grid>
</div>
</div>
</template>
<script>
import '@grapecity/wijmo.styles/wijmo.css';
import { PivotEngine } from '@grapecity/wijmo.olap';
import * as wjcOlap from '@grapecity/wijmo.vue2.olap';
export default {
name: 'App',
components: {
'wj-pivot-panel': wjcOlap.WjPivotPanel,
'wj-pivot-grid': wjcOlap.WjPivotGrid
},
setup() {...}
}
</script>And that’s all it takes! It is that easy to implement Wijmo’s Vue pivot table. Like with the PivotEngine, the PivotGrid and PivotPanel both need to be supplied with an itemsSource. Unlike the PivotEngine, however, these components don’t take a simple array. They take a PivotEngine! So, using the ng variable we set up in the previous section, we can assign that as the itemsSource for both components.
Before running the application, we want to add a little style to clean up the layout. Inside the <style> tags, add the following:
.flextable {
display: flex;
}
.wj-pivotpanel {
width: 500px;
margin: 0px 10px;
}
.wj-pivotgrid {
height: 600px;
}We’re setting up the application so that the PivotPanel and PivotGrid display side-by-side, as well as setting the width of the PivotPanel so that it makes more room for the PivotGrid.
Now, if you run the application, you should see the following:
As you can see, nothing is loaded into the PivotGrid; that’s to be expected. Nothing will display unless we set some fields in the PivotPanel. Let’s fix that and add a couple of fields:
As you can see via the PivotPanel, we’ve added the following fields to the various areas of the panel:
|
Column Fields |
Row Fields |
Value Fields |
|
Platform |
Region |
Sales |
|
Product |
Agent |
Downloads |
|
|
|
Revenue |
Now, we can see a breakdown of all of the sales metrics across regions, by agents, and even by product and platform.
One final thing to discuss before moving on is the Field Settings menu. This menu can be accessed by right-clicking any of the cells in the PivotGrid, or by right-clicking any of the field names in the PivotPanel:
Selecting the Field Settings… option will open up the Field Settings modal:
Here, we can change how the data is summarized, add calculations (such as % difference from the previous row), add filters, and even format how the data is displayed, which includes a preview based on the selected format.
Since this is the field settings menu for the Sales field, we’re just going to change the format to display in a currency format instead of a basic integer format:
As you can see, it also shows us a preview of the updated format. If we click the OK button, we should see the following:
As you can see, the Sales columns are now all displaying in a currency format instead of an integer format.
Creating Custom Fields
Now that we’ve implemented the PivotPanel and PivotGrid and gone over the basics, it’s time to customize our data using the PivotEngine. We can do this by setting up properties manually via the PivotEngine, instead of allowing it to generate them automatically.
Open up the App.vue file and add the following to the PivotEngine:
var ng = new PivotEngine({
itemsSource: getOrdersList(1000),
fields:[
{ binding: 'date', header: 'Quarter', format: '"Q"q yyyy' },
{ binding: 'date', header: 'Month', format: 'MMMM' },
{ binding: 'agent', header: 'Agent' },
{ binding: 'region', header: 'Region' },
{ binding: 'platform', header: 'Platform' },
{ binding: 'product', header: 'Product' },
{ binding: 'sales', header: 'Sales', format: 'c2' },
{ binding: 'downloads', header: 'Downloads', format: 'n0' },
{ binding: 'revenue', header: 'Revenue', format: 'c2' },
]
});As you can see, the fields property takes an array of objects, which is used to create the various fields that users can pick from in the PivotPanel. By default, these objects only need to have two properties: a binding and a header property. The binding property tells the engine which property name this field will be bound to. For Agent, Region, Platform, and Product, these are the only properties that we’re setting.
However, there’s another property that we can set: the format property. Like how users can use the Field Settings menu to change the formatting, developers can set the default format in which we want to display the field.
Along with setting the format, we can actually create multiple fields from the same data point and set different formats for both. In this application, we’re setting up quarterly and monthly date fields. Now, if we save this and open up the application, we’ll select a few fields, and you should see the following:
As you can see, we’ve created a quarterly and monthly field using a single data point. Now, users can view an even greater breakdown, seeing sales data breakdowns by quarterly and monthly views.
Using PivotCharts to Display Data in Chart Form
Along with Wijmo’s Vue pivot table, PivotGrid, Wijmo also offers a PivotChart control to work in conjunction with the PivotPanel and the PivotGrid. Like the PivotGrid, the PivotChart will automatically update to reflect changes in areas of the PivotPanel.
Adding a PivotChart is just as simple as adding the PivotGrid and PivotPanel. Open up your App.vue file and modify the contents of the <template> tags as follows:
<template>
<div>
<div class="flextable">
<wj-pivot-panel :items-source="ng"></wj-pivot-panel>
<wj-pivot-grid :items-source="ng"></wj-pivot-grid>
</div>
<wj-pivot-chart :items-source="ng" :showTitle=true :showLegend="Auto"></wj-pivot-chart>
</div>
</template>And that’s it! We pass the PivotEngine in as the itemsSource for the PivotChart. Now, if you save the application and reload it if you add any fields to the Value area, you’ll see them reflected in the PivotChart. Take a look at the image below:
As you can see, we’ve set the Quarter and Platform fields as row fields and the Sales and Downloads fields as value fields. The PivotChart then shows a breakdown, similar to how the PivotGrid does, of the data we’ve selected inside the PivotPanel.
Conclusion
And with that, we’ve come to the end. In this article, we’ve gone over some of the benefits of using Vue pivot tables and how you can use Wijmo to implement your own pivot table, pivot chart, and pivot panel to manage the data.
If you’d like to try out Wijmo, you can download the entire UI component library, available in pure JavaScript, Angular, React, and Vue, here.
Happy coding!
Tags:
Related Blogs
Try Our JavaScript UI Components & Datagrid
