Basic Report Creation And Report Modification

Author: Colm Carew

– This assumes that you have some MySQL and Groovy/Java knowledge.

Always remember to use Errigal QA first (qadb1.err) as the datasource and connect as a reader account. If you have any questions feel free to ask a team member.

• Make sure you have the Report Builder Project - If you have any issues setting it up ask a coworker to aid you
  1. https://bitbucket.org/errigal/reportbuilder
• The two classes that should be focused on are the ReportCreator.groovy which is where you will write the report and SqlConnectionManager.groovy which is where you specify what database you are connecting to
• In order to write a report I suggest starting every report in MySQL Workbench to get the main report query established before implementing it into a groovy script
• As an example say we have a report where the user wants a list of the most recent 100 ticket ids with their corresponding current status the sql would look like :

 SELECT
   t.id ticket_id, st.status ticket_status
 FROM
   ticket t
     JOIN
   ticket_status st ON st.id = t.current_status_id
   ORDER BY t.id desc
 LIMIT 100;

• To turn this query into a report we need to look at the runscript() section of code in ReportCreator.groovy. For the example above the run script should look like :

 def runScript() {
 def db = SqlConnectionManager.instance.retrieveSql('ticketer')
 def table = new ReportTable(title: "Example Report")
 String exampleQuery = """
 SELECT
    t.id ticket_id, st.status ticket_status
 FROM
    ticket t
        JOIN
    ticket_status st ON st.id = t.current_status_id
    ORDER BY t.id desc
 LIMIT 100;
 """
 def exampleResult = db.rows(exampleQuery)
 exampleResult.each { resultRow ->
   //If you want everything from your query
   //resultRow.row_color = ''
   //table.addData(resultRow)
   //If you want to specify which columns + which order they should be in
   table.addData([
       ticketId    : resultRow.ticket_id,
       ticketStatus: resultRow.ticket_status
    ])
  }
 [table: table]
 } 

• Running this report should return something similar to :

1. Generate a report that gets all open tickets and contains the following columns. Tickets with a visibility of 'Deleted Tickets' and child tickets should not be included in the report. Order the report by workflow name and then create date
   1. Ticket Id
   2. Workflow Name
   3. Ticket Status
   4. Ticket Creator (Their Full Name - Not their id)
   5. Ticket Create Date
   6. Market (This is the ticket visibility)
   7. Region
2. Generate a report that gets all alarms that created tickets within a certain time frame (use the alarm create time frame not the ticket create time frame). The columns should be as follows:
   1. Ticket Id
   2. Alarm Id
   3. Trap Name
   4. Alarm Create Date
   5. Alarm Cleared Date
   6. Network Element Name
   7. Network Element Hub Name

**Note when moving between the Report Builder and the Reporting Manager the code in-between :
import com.osssoftware.reporting.*
and
def response = new ReportExecutorResponse()
in the Reporting Manager report is the same as the code between
def runScript() {
and
[chart: chart, table: table] }
in the Report Builder

• Go to a Reporting Manager and copy the following report to local machine : 'Who Gets Email for Given Visibility'
• Add the user id column of the user to this report on your local machine

The errigal-amcharts plugin used for the charts creation lacks the ability of modifying many chart-related settings (e.g. max and min Y axis line values).

Should you need to modify the chart settings or values in non-plugin-intended ways, you may do so manually. In the chart generation Groovy script, retrieve the chart settings after building the chart (chart.retrieveChartSettings(true)), modify the XML file-representing String manually (you can find all the required information on doing that here), and then set the ReportExecutorResponse chartSettings as the modified String.