Use of report variables

Author: Colm Carew

Secure variables are [“CUSTOMER”, “ROLE”, “CARRIER”,“CUSTOMERNAME”,“CARRIERNAME”]. If any of these variable names are used in your report then the user will need the associated permission such that if carrier is a variable and the user runs the report for ATT(Tickets) then the user must have the role of ATT(Tickets) to run the report!

---

From the Report Builder Aspect, variables can be used by defining them above def runScript()

Example - if we want to have a a report that shows a ticket id and its status where the ticket was created on or after a certain date the code it the Report Builder would look like :

String createDate = '2016-08-10'
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
  WHERE
  t.create_date >= '${createDate} 00:00:00'
  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
    ])
  }
  db.close()
  [table: table]
  }

If we want to have a date range using variables in the Report Builder the code would look like :

String startDate = '2016-08-10'
String endDate = '2016-08-20'
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
  WHERE
  t.create_date >= '${startDate} 00:00:00'
  and t.create_date <= '${endDate} 23:59:59'
  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
    ])
  }
  db.close()
  [table: table]
 }

For the Reporting Manager we will still transfer it over by take the code within the runScript method. You do not take the variables defined above runScript.

We can add variables when we create the report or after we create the report by clicking edit report.

When editing the report in the Reporting Manager, there will be a field for adding variables:

Click Add Variable and name it the exact same as the variable used in the report code. Fill in the fields as follows

Name : This is the name of the variable in the report code
Value : This is the initial value of the variable and must be populated! If a script is defined and used then the initial value/s will become that of the script
Type : This is the type of the variable. If the variable is a String use a String, if the variable is a date use the Calendar type
Script : This is a groovlet which can be used to obtain a value/s for a variable.
Use Script : This determines whether or not to use the script in the script field to populate the value of the variable.
Strict : This is a boolean which when checked means the only values that can be used for the variables are that which are initial value or if there is a script used then what the script returns.
Depends On : If a variable depends on another variable such that a network element list will only display certain elements depending on what carriers is chosen first. If no variables depend on another you do not need to enter anything here

In the case of our second example the variables will look like :

The script for statDate is return (new Date() -7).format('yyyy/M/d') and the script for endDate is return (new Date() ).format('yyyy/M/d')

When running the report a popup will appear asking to enter the values of the variables :

---

Generate a report that takes in a carrier and returns all controllers underneath that carrier

---

Variables Depending on other variables

If a variable is required to depend on another object such that a network identifier list must be generate based on the previous selected region. Let us focus on how the network identifier will be generated as we can assume that the region is just pulled form the database.

NOTE WHEN USING DEPENDS ON ENSURE THAT STRICT AND USE SCRIPT ARE LEFT UNCHECKED AS IN EXAMPLE

In order to access the region variable within the network identifier groovlet we make use of the dependents object. As region is a String we can access it in the network identifier groovlet via String region = dependents.region. The overall network identifier groovlet may then look like :

      import groovy.sql.Sql
import com.osssoftware.reporting.database.*
String region = dependents.region
String query = """
SELECT DISTINCT element.network_identifier FROM network_element element 
WHERE element.network_identifier IS NOT NULL
${
	(region.equals('All')) ? '' : "AND element.network_identifier like '${region}%'"
}
	AND element.network_identifier not in ('', 'n/a', '0')
ORDER BY element.network_identifier
"""
def list = []
db = SqlConnectionManager.instance.retrieveSql("snmp_manager")
list << "All"
db.eachRow(query) {
  list << it.network_identifier
}
return list as String[]

---

Generate a report that takes in two variables. One will be the carrier and the other will be the Ticket Visibility. Visibilities should only be generated that are underneath that carrier. Return all open tickets within the selected carrier/visibility.