ProjectTRACKER Help - Reports - Configuration
Note: Only users with “Report” access rights can see and use the reports configuration functionality!
ProjectTRACKER can create reports in the following formats:
HTML | Report in HTML format embedded in ProjectTRACKER |
DocuWiki | Report will be generated directly into a DokuWiki page1) - see http://www.dokuwiki.org |
MoinWiki | Report will be generated in MoinWiki format and can be copy pasted into a MoinWiki page - see http://moinmo.in/ |
RSS | Report will be generated in RSS XML format - see http://www.rssboard.org/ |
Excel | Report will be generated in MS Excel2) / CSV format |
A normal user can only call these reports - authorized users can also create reports. The report creation is done via a command script, which is described in the following sections.
Report HTML command line
For report automatizing individual reports could be called / generated via HTML parameters:
http://.../Report.php?ReportName={Name}&ReportType={Type}&PrintReport=1
Parameters | ||
---|---|---|
{Name} | ProjectTRACKER name of the report3) | |
{Type} | 1 : HTML | |
“ | 2 : DocuWiki | |
” | 4 : MoinWiki | |
“ | 8 : RSS | |
” | 16: Excel | |
PrintReport | If set, header and links will be hidden when printing the page. |
Command script
Reports a configured via a command script in the format:
| {Command1} | {Para1} | {Para2} | ... | {ParaN} | | {Command2} | {Para1} | {Para2} | ... | {ParaN} | ...
Lines could be also formatted by using continuation lines with '..', e.g.:
| {Command2} |.. {Para1} |.. {Para2} |.. {Para3} |
Note: All spaces after '..' up to the next command in the next line are deleted. Don't forget to add at least one space character in front of '..' when needed!
Comments /* */ within command parameter cells are also allowed, e.g.
| {Para} /* Comment */ |
Possible commands and parameters are described in detail in the following chapters.
Command script placeholders
The following placeholders in the command script section will be replaced with the below defined replacement strings or the current settings of the Report Parameters.
Placeholder | Description |
---|---|
_SEP | Is replaced with separator chars. This is depending on report {Type}. |
_LNK | Is replaced with link chars. This is depending on report {Type}. |
_RET | Is replaced with \n. |
_QUOT | Is replaced with “. |
_HTMLL | Wiki HTML format start. This is depending on report {Type}. |
_HTMLR | Wiki HTML format end. This is depending on report {Type}. |
_RTYPE | Returns report {Type}. |
##CUSER## | Current user ID. |
##CUSERN## | Current user Name. |
##CDAY## | Current day in the format YYYYMMDD. |
##CDAYF## | Formatted current day. |
##CYEAR## | Current year - four digits. |
##CYEAR2## | Current year - two digits. |
##DYEAR## | Default year - four digits. |
##DYEAR2## | Default year - two digits. |
##DWEEK## | Default week. |
##DWEEKF## | Formatted default week. |
##DWEEKR## | Default week range. |
##DWEEKRF## | Formatted default week range. |
##DSWEEK## | Default scrum cycle. |
##DSWEEKF## | Formatted default scrum cycle. |
##DUSER## | Default user ID. In case no user is selected, for normal users always the user itself will be returned - otherwise the ProjectTRACKER admin user is returned. |
##DUSERN## | Default user name. |
##DTASK## | Default Task ID. |
##DTASKN## | Default Task Name. |
##DPROG## | Default Program ID. |
##DPROGN## | Default Program Name. |
##DTGROUP## | Default Task group. |
##DEGROUP## | Default User / Resource group. |
##INTPRIO## | Is replaced with a SQL IN statement 'TaskPrio IN (…)' in case a default task prio is set, otherwise '1' is returned. |
In case the CALL function is used, the {Variables} values from the calling script can be also used in the command script section of the called report script via:
##{Variable Name}##
Variable names from calling scripts can be used directly (means without the '##' markers) for the Cmd-Row discrete command data set section. Details on {Variables} and the CALL function see Command parameter syntax.
The usage of Placeholders and ##{Variables}## defined in advance works also in general for the SQL discrete command data set section. Note: In case the variable consists of several rows the last row will be taken.
For details on the SQL / Cmd-Row discrete command data set section see Add a Table, Add a Graphic and Add a Plot.
Activate debug mode
D |
---|
Shows report debug information in an extra page - this works only when the debug mode of the ProjectTRACKER is activated by the ProjectTRACKER PHP admin!
Open a new Wiki page
Append to a Wiki page
Execute command script condition
E | {Cmd};{Cmd..N} | {Message} | |||
---|---|---|---|---|---|
{Cmd} | see Command parameter syntax | ||||
{Message} | Message in report if the first {Cmd} sequence is zero or empty. Note: No CR will be added - use \n for this. In case of a zero or empty value the command script execution is also stopped |
Include command script
I | {Report Name} | ||
---|---|---|---|
{Report Name} | ABC123 | {Name} of command script |
Add a comment
C | ||
---|---|---|
This line is ignored |
Add a line
L | {Line} | {Type} | ||
---|---|---|---|---|
{Line} | ~~NOCACHE~~8) ABC123 \n | Text Note: No CR will be added - use \n for this | ||
{Type} | 1 | Restrict this line to a certain report {Type} |
Add a headline
H | {Header} | {Level} | |||
---|---|---|---|---|---|
{Header} | ABC123 | Header text | |||
{Level} | 1 | Header level 1 .. 5 (empty = 1) |
Add a table
T | {Opt},{Opt..N} | {SQL}\9){Cmd-Row}10)11) | {Cmd1}12) | {Cmd..N} | |
---|---|---|---|---|---|
{Cmd-Row}={Cmd};{Cmd..N} | |||||
{Opt} | COLSUP=ABC123 | Suppress column with name ABC123 (the option can be repeated) | |||
” | G=123 | Group the table with column G (default=0) | |||
“ | H | Generate table header using the label / variable names | |||
” | L=123 | Show first L lines only | |||
“ | N | Do not use Wiki or HTML column separators | |||
” | S=123 | Sort the table by column S | |||
“ | T | Transpose column with row data (labels will be named: _1 .. _N) | |||
” | Z=ABC123 | Fill empty SQL table data with Z (default=null) | |||
{SQL} | SELECT FIELD1 FROM TABLE AS LABEL1, FIELD..N FROM TABLE AS LABEL..N .. | Valid SQL statement, the “AS” alias will be used as label name | |||
{Cmd} | see Command parameter syntax |
RSS Table
To generate RSS reports you need to follow a special header and table format:
RSS Channel Description:
H | Channel title13) | 1 |
H | Channel description | 2 |
H | Channel link | 3 |
H | Channel managingEditor | 4 |
H | (Channel pubDate)14) | 5 |
RSS Item Description:
T | SELECT … | .. | |
Title = Item title15) | .. | ||
Link = Item link16) | .. | ||
Date = Item date17) | .. | ||
Descr1 = Item description 1 | .. | ||
… | |||
DescrN = Item description N | .. |
An example you can find in the Standard Report Effort Allocation Week Status.
Add a graphic
G | {Opt},{Opt..N}/{Para1}/{Para..N} | {SQL}\18){Cmd-Row}19)20) | {Cmd1}21) | {Cmd..N} | ||
---|---|---|---|---|---|---|
{Cmd-Row}={Cmd};{Cmd..N} | ||||||
{Opt} | COL=123 | Start graphic output with GValues column COL | ||||
“ | COLEND=123 | Do graphic output until GValues column COLEND | ||||
” | G | Add grid lines | ||||
“ | MIN=123 | Y axis scale starts with MIN | ||||
” | MAX=123 | Y axis scale ends with MAX | ||||
“ | NS | No Y scaling - means scale is from 0 .. 100 | ||||
” | ROW=123 | Start graphic output with GValues row ROW | ||||
“ | ROWEND=123 | Do graphic output until GValues row ROWEND | ||||
” | T | Transpose X with Y axis data (labels will be named: _1 .. _N) | ||||
“ | X0 | X axis scale starts with zero | ||||
” | Y0 | Y axis scale starts with zero | ||||
“ | XL=ABC123 | X axis lable | ||||
” | YL=ABC123 | Y axis lable | ||||
“ | Z=ABC123 | Fill empty SQL table data with Z (default=null) | ||||
{Para} | Use Google Chart API syntax: | See Google Chart Image API (Google Chart Javascript API) | ||||
” | cht=lc | A line chart, data points are spaced evenly along the X axis | ||||
“ | cht=lxy | A line chart with X, Y axis data points | ||||
” | cht=ls | Sparkline | ||||
“ | cht=bhs | Horizontal bar chart respectively - multiple are summed | ||||
” | cht=bvs | Vertical bar chart respectively multiples are summed | ||||
“ | cht=bhg | Horizontal bar chart respectively - multiple are grouped | ||||
” | cht=bvg | Vertical bar chart respectively - multiples are grouped | ||||
“ | cht=p | Two dimensional pie chart | ||||
” | cht=p3 | Three dimensional pie chart | ||||
“ | cht=gom | Google meter | ||||
” | chtt=ABC123 | Chart title (for space use '+' character) | ||||
“ | chs=400×200 | Chart size, generates e.g. 400 pixels wide and 200 pixels high. Note: The image size (product) cannot be bigger than 300.000 pixel! | ||||
” | chco=00ff00,..N | Chart line / fill colors | ||||
“ | chdl=ABC123;22)..N | Chart agenda | ||||
{SQL} | SELECT FIELD1 FROM TABLE AS LABEL1, FIELD..N FROM TABLE AS LABEL..N .. | Valid SQL statement, the “AS” alias will be used as label name | ||||
{Cmd} | see Command parameter syntax |
Add a plot
P | {Opt},{Opt..N} | {Cmd-Row}={Cmd};{Cmd..N}23)24) | |
---|---|---|---|
{Opt} | TYPE={Plottype} | Plot types are: | |
” | BURN-DOWN-EFFORT : Burn down / up effort chart | ||
“ | BURN-DOWN-SP : Burn down / up story point chart | ||
” | STATUS-SUMMARY : Task status summary chart | ||
“ | XS=123 | X axis size of plot image (default = 800) | |
” | YS=123 | Y axis size of plot image (default = 400) | |
“ | HL=123 | History length for plots with time axis (default shows history back for 12 weeks / 84 days) | |
{Cmd} | Mandatory variable to set is 'PID' (program id), optional variables are 'GROUP' (task group) and 'DATE' (plot date) - see Command parameter syntax |
Command parameter syntax
{cmd} | ({Label}|25){Variable}){Operator}({Variable}|{Constant}|{Function})|_26){Modifier} or | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
({Label}|{Variable}){Compare}({Variable}|{Constant})_({Variable}|{Constant}|SKIP27))28)_({Variable}|{Constant}|SKIP29))30) | ||||||||||||||||||||||||
The command interpreter uses a very basic left to right non-recursive parser: • The parser does not know anything about mathematics or operator precedence. Formulas and operators are evaluated from left to right without any precedence • ',' '(' ')' and the space character are just separators and can be exchanged for readability • Text literals are defined by enclosing the text with ' or ”, e.g. “Hello - it's me” |
||||||||||||||||||||||||
{Label} | A label defined via the “AS” alias in the SQL statement | |||||||||||||||||||||||
{Variable} | A variable with a naming convention matching the regular expression: ^[a-zA-Z_][a-zA-Z0-9_%]*$ A variable / label starting with '_' is not shown in the table / graphic! | |||||||||||||||||||||||
{Constant} | A number or a text string quoted with ' or “ | |||||||||||||||||||||||
{Operator} | + | Add | ||||||||||||||||||||||
” | @ | Add (for a date + a day offset) Note: The new date is based on a working day calculation, means adding 5 days will move the date to next week same day | ||||||||||||||||||||||
“ | - | Minus | ||||||||||||||||||||||
” | / | Divison | ||||||||||||||||||||||
“ | * | Multiplication | ||||||||||||||||||||||
” | . | Concatenation | ||||||||||||||||||||||
“ | = | Assignment | ||||||||||||||||||||||
{Compare} | == | Equal. Note: Be carful when you compare values - there might be control characters (e.g. from a CALL) at the end of the string you want to compare! | ||||||||||||||||||||||
” | < | Less then | ||||||||||||||||||||||
“ | > | Greater then | ||||||||||||||||||||||
” | <= | Less equal then | ||||||||||||||||||||||
“ | >= | Greater equal then | ||||||||||||||||||||||
{Function} | ADAYS(EID, TID, GROUP, YEAR) | Return allocated / planned days for engineer EID, task TID and group GROUP for YEAR31) | ||||||||||||||||||||||
” | CAPACITY(EID, TID, PID, GROUP, MODE) | Return resource capacity for engineer EID, task TID, program PID, group GROUP32) and capacity MODE 0: Perc. allocated, +1: Eng. efficiency, +2: Holidays, +4: Absence profile, +8: Return allocated head count, +16: Use planned HC | ||||||||||||||||||||||
“ | CALL(LINK) | Call other report script and return result. LINK is the report {Name}. Note: In case the result consists of several lines the lines a concatenated | ||||||||||||||||||||||
” | CDATE(TID, MODE) | Return measured start date (MODE='S'), end date (MODE='E') for task TID or current date(MODE='C') or current week date (MODE={Number}) with {Number}: 0 current week, 1: next week, -1: previous week etc.33) | ||||||||||||||||||||||
“ | DHOURS(EID) | Return working hours per day for engineer with EID | ||||||||||||||||||||||
” | MDAYS(YEAR, MONTH) | Return days per month for YEAR (4 digits) and MONTH | ||||||||||||||||||||||
“ | PDAYS(TID, PID, GROUP, DATE) | Return planned work days for task TID, program PID, group GROUP up to DATE34) | ||||||||||||||||||||||
” | REGEX(TEXT, REGEX, INDEX, SEP) | Return text based on PHP regular expression function preg_match_all from TEXT, with REGEX regular expression, from result INDEX, separated by SEP - see example usage in Standard Report Show Reports | ||||||||||||||||||||||
“ | RDAYS(EID, TID, PID, GROUP, DATE) | Return task effort remaining in days for engineer EID, task TID, program PID, group GROUP up to DATE35) | ||||||||||||||||||||||
” | SUBSTR(STR, START, LEN) | Return substring of STR with START and LEN | ||||||||||||||||||||||
“ | WDAYS(EID, TID, PID, GROUP, YEAR) | Return task effort done in days for engineer EID, task TID, program PID and group GROUP for YEAR36) | ||||||||||||||||||||||
{Modifier} | ALARM(LIMIT,THRESHOLD)37) | Change table column colour according to alarm LIMIT and THRESHOLD, THRESHOLD can be postive or negative number or <, > for comparison of non-numeric values, e.g.:
| ||||||||||||||||||||||
” | AVE | Add a column average summary in last row of table | ||||||||||||||||||||||
“ | CENTER38) | Center table column | ||||||||||||||||||||||
” | COLOR(COL)39) | Change table column text / background colour Colour values are 'TGREEN', 'TYELLOW', 'TRED', 'BGREEN', 'BYELLOW', 'BRED' | ||||||||||||||||||||||
“ | CUM | Generate a cummulative summary (sum of previous values) of a label or variable40) | ||||||||||||||||||||||
” | DATE(FORMAT) | Format table column date with FORMAT string41) | ||||||||||||||||||||||
“ | FAT42) | Format table column text in bold | ||||||||||||||||||||||
” | FORMAT(FORMAT)43) | Format table column text with FORMAT string44) | ||||||||||||||||||||||
“ | ITALIC45) | Format table column text in italic | ||||||||||||||||||||||
” | LFORMAT | Format table column text according to Wiki link conventions | ||||||||||||||||||||||
“ | LOWER | Format table column text in lower letters | ||||||||||||||||||||||
” | RIGHT46) | Right format table column | ||||||||||||||||||||||
“ | ROUND(DIGIT) | Round table column number to a precision of DIGITs | ||||||||||||||||||||||
” | SUM | Add a column summary in last row of table | ||||||||||||||||||||||
“ | ULINE47) | Underline table column text | ||||||||||||||||||||||
” | UPPER | Format table column text in upper letters |
Command script examples
?: construct
The PHP expression:
$Parameter = $Test > 10 ? 1 : 0
could be implemented with the ProjectTRACKER command script as:
... | Parameter = Test > 10 1 0 | ...
Note: Don't use Parameter as true or false condition, as the value will be set to Test first. This is because of serial parser execution ((A = B) < C)!
In case the value of Parameter shall be checked and set to 0 or 1 itself, this can be shortened to:
... | Parameter > 10 1 0 | ...
If / Elseif construct
The PHP expression:
if (DATE('Y', $_DATE) < ##DYEAR##) $Week = 0; elseif (DATE('Y', $_DATE) > ##DYEAR##) $Week = 53; else $Week = DATE('W', $_DATE);
with
- _DATE : Current date
- Week : Current week number
could be implemented with the ProjectTRACKER command script as:
... | _Y=_DATE DATE('Y') |.. _Week=_DATE DATE('W') |.. _Week2= _Y < ##DYEAR## 0 _Week |.. Week= _Y > ##DYEAR## 53 _Week2 | ...
Note: _Week2 is needed as otherwise the parser would destroy _Week with the _Y value. This is because of serial parser execution ((A = B) < C)!
Report Table Example
|E|Check=##DPROG##|<strong>Program not set!</strong> | |H|##DPROGN## Program Task Status | |T|H|SELECT Task.TaskGroup AS ID, Task.TaskName AS Task, Task.TaskEffortPlanned AS Plan, .. Task.TaskID AS _TID FROM Program INNER JOIN Task ON Program.ProgramID = Task.ProgramID .. WHERE Program.ProgramID=##DPROG## AND Task.TaskGroup LIKE 'E%' AND Task.TaskActive=True AND .. Program.ProgramActive=True AND Task.TaskSumFlag=False ORDER BY Task.TaskGroup |.. Plan RIGHT FORMAT('%.1f') SUM |.. Done = WDAYS('',_TID,'','','') RIGHT FORMAT('%.1f') SUM |.. Rem = RDAYS('',_TID,'','','') RIGHT FORMAT('%.1f') SUM |.. _Total = Done + Rem |.. Rem% = Rem / _Total * 100 RIGHT ALARM(50,30) FORMAT('%.0f%%') AVE |.. Plan% = _Total / Plan * 100 RIGHT ALARM(100,0) FORMAT('%.0f%%') AVE |
Delivers depending on the default program setting e.g. the following report:
TEST Program Task Status
ID | Task | Plan | Done | Rem | Rem% | Plan% | |
---|---|---|---|---|---|---|---|
EX811 | test1 | 66.0 | 19.7 | 54.0 | 73% | 112% | |
EX812 | task2 | 22.0 | 10.3 | 30.0 | 74% | 183% | |
EX813 | task3 | ||||||
88.0 | 30.0 | 84.0 | 30% | 59% |
Report Graphic Example
|E|Check=##DPROG##|<strong>Program not set!</strong>| |E|Check=##DYEAR##|<strong>Year not set!</strong>| |N|[[portal:program-report]]| |L|~~NOCACHE~~|2|Switch off DocuWiki cache!| |H|Program Effort Curve ##DPROGN## ##DYEAR##| |G|Z=0,COL=3,G,XL=Week,YL=PD/cht=lxy/chs=600x300/chco=ff0000,00ff00,ff8000/chdl=Done;Plan;Estimated+Total/chtt=Program+Effort+Curve |.. SELECT TaskEffort.TaskEffortDate AS _DATE, Sum(TaskEffortCurrent/LocationWorkTime) AS _EFFORT .. FROM (Program INNER JOIN Task ON Program.ProgramID = Task.ProgramID) INNER JOIN (Location .. INNER JOIN (Engineer INNER JOIN TaskEffort ON Engineer.EngineerID = TaskEffort.EngineerID) ON .. Location.LocationID = Engineer.LocationID) ON Task.TaskID = TaskEffort.TaskID .. WHERE Task.TaskActive=True AND Program.ProgramActive=True AND Task.TaskGroup LIKE 'E%' AND .. Program.ProgramID=##DPROG## GROUP BY TaskEffort.TaskEffortDate .. ORDER BY TaskEffort.TaskEffortDate |.. _Y=_DATE DATE('Y') |.. _Y < ##DYEAR## SKIP _Y |.. _Y > ##DYEAR## SKIP _Y |.. Week=_DATE DATE('W') |.. Done=_EFFORT CUM ROUND 1 |.. Plan=PDAYS('',##DPROG##,'E%',_DATE) ROUND 1 |.. _REM=RDAYS('','',##DPROG##,'E%',_DATE) |.. Total=_REM+Done ROUND 1 |
Delivers depending on the default program and year setting the following report:
Program Effort Curve Example Project 2009