Changes between Initial Version and Version 1 of SystemReports


Ignore:
Timestamp:
07/26/05 10:03:34 (19 years ago)
Author:
joe
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • SystemReports

    v1 v1  
     1= Reports = 
     2 
     3The reports module provides a simple, yet powerful reporting facility 
     4to present information about tickets in the database. 
     5 
     6Rather than have its own report definition format, the reports rely on standard SQL 
     7SELECT statements for custom report definition.  
     8 
     9A report consists of these basic parts: 
     10 * ID -- Unique (sequential) identifier  
     11 * Title  -- Descriptive title 
     12 * Description  -- A brief description of the report, in WikiFormatting text. 
     13 * Report Body -- List of results from report query, formatted according to the methods described below. 
     14 * Footer -- Links to alternative download formats for this report. 
     15 
     16 
     17== Changing Sort Order == 
     18Simple reports - ungrouped reports to be specific - can be changed to be sorted by any column simply by clicking the column header.  
     19 
     20If a column header is a hyperlink (red), click the column you would like to sort by. Clicking the same header again reverses the order. 
     21 
     22 
     23== Alternate Download Formats == 
     24Aside from the default HTML view, reports can also be exported in a number of alternate formats. 
     25At the bottom of the report page, you will find a list of available data formats. Click the desired link to  
     26download the alternate report format. 
     27 
     28=== Comma-delimited - CSV (Comma Separated Values) === 
     29Export the report as plain text, each row on its own line, columns separated by a single comma (','). 
     30'''Note:''' Column data is stripped from carriage returns, line feeds and commas to preserve structure. 
     31 
     32=== Tab-delimited === 
     33Like above, but uses tabs (\t) instead of comma. 
     34 
     35=== RSS - XML Content Syndication === 
     36All reports support syndication using XML/RSS 2.0. To subscribe to a , click the the orange 'XML' icon at the bottom of the page. See SystemRss for general information on RSS support. 
     37 
     38---- 
     39== Creating Custom Reports == 
     40 
     41''Creating a custom report requires a comfortable knowledge of SQL.'' 
     42 
     43A report is basically a single named SQL query, executed and presented by 
     44the system.  Reports can be viewed and created from a custom SQL expression directly 
     45in from the web interface. 
     46 
     47Typically, a report consists of a SELECT-expression from the 'ticket' table, 
     48using the available columns and sorting the way you want it. 
     49 
     50== Ticket columns == 
     51The ''ticket'' table has the following columns: 
     52 * id 
     53 * time 
     54 * changetime 
     55 * component 
     56 * severity   
     57 * priority  
     58 * owner 
     59 * reporter 
     60 * cc 
     61 * url 
     62 * version 
     63 * milestone 
     64 * status 
     65 * resolution 
     66 * summary 
     67 * description 
     68 
     69See SystemTickets for a detailed description of the column fields. 
     70 
     71'''all active tickets, sorted by priority and time''' 
     72 
     73'''Example:''' ''All active tickets, sorted by priority and time'' 
     74{{{ 
     75SELECT id AS ticket, status, severity, priority, owner,  
     76       time as created, summary FROM ticket  
     77  WHERE status IN ('new', 'assigned', 'reopened') 
     78  ORDER BY priority, time 
     79}}} 
     80 
     81 
     82---- 
     83 
     84 
     85== Advanced Reports: Dynamic Variables == 
     86For more flexible reports, the system supports the use of ''dynamic variables'' in report SQL statements.  
     87In short, dynamic variables are ''special'' strings that are replaced by custom data before query execution. 
     88 
     89=== Using Variables in a Query === 
     90The syntax for dynamic variables is simple, any upper case word beginning with '$' is considered a variable. 
     91 
     92Example: 
     93{{{ 
     94SELECT id AS ticket,summary FROM ticket WHERE priority='$PRIORITY' 
     95}}} 
     96 
     97To assign a value to $PRIORITY when viewing the report, you must define it as an argument in the report URL, leaving out the the leading '$'. 
     98 
     99Example: 
     100{{{ 
     101 http://vso1.nascom.nasa.gov/docs/reports/14?PRIORITY=high 
     102}}} 
     103 
     104 
     105=== Special/Constant Variables === 
     106There is one ''magic'' dynamic variable to allow practical reports, its value automatically set without having to change the URL.  
     107 
     108 * $USER -- Username of logged in user. 
     109 
     110Example (''List all tickets assigned to me''): 
     111{{{ 
     112SELECT id AS ticket,summary FROM ticket WHERE owner='$USER' 
     113}}} 
     114 
     115 
     116---- 
     117 
     118 
     119== Advanced Reports: Custom Formatting == 
     120The system is also capable of more advanced reports, including custom layouts, 
     121result grouping and user-defined CSS styles. To create such reports, we'll use 
     122specialized SQL statements to control the output of the report engine. 
     123 
     124== Special Columns == 
     125To format reports, SystemReports looks for 'magic' column names in the query 
     126result. These 'magic' names are processed and affect the layout and style of the  
     127final report. 
     128 
     129=== Automatically formatted columns === 
     130 * '''ticket''' -- Ticket ID number. Becomes a hyperlink to that ticket.  
     131 * '''created, modified, date, time''' -- Format cell as a date and/or time. 
     132 * '''description''' -- Ticket description field, parsed through the wiki engine. 
     133 
     134'''Example:''' 
     135{{{ 
     136SELECT id as ticket, created, status, summary FROM ticket  
     137}}} 
     138 
     139=== Custom formatting columns === 
     140Columns whose names begin and end with 2 underscores (Example: '''_''''''_color_''''''_''') are 
     141assumed to be ''formatting hints'', affecting the appearance of the row. 
     142  
     143 * '''_''''''_group_''''''_''' -- Group results based on values in this column. Each group will have its own header and table. 
     144 * '''_''''''_color_''''''_''' -- Should be a numeric value ranging from 1 to 5 to select a pre-defined row color. Typically used to color rows by issue priority. 
     145 * '''_''''''_style_''''''_''' -- A custom CSS style expression to use for the current row.  
     146 
     147'''Example:''' ''List active tickets, grouped by milestone, colored by priority'' 
     148{{{ 
     149SELECT p.value AS __color__, 
     150     t.milestone AS __group__, 
     151     (CASE owner WHEN 'daniel' THEN 'font-weight: bold; background: red;' ELSE '' END) AS __style__, 
     152       t.id AS ticket, summary 
     153  FROM ticket t,enum p 
     154  WHERE t.status IN ('new', 'assigned', 'reopened')  
     155    AND p.name=t.priority AND p.type='priority' 
     156  ORDER BY t.milestone, p.value, t.severity, t.time 
     157}}} 
     158 
     159'''Note:''' A table join is used to match ''ticket'' priorities with their 
     160numeric representation from the ''enum'' table. 
     161 
     162=== Changing layout of report rows === 
     163By default, all columns on each row are display on a single row in the HTML 
     164report, possibly formatted according to the descriptions above. However, it's 
     165also possible to create multi-line report entries. 
     166 
     167 * '''column_''' -- ''Break row after this''. By appending an underscore ('_') to the column name, the remaining columns will be be continued on a second line. 
     168 
     169 * '''_column_''' -- ''Full row''. By adding an underscore ('_') both at the beginning and the end of a column name, the data will be shown on a separate row. 
     170 
     171 * '''_column'''  --  ''Hide data''. Prepending an underscore ('_') to a column name instructs the system to hide the contents from the HTML output. This is useful for information to be visible only if downloaded in other formats (like CSV or RSS/XML). 
     172 
     173'''Example:''' ''List active tickets, grouped by milestone, colored by priority, with  description and multi-line layout'' 
     174 
     175{{{ 
     176SELECT p.value AS __color__, 
     177       t.milestone AS __group__, 
     178       (CASE owner  
     179          WHEN 'daniel' THEN 'font-weight: bold; background: red;'  
     180          ELSE '' END) AS __style__, 
     181       t.id AS ticket, summary AS summary_,             -- ## Break line here 
     182       component,version, severity, milestone, status, owner, 
     183       time AS created, changetime AS modified,         -- ## Dates are formatted 
     184       description AS _description_,                    -- ## Uses a full row 
     185       changetime AS _changetime, reporter AS _reporter -- ## Hidden from HTML output 
     186  FROM ticket t,enum p 
     187  WHERE t.status IN ('new', 'assigned', 'reopened')  
     188    AND p.name=t.priority AND p.type='priority' 
     189  ORDER BY t.milestone, p.value, t.severity, t.time 
     190}}} 
     191 
     192 
     193---- 
     194See also: SystemTickets, SystemQuery, SystemGuide