Button to download pivot table to excel
The idea would be to create a text widget with the label "Download to Excel" and in that widget's script have functionality which would emulate exactly what a pivot table's download excel file menu item does when a user clicks on that text area. Reason for this is people within our organization are heavy users of download to excel functionality and that function is buried in a menu which is either inconvenient and/or convoluted to use.
Any ideas on what a script like this would look like or another approach which would address this issue?
Thanks
-
I came across this thread which seems very similar to my inquiry. Does the response from Sisense in that thread indicate that there is no way through the API we can achieve this functionality or does it mean that it would take development time using the API on Sisense's part? If it's the latter, I would just like to know where to look for the code that would give me insight as to how I could code this myself.
-
With the help of Sisense I was able to come up with something that captures the heart of the functionality I have been after. If you create a dashboard with a pivot table and a text widget you can paste the below code in the text widget's script and when a user clicks the text widget an excel file with the pivot table's data will download.
widget.on('ready', function(e) {
if(element.find("font").length >= 1){
element.find("font").text("Download to Excel");
}else{
element.find("span").text("Download to Excel");
}
$(element).on("click", function() {downloadToExcel()});
});function downloadToExcel() {
var widgetQuery = prism.activeDashboard.$query.buildWidgetQuery(
prism.activeDashboard.widgets.$$widgets[0] // any widget obj
),
$http = prism.$injector.get('$http');widgetQuery.dashboard = prism.activeDashboard.oid;
$http.post('/engine/excelExport', {
options: {},
query: widgetQuery
}, {
responseType: 'blob'
}).then(res => {var fileName = 'example';
// IE11 and older fix because they have bug with createObjectURL
if (window.navigator.msSaveBlob) {
window.navigator.msSaveBlob(res.data, fileName);
} else {
var blob = new Blob([res.data], {
type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
});
var blobURL = window.URL.createObjectURL(blob);
var a = document.createElement('a');
a.href = blobURL;
a.download = fileName;
document.body.appendChild(a);
a.click();
window.URL.revokeObjectURL(blobURL);
document.body.removeChild(a);
}
});
}I am not very good with Javascript so any advice on how to make this code cleaner is welcomed. While this does capture the heart of functionality I am also looking to improve the user experience through the following list of things:
1. On hover over text area change cursor to hand like other web links/buttons.
2. It seems when a user has the ability to edit the dashboard they are required to click the text widget twice to download. The first click brings up text editing functions and the second fires the download. Would like to have to only click once.
3. On click, pop up dialog box which signals that a download is happening and blocks user from further interaction with the dashboard and then auto closes when download completes.
4. Be able to reference a pivot table from a different dashboard. Our users will be looking at aggregated data and sometimes would want to see the details of that data. Only way we know how to make this possible is to make a text widget jump to a dashboard with a pivot table that contains that detailed data. At this point the user is not interested in seeing the data in pivot table form but rather has already made the decision of wanting a download of that data in Excel format so it would be nice to be able to skip the intermediate jump to step and just download the pivot table from the aggregate dashboard.
If anyone has any insight on how to accomplish any thing in the list you help would be much appreciated.
-
Hi all,
Ravid here from Paldi Solutions.
This post inspired us to develop an officially supported plugin that does exactly that and more.
Feel free to check out the listing on the Marketplace.
We're expected to release more enhancements to this plugin, so we'd love to hear your feedback & suggestions.
Feel free to reach out for a free trial.
Best,
Ravid
Please sign in to leave a comment.
Comments
7 comments