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 page¹⁾ - 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 Excel²⁾ / 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.

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 report³⁾

{Type}	1 : HTML
“	2 : DocuWiki
”	4 : MoinWiki
“	8 : RSS
”	16: Excel

PrintReport	If set, header and links will be hidden when printing the page.

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.

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.

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!

N⁴⁾	{Link}

{Link}	[[portal:xyz]]⁵⁾	Wiki Link

A⁶⁾	{Link}

{Link}	[[portal:xyz]]⁷⁾	Wiki Link

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

I	{Report Name}

{Report Name}	ABC123	{Name} of command script

Add a comment

C

	This line is ignored

L	{Line}	{Type}

{Line}	~~NOCACHE~~⁸⁾ ABC123 \n	Text Note: No CR will be added - use \n for this
{Type}	1	Restrict this line to a certain report {Type}

H	{Header}	{Level}

{Header}	ABC123	Header text

{Level}	1	Header level 1 .. 5 (empty = 1)

T	{Opt},{Opt..N}	{SQL}\⁹⁾{Cmd-Row}¹⁰⁾¹¹⁾
		{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 title¹³⁾	1
H	Channel description	2
H	Channel link	3
H	Channel managingEditor	4
H	(Channel pubDate)¹⁴⁾	5

RSS Item Description:

T	SELECT …	..
	Title = Item title¹⁵⁾	..
	Link = Item link¹⁶⁾	..
	Date = Item date¹⁷⁾	..
	Descr1 = Item description 1	..
	…
	DescrN = Item description N	..

An example you can find in the Standard Report Effort Allocation Week Status.

G	{Opt},{Opt..N}/{Para1}/{Para..N}	{SQL}\¹⁸⁾{Cmd-Row}¹⁹⁾²⁰⁾	{Cmd1}²¹⁾	{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;²²⁾..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

P	{Opt},{Opt..N}	{Cmd-Row}={Cmd};{Cmd..N}²³⁾²⁴⁾

{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

{cmd}

({Label}|²⁵⁾{Variable}){Operator}({Variable}|{Constant}|{Function})|_²⁶⁾{Modifier} _or

({Label}|{Variable}){Compare}({Variable}|{Constant})_({Variable}|{Constant}|SKIP²⁷⁾)²⁸⁾_({Variable}|{Constant}|SKIP²⁹⁾)³⁰⁾

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 YEAR³¹⁾

”

CAPACITY(EID, TID, PID, GROUP, MODE)

Return resource capacity for engineer EID, task TID, program PID, group GROUP³²⁾ 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.³³⁾

“

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 DATE³⁴⁾

”

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 DATE³⁵⁾

”

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 YEAR³⁶⁾

{Modifier}

ALARM(LIMIT,THRESHOLD)³⁷⁾

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.:

Example LIMIT=10, THRESHOLD=3
VALUE < 7	VALUE 7 ... 13	VALUE > 13
Example LIMIT=10, THRESHOLD=-3
VALUE < 7	VALUE 7 ... 13	VALUE > 13

Example LIMIT='ABC', THRESHOLD='<'
VALUE <= 'ABC'	VALUE > 'ABC'
Example LIMIT='ABC', THRESHOLD='>'
VALUE >= 'ABC'	VALUE < 'ABC'

”

AVE

Add a column average summary in last row of table

“

CENTER³⁸⁾

Center table column

”

COLOR(COL)³⁹⁾

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 variable⁴⁰⁾

”

DATE(FORMAT)

Format table column date with FORMAT string⁴¹⁾

“

FAT⁴²⁾

Format table column text in bold

”

FORMAT(FORMAT)⁴³⁾

Format table column text with FORMAT string⁴⁴⁾

“

ITALIC⁴⁵⁾

Format table column text in italic

”

LFORMAT

Format table column text according to Wiki link conventions

“

LOWER

Format table column text in lower letters

”

RIGHT⁴⁶⁾

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

“

ULINE⁴⁷⁾

Underline table column text

”

UPPER

Format table column text in upper letters

?: 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

¹⁾

PHP System admin must setup this to modify directly DocuWiki files

²⁾

All third party trademarks are the property of their respective owners

³⁾

Use '+' for for space characters

⁴⁾ , ⁶⁾

Only needed for DocuWiki pages

⁵⁾ , ⁷⁾

The Wiki page must be created manually in advance

⁸⁾

Should be used when graphics are generated

⁹⁾ , ¹⁸⁾

Optional and SQL / Cmd-Row could be combined in arbitrary order

¹⁰⁾ , ¹⁹⁾ , ²³⁾

Cmd-Row is equivalent to one data row

¹¹⁾ , ²⁰⁾ , ²⁴⁾

Discrete command data set section

¹²⁾ , ²¹⁾

Command script section

¹³⁾ , ¹⁵⁾ , ¹⁶⁾

Mandatory item

¹⁴⁾

Date is generated automatically

¹⁷⁾

Must be RFC 2822 formated

²²⁾

';' is translated to '|'

²⁵⁾

'(|)' means either or

²⁶⁾

'_' is a space character

²⁷⁾ , ²⁹⁾

skips the current data set value

²⁸⁾

used on true condition

³⁰⁾

used on false condition

³¹⁾ , ³²⁾ , ³³⁾ , ³⁴⁾ , ³⁵⁾ , ³⁶⁾

use '' as empty parameter

³⁷⁾ , ³⁸⁾ , ³⁹⁾ , ⁴²⁾ , ⁴³⁾ , ⁴⁵⁾ , ⁴⁶⁾ , ⁴⁷⁾

Works only for table command section

⁴⁰⁾

CUM does not work correctly when using the table S option

⁴¹⁾

Format string is according to PHP date() syntax

⁴⁴⁾

Format string is according to PHP printf() syntax

ProjectTRACKER Help - Reports - Configuration

Report HTML command line

Command script

Command script placeholders

Activate debug mode

Open a new Wiki page

Append to a Wiki page

Execute command script condition

Include command script

Add a comment

Add a line

Add a headline

Add a table

RSS Table

Add a graphic

Add a plot

Command parameter syntax

Command script examples

?: construct

If / Elseif construct

Report Table Example

Report Graphic Example