Internal Errigal Collaboration Wiki

User Tools

Site Tools

Trace:
Writing /app/www/public/data/meta/onboarding/reportingmanager/use_of_report_variables.meta failed
onboarding:reportingmanager:use_of_report_variables

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
onboarding:reportingmanager:use_of_report_variables [2016/08/15 14:27] – scottyonboarding:reportingmanager:use_of_report_variables [2021/06/25 10:09] (current) – external edit 127.0.0.1
Line 1: Line 1:
 +====== Use of report variables ======
  
 +Author: Colm Carew
 +
 +===== Secure Variables =====
 +
 +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!
 +
 +
 +----
 +
 +
 +===== Variables =====
 +
 +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:
 +
 +{{:onboarding:reportingmanager:novariables.png|}}
 +
 +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 :
 +{{:onboarding:reportingmanager:variables.png|}}
 +
 +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 :
 +
 +{{:onboarding:reportingmanager:varpopup.png|}}
 +
 +
 +----
 +
 +
 +===== Assessment =====
 +
 +
 +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
 +
 +{{:onboarding:reportingmanager:dependson.png|}}
 +
 +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[]
 +
 +
 +----
 +
 +
 +===== Assessment =====
 +
 +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.

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki