Jollen MoyaniTL;DR: Integrate Syncfusion Vue Pivot Table with a Node.js Express server and MS SQL Server for efficient report management. Set up the SQL Server database and table, configure the Node.js server with necessary packages, and implement functions for saving, loading, renaming, and removing reports.
Managing reports in data-intensive apps can be challenging, mainly when dynamically saving and loading configurations. The Syncfusion Vue Pivot Table offers a comprehensive solution for creating interactive and robust pivot tables. When combined with a Node.js Express server, saving and loading reports becomes streamlined and efficient.
Let’s see how to save and load reports in the Vue Pivot Table using MS SQL Server as the backend database and Node JS Express for server-side logic. By the end, you’ll learn how to implement robust report management capabilities in your Vue.js apps.
Let’s get started!
Set up the MS SQL server database
First, we must configure the MS SQL server database to save and load the reports.
Step 1: Connect to the SQL Server Management Studio (SSMS)
First, open the SQL Server Management Studio and connect to your SQL server instance using the appropriate credentials.
Step 2: Creating a new database
In the Object Explorer, right-click on Databases and select New Database.
Now, enter a name for your database in the Database name field and click OK to create it.
Step 3: Create a new table
Once the new database has been created, expand it, then right-click on the Tables and choose New-> Table.
Define your table’s columns, including the column name, data type, and restrictions. Then, set up a primary key for the table if required.
To save the table, click Save, enter a name when prompted, and click OK.
To ensure your new table is created, open Object Explorer and expand the Tables folder under your Reports database. Refer to the following image.
Set up Node JS Express server
Let’s start by configuring a Node JS Express server. Open your terminal and execute the following commands.
mkdir server
cd server
npm init -y
npm install express mssql body-parser cors msnodesqlv8
Next, create a file named api.js and add the following code.
var express = require('express');
var bodyParser = require('body-parser');
var cors = require('cors');
var app = express();
var router = express.Router();
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
app.use(cors());
app.use('/api', router);
// Define the port for the server here.
app.listen(5000, () => {
console.log("Server is running on port 5000");
});
Then, create a file named dboperations.js and add the following code.
const sql = require('mssql/msnodesqlv8');
var dbConfig = {
server: 'localhost',
database: 'Reports',
driver: "msnodesqlv8",
options: {
trustedConnection: true,
}
}
Replace the server and database names in the above code with your MS SQL Server database information.
Then, in the dboperations.js file, we’ll add methods for save, save as, load, fetch, remove, and rename reports from the database.
[dboperations.js]
const sql = require('mssql/msnodesqlv8');
var dbConfig = {
server: 'localhost',
database: 'Reports',
driver: "msnodesqlv8",
options: {
trustedConnection: true,
}
}
async function SaveReportToDB(args) {
try {
let pool = await sql.connect(dbConfig);
let isDuplicate = true;
const request = pool.request();
request.input('reportName', sql.VarChar, args.reportName)
request.input('report', sql.VarChar, args.report)
var reports = await getReports(pool);
for (let i = 0; i < reports.length; i++) {
if (reports[i]["ReportName"] === args.reportName) {
isDuplicate = false;
request.query('update ReportTable set Report=@report where ReportName=@reportName');
}
}
if (isDuplicate) {
request.query('insert into ReportTable (ReportName, Report) values (@reportName, @report)');
}
}
catch (err) {
console.log(err);
}
}
async function RemoveReportFromDB(args) {
try {
let pool = await sql.connect(dbConfig);
const request = pool.request();
request.input('reportName', sql.VarChar, args.reportName);
request.query('delete from ReportTable where ReportName=@reportName');
}
catch (err) {
console.log(err);
}
}
async function RenameReportInDB(args) {
try {
let pool = await sql.connect(dbConfig);
const request = pool.request();
request.input('reportName', sql.VarChar, args.reportName);
request.input('renameReport', sql.VarChar, args.renameReport);
if (args.isReportExists) {
var reports = await getReports(pool);
for (let i = 0; i < reports.length; i++) {
if (reports[i]["ReportName"] === args.renameReport) {
request.query('delete from ReportTable where ReportName=@renameReport');
}
}
}
var reports = await getReports(pool);
for (let j = 0; j < reports.length; j++) {
if (reports[j]["ReportName"] === args.reportName) {
isDuplicate = false;
request.query('update ReportTable set ReportName=@renameReport where ReportName=@reportName');
}
}
}
catch (err) {
console.log(err);
}
}
async function FetchReportListFromDB() {
try {
let pool = await sql.connect(dbConfig);
var reports = await getReports(pool);
var reportNames = [];
for (let j = 0; j < reports.length; j++) {
reportNames.push(reports[j]["ReportName"]);
}
return reportNames;
}
catch (err) {
console.log(err);
}
}
async function LoadReportFromDB(args) {
try {
let pool = await sql.connect(dbConfig);
var report = '';
const request = pool.request();
request.input('reportName', sql.VarChar, args.reportName)
var reports = await getReports(pool);
for (let i = 0; i < reports.length; i++) {
if (reports[i]["ReportName"] === args.reportName) {
report = reports[i]["Report"];
break;
}
}
return report;
}
catch (err) {
console.log(err);
}
}
async function getReports(pool) {
try {
let reports = await pool.query("select * from ReportTable");
return reports.recordset;
}
catch (err) {
console.log(err);
}
}
module.exports = {
SaveReportToDB: SaveReportToDB,
RemoveReportFromDB: RemoveReportFromDB,
RenameReportInDB: RenameReportInDB,
FetchReportListFromDB: FetchReportListFromDB,
LoadReportFromDB: LoadReportFromDB
}
Following that, we’ll define endpoints in our Express server to handle the report manipulation operations defined in the dboperations.js file.
[api.js]
var dboperations = require('./dboperations');
var express = require('express');
var bodyParser = require('body-parser');
var cors = require('cors');
var app = express();
var router = express.Router();
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
app.use(cors());
app.use('/api', router);
router.route('/saveReport').post((request, response) => {
let report = { ...request.body }
console.log(report);
dboperations.SaveReportToDB(report).then(data => {
response.status(201).json(data);
})
})
router.route('/removeReport').post((request, response) => {
let report = { ...request.body }
console.log(report);
dboperations.RemoveReportFromDB(report).then(data => {
response.status(201).json(data);
})
})
router.route('/renameReport').post((request, response) => {
let report = { ...request.body }
console.log(report);
dboperations.RenameReportInDB(report).then(data => {
response.status(201).json(data);
})
})
router.route('/fetchReport').post((request, response) => {
dboperations.FetchReportListFromDB().then(data => {
response.status(201).json(data);
});
})
router.route('/loadReport').post((request, response) => {
let report = { ...request.body }
console.log(report);
dboperations.LoadReportFromDB(report).then(data => {
response.status(201).json(data);
})
})
// Define the port for the server here.
app.listen(5000, () => {
console.log("Server is running on port 5000");
});
Now, run the server with the following command.
npm start
Integrate with Syncfusion Vue Pivot Table
The Syncfusion Vue Pivot Table includes a built-in toolbar UI with predefined report manipulation options. These options can be used to perform dynamic report manipulation operations such as save, save as, rename, load, delete, and add reports. You can add these predefined report manipulation options to the toolbar UI, as per the documentation.
Refer to the following image.
With the above toolbar UI options, we can update the report to the SQL Server database via Vue Pivot Table and Node JS Express service. Refer to the following code example.
[App.vue]
<template>
<div>
<div class="control-section" style="overflow: auto, margin-top: 100px">
<div class="content-wrapper">
<ejs-pivotview id="pivotview" ref="pivotview" :dataSourceSettings="dataSourceSettings"
:width="width" :allowExcelExport="allowExcelExport"
:allowConditionalFormatting="allowConditionalFormatting"
:allowPdfExport="allowPdfExport" :showToolbar="showToolbar" :allowCalculatedField="allowCalculatedField"
:showFieldList="showFieldList" :toolbar="toolbar" :saveReport="saveReport" :loadReport="loadReport"
:fetchReport="fetchReport" :renameReport="renameReport" :removeReport="removeReport" :newReport="newReport"
:toolbarRender="beforeToolbarRender" :displayOption="displayOption"
:chartSettings="chartSettings"></ejs-pivotview>
</div>
</div>
</div>
</template>
<script>
import Vue from "vue";
import {
PivotViewPlugin,
FieldList,
CalculatedField,
Toolbar,
ConditionalFormatting,
NumberFormatting
} from "@syncfusion/ej2-vue-pivotview";
import { enableRipple } from "@syncfusion/ej2-base";
enableRipple(false);
Vue.use(PivotViewPlugin);
var data = [
{ 'Sold': 25, 'Amount': 42600, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2015', 'Quarter': 'Q4' },
{ 'Sold': 27, 'Amount': 46008, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2016', 'Quarter': 'Q1' },
{ 'Sold': 49, 'Amount': 83496, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2016', 'Quarter': 'Q2' },
{ 'Sold': 31, 'Amount': 52824, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2015', 'Quarter': 'Q1' },
{ 'Sold': 51, 'Amount': 86904, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2015', 'Quarter': 'Q2' },
// ....
];
export default {
data() {
return {
dataSourceSettings: {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: data,
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
},
displayOption: { view: "Both" },
chartSettings: {
value: "Amount",
enableExport: true,
chartSeries: { type: "Column", animation: { enable: false } },
enableMultipleAxis: false,
},
toolbar: [
"New",
"Save",
"SaveAs",
"Rename",
"Remove",
"Load",
"Grid",
"Chart",
"MDX",
"Export",
"SubTotal",
"GrandTotal",
"ConditionalFormatting",
"FieldList",
],
allowExcelExport: true,
allowConditionalFormatting: true,
allowPdfExport: true,
showToolbar: true,
allowCalculatedField: true,
showFieldList: true,
width: "700",
};
},
methods: {
updateReport(reportList) {
var pivotTableObj = (this.$refs.pivotview).ej2Instances;
// Here, you can refresh the report list by feeding updated reports fetched from the database.
var reportListObj = pivotTableObj.element.querySelector(
"#" + pivotTableObj.element.id + "_reportlist").ej2_instances;
if (reportListObj) {
reportListObj[0].dataSource = reportList;
reportListObj[0].value = pivotTableObj.toolbarModule.currentReport;
// To remove the report.
if (pivotTableObj.toolbarModule.currentReport === "" && (reportListObj[0].itemData === null || reportList.length < 2)) {
pivotTableObj.toolbarModule.currentReport = reportList[reportList.length - 1];
reportListObj[0].value = pivotTableObj.toolbarModule.currentReport;
this.loadReport({ reportName: reportList[reportList.length - 1] })
}
}
},
saveReport(args) {
var report = JSON.parse(args.report);
report.dataSourceSettings.dataSource = [];
report.pivotValues = [];
fetch('http://localhost:5000/api/saveReport', {
method: 'POST',
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json',
},
body: JSON.stringify({ reportName: args.reportName, report: JSON.stringify(report) })
}).then(response => {
this.fetchReport(args);
});
},
fetchReport(args) {
fetch('http://localhost:5000/api/fetchReport', {
method: 'POST',
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json',
},
body: ""
}).then(res => res.json())
.then(response => {
this.updateReport(response.length > 0 ? response : []);
});
},
loadReport(args) {
fetch('http://localhost:5000/api/loadReport', {
method: 'POST',
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json',
},
body: JSON.stringify({ reportName: args.reportName })
}).then(res => res.json())
.then(response => {
if (response) {
var report = JSON.parse(response);
var pivotTableObj = (this.$refs.pivotview).ej2Instances;
report.dataSourceSettings.dataSource = pivotTableObj.dataSourceSettings.dataSource;
pivotTableObj.dataSourceSettings = report.dataSourceSettings;
}
});
},
removeReport(args) {
fetch('http://localhost:5000/api/removeReport', {
method: 'POST',
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json',
},
body: JSON.stringify({ reportName: args.reportName })
}).then(response => {
this.fetchReport(args);
});
},
renameReport(args) {
fetch('http://localhost:5000/api/renameReport', {
method: 'POST',
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json',
},
body: JSON.stringify({ reportName: args.reportName, renameReport: args.rename, isReportExists: args.isReportExists })
}).then(response => {
this.fetchReport(args);
});
},
newReport() {
var pivotTableObj = (this.$refs.pivotview).ej2Instances;
pivotTableObj.setProperties({ dataSourceSettings: { columns: [], rows: [], values: [], filters: [] } }, false);
},
beforeToolbarRender(args) {
args.customToolbar.splice(6, 0, {
type: 'Separator'
});
args.customToolbar.splice(9, 0, {
type: 'Separator'
});
}
},
provide: {
pivotview: [FieldList, CalculatedField, Toolbar, ConditionalFormatting, NumberFormatting]
}
};
</script>
<style>
@import "../node_modules/@syncfusion/ej2-base/styles/material3.css";
@import "../node_modules/@syncfusion/ej2-inputs/styles/material3.css";
@import "../node_modules/@syncfusion/ej2-buttons/styles/material3.css";
@import "../node_modules/@syncfusion/ej2-splitbuttons/styles/material3.css";
@import "../node_modules/@syncfusion/ej2-dropdowns/styles/material3.css";
@import "../node_modules/@syncfusion/ej2-lists/styles/material3.css";
@import "../node_modules/@syncfusion/ej2-popups/styles/material3.css";
@import "../node_modules/@syncfusion/ej2-navigations/styles/material3.css";
@import "../node_modules/@syncfusion/ej2-grids/styles/material3.css";
@import "../node_modules/@syncfusion/ej2-pivotview/styles/material3.css";
@import "../node_modules/@syncfusion/ej2-vue-pivotview/styles/material3.css";
</style>
[api.js]
var dboperations = require('./dboperations');
var express = require('express');
var bodyParser = require('body-parser');
var cors = require('cors');
var app = express();
var router = express.Router();
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
app.use(cors());
app.use('/api', router);
router.route('/saveReport').post((request, response) => {
let report = { ...request.body }
console.log(report);
dboperations.SaveReportToDB(report).then(data => {
response.status(201).json(data);
})
})
router.route('/removeReport').post((request, response) => {
let report = { ...request.body }
console.log(report);
dboperations.RemoveReportFromDB(report).then(data => {
response.status(201).json(data);
})
})
router.route('/renameReport').post((request, response) => {
let report = { ...request.body }
console.log(report);
dboperations.RenameReportInDB(report).then(data => {
response.status(201).json(data);
})
})
router.route('/fetchReport').post((request, response) => {
dboperations.FetchReportListFromDB().then(data => {
response.status(201).json(data);
});
})
router.route('/loadReport').post((request, response) => {
let report = { ...request.body }
console.log(report);
dboperations.LoadReportFromDB(report).then(data => {
response.status(201).json(data);
})
})
// Define the port for the server here.
app.listen(5000, () => {
console.log("Server is running on port 5000");
});
[dboperations.js]
const sql = require('mssql/msnodesqlv8');
var dbConfig = {
server: 'localhost',
database: 'Reports',
driver: "msnodesqlv8",
options: {
trustedConnection: true,
}
}
async function SaveReportToDB(args) {
try {
let pool = await sql.connect(dbConfig);
let isDuplicate = true;
const request = pool.request();
request.input('reportName', sql.VarChar, args.reportName)
request.input('report', sql.VarChar, args.report)
var reports = await getReports(pool);
for (let i = 0; i < reports.length; i++) {
if (reports[i]["ReportName"] === args.reportName) {
isDuplicate = false;
request.query('update ReportTable set Report=@report where ReportName=@reportName');
}
}
if (isDuplicate) {
request.query('insert into ReportTable (ReportName, Report) values (@reportName, @report)');
}
}
catch (err) {
console.log(err);
}
}
async function RemoveReportFromDB(args) {
try {
let pool = await sql.connect(dbConfig);
const request = pool.request();
request.input('reportName', sql.VarChar, args.reportName);
request.query('delete from ReportTable where ReportName=@reportName');
}
catch (err) {
console.log(err);
}
}
async function RenameReportInDB(args) {
try {
let pool = await sql.connect(dbConfig);
const request = pool.request();
request.input('reportName', sql.VarChar, args.reportName);
request.input('renameReport', sql.VarChar, args.renameReport);
if (args.isReportExists) {
var reports = await getReports(pool);
for (let i = 0; i < reports.length; i++) {
if (reports[i]["ReportName"] === args.renameReport) {
request.query('delete from ReportTable where ReportName=@renameReport');
}
}
}
var reports = await getReports(pool);
for (let j = 0; j < reports.length; j++) {
if (reports[j]["ReportName"] === args.reportName) {
isDuplicate = false;
request.query('update ReportTable set ReportName=@renameReport where ReportName=@reportName');
}
}
}
catch (err) {
console.log(err);
}
}
async function FetchReportListFromDB() {
try {
let pool = await sql.connect(dbConfig);
var reports = await getReports(pool);
var reportNames = [];
for (let j = 0; j < reports.length; j++) {
reportNames.push(reports[j]["ReportName"]);
}
return reportNames;
}
catch (err) {
console.log(err);
}
}
async function LoadReportFromDB(args) {
try {
let pool = await sql.connect(dbConfig);
var report = '';
const request = pool.request();
request.input('reportName', sql.VarChar, args.reportName)
var reports = await getReports(pool);
for (let i = 0; i < reports.length; i++) {
if (reports[i]["ReportName"] === args.reportName) {
report = reports[i]["Report"];
break;
}
}
return report;
}
catch (err) {
console.log(err);
}
}
async function getReports(pool) {
try {
let reports = await pool.query("select * from ReportTable");
return reports.recordset;
}
catch (err) {
console.log(err);
}
}
module.exports = {
SaveReportToDB: SaveReportToDB,
RemoveReportFromDB: RemoveReportFromDB,
RenameReportInDB: RenameReportInDB,
FetchReportListFromDB: FetchReportListFromDB,
LoadReportFromDB: LoadReportFromDB
}
Let’s examine how to use the built-in toolbar options with the SQL Server database and the Node JS Express server, one by one, using the above code.
Saving a report
When you select the Save a Report option from the toolbar, the saveReport event will be triggered. In this event, a fetch request is sent to the Node JS Express server’s (aka api.js ) saveReport method, passing the name of the current report and its settings, which can then be checked and saved in the SQL Server database.
For example, the report shown in the above code will be passed to the saveReport method with the name Sample Report (the name Sample Report comes from the source code by default, but you can change it in the code) and saved in the SQL Server database.
Refer to the following image.
If you want to save a copy of the current report to the SQL Server database under a different name, use the Save as current report option in the toolbar UI.
When you do so, the saveReport event will be triggered with the current report and new report name Sample Report 1. As previously mentioned, they can be saved to the SQL Server database after being passed to the Node JS Express service.
Note: The code snippets for Save and Save as reports are all the same as mentioned above.
Loading a report
When you select a report from the toolbar’s dropdown menu, the loadReport event is triggered. In this event, a fetch request is sent to the Node JS Express server’s (aka api.js ) LoadReport method, with the selected report’s name as the parameter. This method searches the SQL Server database for the report, retrieves it, and loads it into the Pivot Table.
For example, if the report name Sample Report is chosen from a dropdown menu and passed, the LoadReport method will look up the report in the SQL Server database, retrieve it, and load it into the pivot table.
Renaming a report
Use the Rename a current report icon in the toolbar to rename the current report name and save it back to the SQL database.
When you select the Rename a Current Report option, the renameReport event is triggered. In this event, a fetch request is sent to the Node JS Express server’s (aka api.js ) RenameReport method, passing the current and new report names. You can use the current report name to identify the report and resave it with the new report name in the SQL Server database.
For example, suppose we rename the current report from Sample Report 1 to Sample Report 2. In that case, both names will be passed to the RenameReport method, renaming the current report to Sample Report 2 in the SQL Server database.
Deleting a report
When you select the Delete a current report option from the toolbar, the removeReport event is fired. In this event, a fetch request is made to the Node JS Express server’s (aka api.js) RemoveReport method, which uses the current report name to locate and delete the appropriate report from the SQL Server database.
For example, suppose we delete the current report, Sample Report 2, from the Pivot Table. In that case, the current report, Sample Report 2, is passed to the RemoveReport method, allowing you to find and remove the report from the SQL Server database.
Note:
1. If the current report n is deleted, the Vue Pivot Table will automatically load the last report in the report list.
2. If only one report is in the list and removed, the Pivot Table will display the removed report state until a new report is added.
Adding a report
When you select the Create a new report option from the toolbar, the newReport event is triggered first, followed by the saveReport event, which saves this new report to the SQL Server database using the same saveReport method outlined in the code blocks above.
Limitations for report manipulation
The following points should be considered when saving the report to the SQL Server database:
- Both raw and aggregated data will not be saved or loaded from the database.
- The option to add external links using Pivot Table cells will not be saved or loaded from the database.
- The Pivot Table should always load reports from the SQL Server database using the data source that is currently assigned to it.
GitHub reference
For more details, refer to saving and loading reports in Vue Pivot Table using SQL and Node JS Express servers GitHub demo.
Conclusion
Thanks for reading! In this blog, we learned how to easily connect to an MS SQL Server database and a Node JS Express server to save and load reports into the Syncfusion Vue Pivot Table. Try this and share your thoughts in the comments section below!
For existing customers, the newest version of Essential Studio is available for download from the License and Downloads page. If you are not a Syncfusion customer, try our 30-day free trial to check out the available features.
For questions, you can contact us through our support forum, support portal, or feedback portal. We are always happy to assist you!
