scriptrunner automated report

Question

I have written a script that gets all the detail I need but at this point it has to be manually run and then saved and then re-opened in excel.. Here is the script (below) and the final objective is to utilize this to generate output automatically each quarter. Basically I am totaling hours around field selections on issues, but need this in a report/output that does not have to be manually run and generated. What is the best approach for this utilizing scriptrunner or a combo of scriptrunner and delivered Jira functionality and free automation for jira lite.. Not looking to purchase another plugin import com.atlassian.jira.component.ComponentAccessor import com.atlassian.jira.issue.search.SearchProvider import com.atlassian.jira.jql.parser.JqlQueryParser import com.atlassian.jira.web.bean.PagerFilter def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser) def searchProvider = ComponentAccessor.getComponent(SearchProvider) def issueManager = ComponentAccessor.getIssueManager() //def user = ComponentAccessor.getJiraAuthenticationContext().getUser() def user = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser() String field1 = "Client"; String field2 = "OIG Strategic Goal"; //TODO: Please change field 1 and field 2 name to proper names according to your actual settings List value1 = new ArrayList(); value1.add("Audit"); value1.add("Investigations"); value1.add("Management"); value1.add("ODS"); List value2 = new ArrayList(); value2.add("1- Safety and Security: Strengthen USDA’s ability to implement and improve safety and security measures to protect the public health, as well as agricultural and Departmental resources"); value2.add("2- Integrity of Benefits: Detect and reduce program vulnerabilities and deficiencies to strengthen the integrity of the Department’s programs"); value2.add("3- Workforce: Recruit, develop, and maintain a highly qualified and diverse workforce while fostering a work environment that promotes productivity, innovation, excellence, and employee satisfaction"); value2.add("4- Improvement Initiatives: Provide USDA with oversight to help it achieve results-oriented performance"); //TODO: Set the options of two fields accordingly. StringBuffer result = new StringBuffer(); result.append(field1) result.append(",") result.append(field2) result.append(",Keys,Hours") result.append("
") for(String first_value: value1){ for(String second_value: value2){ String jql = "\"" + field1 + "\"=" + "\""+ first_value + "\" and \"" + field2 + "\"=\"" + second_value + "\""; // result.append(jql); // result.append("
") def query = jqlQueryParser.parseQuery(jql); def results = searchProvider.search(query, user, PagerFilter.getUnlimitedFilter()) log.debug("Total issues: ${results.total}") long total_log = 0; if(results.total>0){ result.append("\"") result.append(first_value) result.append("\"") result.append(",") result.append("\"") result.append(second_value.replace("'","\'")) result.append("\"") result.append(",") result.append("\""); results.getIssues().each {documentIssue -> log.debug(documentIssue.key) result.append(documentIssue.key) result.append(",") def issue = issueManager.getIssueObject(documentIssue.id) def time_spent = issue.timeSpent if(time_spent!=null) { total_log = total_log + time_spent log.debug(total_log) } } if(result.toString().charAt(result.length()-1)==","){ result.setLength(result.length()-1) } result.append("\",") result.append(total_log/3600) result.append("
") } } } return result.toString()

Aron Gombas _Midori_ · Answer

If you eventually changed your mind, give a try to the Better Excel Exporter app, because:

* It is integrated with ScriptRunner (to reuse your skills).
* It is integrated with Automation for Jira (for automatic report generation).
* It is integrated with Tempo (to collect the report's source data).

It could a powerful tool set for your use case.

Jennifer Ward · Answer

Thanks so much for your insight..  I went a bit of a different route, but got the output I needed..  If only Tempo would just provide better reporting features!  Thanks again!

PD Sheehan · Answer

My understanding from your script is that you would need only 1 scripted field for something like "Total Hours". Maybe a second if you need to list all the issue keys.

So new project RPT with only 3/4 fields on the screens

* Client
* OIG Strategic Goal
* Total Hours
* Issues

Then you create your 16 issues:

* RPT-1 Client = Audit & OIG Strategic Goal = "1- Safety and Security ..."
* RPT-1 Client = Audit & OIG Strategic Goal = "2- Safety and Security ..."
* ...
* RPT-16 Client = ODS & OIG Strategic Goal = "4- Improvement Initiatives ..."

Then your scripted field would follow this Adaptavist Script Library example with the jql being something like:

def customFieldManager = customFieldManager.customFieldManager
def clientCf = customFieldManager.getCustomFieldObjectByName("Client")
def goalCf = customFieldManager.getCustomFieldObjectByName("ODS & OIG Strategic Goal")
//or use the customfield id if you know it
//def goalCf = customFieldManager.getCustomFieldObject(123456)
def clientVal = issue.getCustomFieldValue(clientCf)
def goalVal= issue.getCustomFieldValue(goalCf )
def jqlSearch = """
   Client = $clientVal AND "ODS & OIG Strategic Goal" = $goalVal
"""

One thing to mention is that scripted custom fields don't store their values in the db. They are calculated on the fly when the issue is indexed or accessed.  They are not re-calculated when other issues (such as those that match the jql) are updated.

But when you view the issues, either in jira, in a filter or in the dashboard, it will be calculated fresh each time.