KB Article 16362
2020/01/16

Close
X
Contact Tech Support

 Email Tech Support
 (250) 655-1766
 [7:30 - 5:00 PST]
Your Meeting ID will be Provided by a Rep.
Tech Support
Tech Support Home
Software Updates
Knowledge Base Search
Recent KB Articles
Product FAQs
Code Samples
Documentation
Tech Blog
System Requirements
Installation Instructions
Support Testimonials
Contact us to...
Create a Ticket
Request a Free Demo
Suggest a New Feature
Submit Feedback
Upload a Large File
Other Services
Software Training
Professional Services

How to have dynamic libraries and tables on an SQL query and export the resulting data to excel

Product: Presto Type: How to

Problem

Although it is possible to export data using SQL queries with dynamic parameter values on the visual editor, the only way to work with parameters relating to subfiles and libraries in SQL is through javascript. Below are the instructions on how to work with this dynamic collection of data through SQL and export it to excel as a .csv file.

 

Solution

On the panel where you want this done, add a button and label it "Export Data". 

On the element properties, make note of the Element ID.

Create a SQL query under Tools -> SQL Queries as follows:

    SELECT * FROM {lib}/{file}

Go to the page level javascript of the page, and add the following code:

 

jQuery(document).one("PageComplete", function()
{
    // Put the element ID for the button to click for exporting data
    jQuery("#xlg0001").on("click", function()
    {
        Presto.ExecuteSql({
            format: "json",
            dataType: "json",
            name: "my_query", // Name of the SQL query
            placeholders:
            {
                "file": "mu_custf", // File to get data from
                "lib": "xl_prdemo" // Library where table is located
            },
            success: function(data, textStatus, XMLHttpRequest)
            {
                JSONToCSVConvertor(data, "MyTitle", true);
            }
        });
    });
});

function JSONToCSVConvertor(JSONData, ReportTitle, ShowLabel){

    //If JSONData is not an object then JSON.parse will parse the JSON string in an Object
    var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
    
    var CSV = 'sep=,' + '\r\n\n';

    //This condition will generate the Label/Header
    if (ShowLabel) {
        var row = "";

        //This loop will extract the label from 1st index of on array
        for (var index in arrData[0]) {

            //Now convert each value to string and comma-seprated
            row += index + ',';
        }

        row = row.slice(0, -1);

        //append Label row with line break
        CSV += row + '\r\n';
    }

    //1st loop is to extract each row
    for (var i = 0; i < arrData.length; i++) {
        var row = "";

        //2nd loop will extract each column and convert it in string comma-seprated
        for (var index in arrData[i]) {
            row += '"' + arrData[i][index] + '",';
        }

        row.slice(0, row.length - 1);

        //add a line break after each row
        CSV += row + '\r\n';
    }

    if (CSV == '') {
        alert("Invalid data");
        return;
    }

    //Generate a file name
    var fileName = "MyFileName_";
    //this will remove the blank-spaces from the title and replace it with an underscore
    fileName += ReportTitle.replace(/ /g,"_");

    //Initialize file format you want csv or xls
    var uri = 'data:text/csv;charset=utf-8,' + escape(CSV);

    // Now the little tricky part.
    // you can use either>> window.open(uri);
    // but this will not work in some browsers
    // or you will not get the correct file extension
    // so we use the following solution

    // Add functionality to IE and Edge
    var blob = new Blob([CSV], {type: "text/csv;charset=utf-8;"})
    if(navigator.msSaveBlob)
    {
        navigator.msSaveBlob(blob, fileName + ".csv");
    }
    else
    {
        //this trick will generate a temp <a /> tag
        var link = document.createElement("a");
        link.href = uri;
    
        //set the visibility hidden so it will not effect on your web-layout
        link.style = "visibility:hidden";
        link.download = fileName + ".csv";

        //this part will append the anchor tag and remove it after automatic click
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    }
}

Rate This Article

Did this example help you to achieve your goal?
 Yes  No  Don't Know

Enter additional comments below.   If you want to hear back from us, include your contact information.

Email Address:
Comments:
Please enable JavaScript in order to rate this page.