The SIL Excel Reporting Gadget

SIL Excel Reporting Gadget

The main feature of the SIL Excel Reporting app is the ability to run SIL scripts using the reporting gadget and to export their result to an Excel file. The gadget can be installed in your Jira Dashboard after successfully installing the app.The process of installing and configuring the gadget is described below.

Configuration

The configuration screen is only available to Jira Administrators and System Administrators and allows them to manage the list of available reporting configurations. They can add and delete configurations, edit the parameters or change the security of a reporting configuration.

A reporting configuration is comprised of the following components:

• Name - the name of the configuration
• Execution script - the script that is executed and which writes the Excel report file

• Parameter script - an optional script that dynamically inserts advanced parameter fields on the configuration's run screen. If no parameter script is given, the user is able to add simple input fields for text parameters.

These components can be edited in the following screen, after choosing the gadget's edit option:

The gadget also offers the ability to restrict script usage to specific users or groups by choosing a security option:

•  Public - the script is available for any user
•  Group - the script is available only if the currently logged in user is a member of the specified group (a group picker is displayed)
•  User - the script is available only if the currently logged in user is the same as the specified one (a group picker is displayed)
•  Project role - the script is available only if the currently logged in user is in a specific role on a specific project (a project picker is displayed)

After successfully editing/creating a configuration, you are able to select that configuration and run the associated script.

The following image represents a configuration defined using a parameter script which adds an input field, a DatePicker and a checkbox field in the report's main interface.

For a configuration having no parameter script it will look like this:

Simple text parameters can be added by clicking the Add Parameter button.

For more information about the parameter script refer to the Input type routines.

Usage

Let's assume you have the following parameter script:

gadget_createInput("Template file", "template.xlsx", true, "Excel template file");
gadget_createInput("Sheet name", "Data", true, "Data Worksheet name in the template file");
gadget_createDatePicker("Start Date", currentDate(), true, "Choose a start date");

The report execution script contains the following routine calls to retrieve the parameters' values:

string templateFile = gadget_getSingleValue(argv, "Template file");
string sheetName = gadget_getSingleValue(argv, "Sheet name");
date startDate = gadget_getDateValue(argv, "Start Date");

You use these values further in the execution script for setting up the template and worksheet:

//setting up headers and column types
string[] headers = {"Name", "User Name", "Salary", "Age"};
number[] columnTypes = {0, 0, 1, 1};
string strStartDate=formatDate(startDate, "MM/dd/yyyy");
string strEndDate=formatDate(currentDate(), "MM/dd/yyyy");
//creating SQL query string
string sqlDate="SELECT NAME, USERNAME, SALARY FROM USERS Where and LAST_LOGIN_DATE between to_date('"+strDateFrom+"','mm/dd/yyyy') and to_date('"+strDateTo+"','mm/dd/yyyy')";
//the data source must be defined
string dbstring = "DataSource";
//there are more ways to define a data array, querying a database is just one of them
string[] data=sql(dbstring,sqlDate); 
runnerLog("Setting up template file");
reporting_setupTemplate(templatePath);
runnerLog("Setting up worksheet");
reporting_setupSheet(sheetName, headers, columnTypes);
runnerLog("Writing data...");
reporting_addData(sheetName, data);
string fileName = reporting_writeWorkbook();
runnerLog("Done writing, returning file.");
return fileName;

Once you run the script, the program console is displayed