Source item fields

Source item fields are place holders that will be replaced with field values related to main item or other generic information.

• Any field from the item itself
  • project.name
  • project.projectmanager.name
  • project.duedate
• Any field from the organization
  • organization.name
  • organization.billingrate
• Any field from the user running the report
  • currentuser.name
  • currentuser.directmanager.name
• Data sent over in the URL call ("name=value" format). Add the relevant data into the URL (e.g. …/DynamicUI/DynamicUI.aspx?DynamicType=ReportTemplate& … &name1=value1&name2=value2)
  • url.name1
  • url.name2
• Runtime function
  • function.now - current date and time (can be formated)
  • function.logo - adds the organization logo
  • function.lastweek-start
  • function.today-3-start
  • function.nextmonth-end
  • See more date functions in the table below

Note that you can access nested fields even if they are not on the main item itself. For example, project.manager.directmanager.jobtitle.name

The format of the source item field comment should be set as follows:

Examples

• { !field=project.name }
• { !field=project.actualcost !format=#,##0.00 }
• { !field=organization.name }
• { !field=user.directmanager.name }
• { !field=function.now !format="MM/dd/yyyy hh:mm"}
• { !field=project.C_url_to_my_image !format=image !width=16 !height=16}
• { !height=400 !pagewidth=1024 !pageheight=768 !loadingtime=10000}

The table below shows the supported formats for the relevant data types.

Related item definition

To access items related to the source item, you must create a separate table.

The first row of the table should contain names for the relevant columns, while the second row should include the indication for the related item itself.

It doesn’t matter where in the row this indication appears (any cell will do), as long as it's there. This serves as a repeatable row for each related item.

Any item related to the source item can be added to the template. It can be a link, an out of the box relation, a custom relation (with or without a reverse reference), or any item type that includes a “reference to object” field that points to the source item.

Here are a few examples:

• ResourceLink item type that links to the project’s team (users). This link contains the field WorkItem that is a "reference to object" field to the project itself
• WorkItemHierarchyLink item type that links to the project’s sub-items. This link contains the field Parent that is a "reference to object" field to the project itself
• Notes item type that has the AttachedTo field that is a "reference to object" field to the project itself
• User item type that has a custom MainProject field that is a "reference to object" field to the project itself. In this case, you don't even have to mark the field with a "reverse reference" in order to access it

With the related items, you can also determine the sort order, filter for relevant related items, and even set a total row (needs to correlate with group-by functions - see below).

The format of the related item comment needs to be as follows:

Examples

• [ !field=Project.Note !relationfield=attachedto !filterfield=createdon !filteroperator=GreaterThan !filtervalue=01/01/2014 !orderby=createdby.name !orderdesc=True !totalrow=True ]
• [ !field=Project.ResourceLink !relationfield=workitem !orderby=Resource.name !orderdesc=False !totalrow=False ]
• [ !field=Project.WorkItemHierarchyLink !relationfield=Parent !filterfield=Child !filteroperator=Equal !filtervalue=Project !orderby=Child.StartDate !orderdesc=False !totalrow=False ]
• Using parameters -
  [ !field=Project.Task !relationfield=Parentproject !filterfield=state.name !filteroperator=Equal !filtervalue=active !filterfield1=DueDate !filteroperator1=LessThanOrEqual !filtervalue1=Param.EndDate
  !orderby=task.duedate !orderdesc=false !totalrow=False ]

Related item fields

Related item fields are place holders that will be replaced with field values of fields from the related item, as defined in the section above.

The format of the related item field comment needs to be as follows:

Examples:

• { !field= ResourceLink.Resource.name }
• { !field= ResourceLink.CreatedOn !format=MM/dd/yyyy}
• { !field= ResourceLink.Units !groupby=MIN}

When no field is marked with a "groupby" function, all relevant relations (according to the filters mentioned above) will be added to the table, one per table row.

The use of "groupby" will change the table to include summary rows and fields with the "groupby" function will display the aggregated data according to the relevant formula.

Below you will find an example of the related timesheets grouped by user.

The formulas for the "groupby" are minimum and maximum reported date and the total reported hours per user.

A total row was also added to view the total reported hours per user.

In the table below you will find a list of the supported functions per field type when using the "groupby" option

• Duration type
  • In order to get to a common denominator, duration values are converted to hours as follows
    • 20 days in one month
    • 5 days in one week
    • 8 hours in one day
  • The formula value will always be displayed in hours
  • Empty value (NULL) will be set to 0 hours
• Currency type
  • The currency will be ignored. When using multi-currency, all values will get summed up together
  • Empty value (NULL) will be set to 0

Limitations of using field placeholders

In order to recognize a field placeholder, it must be located inside a table cell. The table doesn’t have to have a border, and it can be a one cell table if needed.

When a table cell has a field placeholder inside, the entire cell content will be replaced with the field’s data, this includes text that is not marked with the comment indication.

In order to mitigate this limitation, you can add a table inside a table and locate the placeholder comment on one of the inner cells.

Field placeholders (comments) cannot be placed in the header or footer, only in the document body.

Building your templates with the help of macros

In order to help build templates, we have created two Microsoft Word macros that you can use.

There is a separate macro for field placeholder creation and for related item placeholder creation.

In order to use these macros, please download the document with the macros from the link in the resources section.

Once downloaded, you can design your document to fit your specific needs by highlighting the relevant text that you want to mark as a placeholder,

run the macro, fill in the data and click ok. A comment will then be added on the relevant text, with the correct format already populated.

Supported parameter via the URL

If you want to call this integration and build the URL yourself, and control the value of the parameters, please refer to the table below

Roadmaps

The following parameters should be used when inserting a roadmap:

• !format – Use “roadmap” instead of “thumbnail“ as the data type
• !width – Example: !width=2048
• !height – Example: !height=384
• !pagewidth – Example: !pagewidth=2048
• !pageheight – Example: !pageheight=384
• !daterange - Click here to see all options. Example: !daterange = ThisMonth+15
• !groupby – Example: !groupby=Project.State
• !barcolor – Example: !barcolor=Project.TrackStatus
• !barfill – Example: !barfill=PercentCompleted
• !dotcolor – Example: !dotcolor=Project.Importance
• !additionalinfo – Example: !additionalinfo=Project.PercentCompleted
• !DurationToCompare – Examples: !DurationToCompare=None / !DurationToCompare="Baseline Duration" / !DurationToCompare="Planned Duration"
• !MilestonesColor – Example: !MilestonesColor=State