Report Writing Gotchas

Author: Colm Carew
Updated: Loughlin Moore

• Include the time in tickets when specifying dates eg: hh:mm:ss , as you may miss tickets.

• Always declare queries as a string and not as def, this prevents error messages in the log.

• When joining forms, be careful of multiple forms of the same name being attached to a ticket - this can cause duplicate entries on the report. :

to join a form you first join in ticket_change join ticket_change tc on tc.ticket_id = t.id and tc.contents like 'Your Form%' This leads to problems with multiple forms of same names so try this : join ticket_change tc on tc.id = (select max(id) from ticket_change where ticket_id = t.id and contents like 'Your Form%') When joining in a node ie: a network_element, you need to specify its parent id, or else you will end up with multiple rows with the same node name.

• When converting time in mysql, there are some date functions which have a max limit on their value of output. When doing calculations such as duration's, use the UNIX_TIMESTAMP() function in MySql.

• AVOID DISTINCT! - this can mask a bad/non optimal query and increase the runt ime of the query.

• Never have more then 2 or 3 “group by” as this can cause significant overhead on large data sets.

• When wanting to have a “where” clause on an aliased column name use “having” at the end of the query, ie below “order by”.

• Avoid “UNION” - these are hard to maintain.

• Always use the ticketer.district table and not the snmp_manager.district table.

• If possible and optimal, try keep the report in the one query - this is cleaner to maintain.

• When declaring the data source, check in the reporting manager data source button what database name refers to what data source.

• Fully qualify everything if using more than one database in the report, ie ticketer.ticket and snmp_manager.network_element not ticket and network_element.

• Always make back ups before editing - of all versions of report, qa, prod, local etc.. and label all accordingly.

• Be aware that software versions are not reliable for joining and the data is not always maintained.

• Sometimes the issue is not with the report, it can be with the form it is pulling from.

• Always include db.close

• When naming a column always do it with camel cases and no spaces, spaces may work in the report builder tool but will not work in the reporting manager and cause less results in the returned table than expected

• Secure variables: [“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!

• Don't use remote_ticket table to be the join table between snmp_manager and ticketer, using general_trap_summary and f_snmp_trap please.

• When using group by, errors can occur if all columns that are not aggregated are not included in the group by i.e

  select ne.technology, ne.link_checks count(*) from network_element.ne group by ne.technology;

  In this example, ne.link_checks should also be included in the group by clause.

Please add to this list as you discover a new gotcha!

ScottyPro Reports FYI