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 will be executed and which will write the Excel report file

• Parameter script - an optional script that will dynamically insert advanced parameter fields on the configuration's run screen. If no parameter script is given, the user will be 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 will be available for any user

          Group - the script will be available only if the currently logged in user is a member of the specified group (will display a group picker)

          User - the script will be available only if the currently logged in user is the same as the specified one (will display a user picker)

  Project role - the script will available only if the currently logged in user is in a specific role on a specific project (will display a project picker)

After successfully editing/creating a configuration, you will be able to select that configuration an 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 we will have the following appearance:

Simple text parameters can be added with a press of the Add Parameter button.

For more information about the parameter script refer to the Input type routines section of the current documentation.

Usage

Let's assume we 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 will contain the following routine calls in order 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");

We will 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 will be displayed.