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:

HTMLReport in HTML format embedded in ProjectTRACKER
DocuWikiReport will be generated directly into a DokuWiki page1) - see http://www.dokuwiki.org
MoinWikiReport will be generated in MoinWiki format and can be copy pasted into a MoinWiki page - see http://moinmo.in/
RSSReport will be generated in RSS XML format - see http://www.rssboard.org/
ExcelReport 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
PrintReportIf 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.

PlaceholderDescription
_SEPIs replaced with separator chars. This is depending on report {Type}.
_LNKIs replaced with link chars. This is depending on report {Type}.
_RETIs replaced with \n.
_QUOTIs replaced with ”.
_HTMLLWiki HTML format start. This is depending on report {Type}.
_HTMLRWiki HTML format end. This is depending on report {Type}.
_RTYPEReturns 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

N4){Link}
{Link} [[portal:xyz]]5)Wiki Link

Append to a Wiki page

A6){Link}
{Link} [[portal:xyz]]7)Wiki Link

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 \nText Note: No CR will be added - use \n for this
{Type} 1Restrict this line to a certain report {Type}

Add a headline

H{Header}{Level}
{Header} ABC123Header text
{Level} 1Header 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=ABC123Suppress column with name ABC123 (the option can be repeated)
G=123Group the table with column G (default=0)
HGenerate table header using the label / variable names
L=123Show first L lines only
NDo not use Wiki or HTML column separators
S=123Sort the table by column S
TTranspose column with row data (labels will be named: _1 .. _N)
Z=ABC123Fill 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:

HChannel title13)1
HChannel description2
HChannel link3
HChannel managingEditor4
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=123Start graphic output with GValues column COL
COLEND=123Do graphic output until GValues column COLEND
GAdd grid lines
MIN=123Y axis scale starts with MIN
MAX=123Y axis scale ends with MAX
NSNo Y scaling - means scale is from 0 .. 100
ROW=123Start graphic output with GValues row ROW
ROWEND=123Do graphic output until GValues row ROWEND
TTranspose X with Y axis data (labels will be named: _1 .. _N)
X0X axis scale starts with zero
Y0Y axis scale starts with zero
XL=ABC123X axis lable
YL=ABC123Y axis lable
Z=ABC123Fill empty SQL table data with Z (default=null)
{Para} Use Google Chart API syntax: See Google Chart Image API (Google Chart Javascript API)
cht=lcA line chart, data points are spaced evenly along the X axis
cht=lxyA line chart with X, Y axis data points
cht=lsSparkline
cht=bhsHorizontal bar chart respectively - multiple are summed
cht=bvsVertical bar chart respectively multiples are summed
cht=bhgHorizontal bar chart respectively - multiple are grouped
cht=bvgVertical bar chart respectively - multiples are grouped
cht=pTwo dimensional pie chart
cht=p3Three dimensional pie chart
cht=gomGoogle meter
chtt=ABC123Chart title (for space use '+' character)
chs=400×200Chart 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,..NChart line / fill colors
chdl=ABC123;22)..NChart 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=123X axis size of plot image (default = 800)
YS=123Y axis size of plot image (default = 400)
HL=123History 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.:
 Example LIMIT=10, THRESHOLD=3
 VALUE < 7VALUE 7 ... 13 VALUE > 13 
 Example LIMIT=10, THRESHOLD=-3
 VALUE < 7VALUE 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
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

IDTaskPlanDoneRemRem%Plan%
EX811test1 66.0 19.7 54.0 73% 112%
EX812task2 22.0 10.3 30.0 74% 183%
EX813task3
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

1) PHP System admin must setup this to modify directly DocuWiki files
2) All third party trademarks are the property of their respective owners
3) Use '+' for for space characters
4) , 6) Only needed for DocuWiki pages
5) , 7) The Wiki page must be created manually in advance
8) Should be used when graphics are generated
9) , 18) Optional and SQL / Cmd-Row could be combined in arbitrary order
10) , 19) , 23) Cmd-Row is equivalent to one data row
11) , 20) , 24) Discrete command data set section
12) , 21) Command script section
13) , 15) , 16) Mandatory item
14) Date is generated automatically
17) Must be RFC 2822 formated
22) ';' is translated to '|'
25) '(|)' means either or
26) '_' is a space character
27) , 29) skips the current data set value
28) used on true condition
30) used on false condition
31) , 32) , 33) , 34) , 35) , 36) use '' as empty parameter
37) , 38) , 39) , 42) , 43) , 45) , 46) , 47) Works only for table command section
40) CUM does not work correctly when using the table S option
41) Format string is according to PHP date() syntax
44) Format string is according to PHP printf() syntax
 
tracker/reports-config.txt · Last modified: 2012/09/23 16:57 (external edit)
 
Except where otherwise noted, content on this wiki is licensed under the following license:(C) 2012 ProjectTRACKER - A. Welz. All rights reserved.
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki