Exporting Options in SisenseJS

[OleksiiDemianyk]

Exporting Options in SisenseJS

SisenseJS is a powerful tool that allows embedding widgets from Sisense into external applications. Despite it being a very straightforward way of embedding, SisenseJS has some missed functionality. For example, there are no OOTB methods to export widget's data into CSV and Excel. In this article, we will implement this functionality. We will use a combination of Sisense REST API endpoints for exporting and capabilities of SisenseJS in order to prepare payloads. In our sample, we will create the required logic for one widget. You can replicate this logic for every widget in your implementation.

Common logic
When you load a dashboard, you need to save its object. For these purposes we will use a variable [currentDashboard]:

Sisense.connect('https://example.com').then(function (app) {
   app.dashboards.load(dashboardId).then(function (dashboard) {
       currentDashboard = dashboard;
       //Further logic to render widgets on the page
   });
});

In terms of our task we need to get the widget’s object we are going to export. To get this object you can run the following logic:

const getWidget = (widgetId) => {
   return currentDashboard.widgets.get(widgetId).$$model;
}

This function returns the widget’s object. We do have the widget’s object, so we know the widget’s metadata and also we have information about the dashboard’s filters. We could use this to build a query manually, but it would be quicker to utilize the SisenseJS capabilities.

When we call the function getWidget() we need to provide the widget’s identifier as an argument:

const currentWidget = getWidget('63cfd64645e8ec00324a13aa'); //Replace "63cfd64645e8ec00324a13aa" with your widget's identfier. Note, that it should be loaded and rendered

As a result, we will get the widget’s model. Later we will use it to prepare the payload. Also, we need to implement some functions to generate unique identifiers. This is not a mandatory step, but it can be useful if you want to cancel queries. Sample of the function:

function uuidv4() {
   return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
       var r = Math.random() * 16 | 0, v = c == 'x' ? r : (r & 0x3 | 0x8);
       return v.toString(16);
   });
}

Excel
To generate payload for Excel we will use the function below:

const prepareExcelPayload = (widget) => {
  const query = widget.dashboard.$query.buildWidgetQuery(widget); //Use Sisense capabilities to prepare query
  query.queryGuid = uuidv4(); //Generate queryGuid
  query.dashboard = widget.dashboardid || widget.dashboard.oid;
  query.culture = "en-us"; //This can be set from navigator.language
  query.format = 'pivot';
  query.metadata.forEach(function(m) {
      if (m.jaql && m.jaql.dim) { //We need to remove [table] and [column]
          delete m.jaql.table;
          delete m.jaql.column;
      }
  })
  return JSON.stringify({
      query: query,
      options: {}
  }); //Return payload for exporting in Excel
}

Once the payload is prepared, we can send it to Sisense’s endpoint /engine/excelExport to retrieve the prepared Excel file. After this, we need to load the generated Excel file explicitly.

Code:

function getExcel(widget) {
   const xhttp = new XMLHttpRequest();
   xhttp.withCredentials = true; //This will be cross-domain request, so we force the browser to add cookies
   xhttp.onreadystatechange = function () {
      if (xhttp.readyState === 4 && xhttp.status === 200) {
          const a = document.createElement('a');
          a.href = window.URL.createObjectURL(xhttp.response);
          a.download = widget.title ? `${widget.title}.xlsx` : 'Details.xlsx';
          a.style.display = 'none';
          document.body.appendChild(a);
          a.click();
      }
   };
   xhttp.open("POST", `${sisenseUrl}/engine/excelExport`);
   xhttp.setRequestHeader("Content-Type", "application/json");
   xhttp.responseType = 'blob';
   xhttp.send(payload);
}

CSV
A function to generate query:

const prepareCSVPayload = (widget) => {
   let query = widget.$query.buildWidgetQuery(widget, 'exportToCSV');
   query = Object.assign(query, {
       format: "csv",
       isMaskedResponse: true,
       download: true,
       count: 0,
       offset: 0
   });
   query = widget.dashboard.$query.createJaqlRequestConfig(query);
   query.metadata.filter((item) => {
       return defined(item.format);
   })
   return {
       data: encodeURIComponent(JSON.stringify(query))
   }; 
}

This function returns a payload, which can be sent at the endpoint `/api/datasources/${widget.datasource.title}/jaql/csv`. As you can see, this endpoint depends on the datasource’s title. Since the dashboard’s datasource can differ from the widgets’ datasources, I do recommend getting the correct datasource from the widget itself and for sure you need to avoid any hardcoded values.

function getCSV(widget) {
   let csvDownloader = new XMLHttpRequest();
   csvDownloader.open('POST', `/api/datasources/${widget.datasource.title}/jaql/csv`);
   csvDownloader.responseType = 'arraybuffer';
   csvDownloader.onload = function () {
      if (this.status === 200) {
          let filename = "";
          const disposition = csvDownloader.getResponseHeader('Content-Disposition');
          if (disposition && disposition.indexOf('attachment') !== -1) {
             const filenameRegex = /filename[^;=\n]*=((['"]).*?\2|[^;\n]*)/;
             const matches = filenameRegex.exec(disposition);
             if (matches != null && matches[1]) filename = matches[1].replace(/['"]/g, '');
          }
          const type = csvDownloader.getResponseHeader('Content-Type');
          const blob = typeof File === 'function'
           ? new File([this.response], filename, { type: type })
           : new Blob([this.response], { type: type });
          if (typeof window.navigator.msSaveBlob !== 'undefined') {
             // IE workaround for "HTML7007: One or more blob URLs were revoked by closing the blob for which they were created. These URLs will no longer resolve as the data backing the URL has been freed."
             window.navigator.msSaveBlob(blob, filename);
          } else {
             const URL = window.URL || window.webkitURL;
             const downloadUrl = URL.createObjectURL(blob);
             if (filename) {
                const a = document.createElement("a");
                if (typeof a.download === 'undefined') {
                   window.location = downloadUrl;
                } else {
                   a.href = downloadUrl;
                   a.download = filename;
                   document.body.appendChild(a);
                   a.click();
               }
            } else {
               window.location = downloadUrl;
            }
            setTimeout(() => {
               URL.revokeObjectURL(downloadUrl);
            }, 100); // cleanup
         }
      }
   };
   csvDownloader.withCredentials = true;
   csvDownloader.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
   $.param(payload);
   csvDownloader.send(payload);
}

I hope you find this article useful and leverage the knowledge shared about exporting widgets data in different formats. Please share your experience in the comments!

[nehabraham]

I am trying to implement this and have been able to implement the excel portion succesfully. The CSV portion is giving me a few issues.

query.metadata.filter((item) => {
       return defined(item.format);
   })

In the above section, I get an error message saying that 'defined is undefined'. I am only able to move past this by commenting it out but do not get a successful response from the server.

[ILLIA]

Hello!

Please accept my apologies for the delay. 
Kindly ask you to try using this prepareCSVPayload and check if the issue persists:

const prepareCSVPayload = (widget) => {
    let query = {}
    query.data = widget.$$model.$query.buildWidgetQuery(widget.$$model, 'exportToCSV');
    query.data = Object.assign(query.data, {
        format: "csv",
        isMaskedResponse: true,
        download: true,
        count: 0,
        offset: 0
    });
    query.data = widget.$$model.dashboard.$query.createJaqlRequestConfig(query.data);
    query.data = encodeURIComponent(JSON.stringify(query.data))
    return new URLSearchParams(query).toString()
}

Best regards,
Illia

[ILLIA]

UPD:

In order to apply proper encoding to returned body, please replace:

const type = csvDownload          const type = csvDownloader.getResponseHeader('Content-Type');
const blob = typeof File === 'function'
  ? new File([this.response], filename, { type: type })
  : new Blob([this.response], { type: type });

with

  const type = 'text/csv;charset=utf-8'
  const utf8BOM = new Uint8Array([0xEF, 0xBB, 0xBF])
  const blob = new Blob([utf8BOM, this.response], {type:type})

Best regards,
Illia

[csturgeon]

For the CSV, how is the WAT passed in the request.  Currently getting /api/datasources/StatementLive/jaql/csv 401 (Unauthorized) when making the request for the CSV.