This section describes the Clarizen’s Document Publisher and includes the following sections:

Intro

Architecture

Step by Step

Template format

Building your templates with the help of macros

Supported parameters via URL

Roadmaps

Resources

Intro

Clarizen’s Document Publisher allows to dynamically create documents based on an item(s) data.

The document publisher works by uploading a template, that you can create using supplied macros,

and selecting the relevant items in Clarizen from which you would like to gather data.

Flow view:

Template example

Output example

Architecture

The process behind this feature is simple and robust:

• When an item is selected and the custom action is selected in the ribbon, a call is sent to Clarizen's integration server with the user’s session id and other parameters, as set by the user

• The server then connects back to Clarizen, fetches the relevant template along with the relevant data
  
  
• An Open XML component then reads the selected template, replaces the place-holders with the relevant data, downloads the document, and then uploads and attaches it back to the relevant item in Clarizen

Any applications that support .docx formats (such as Microsoft Office Word, OpenOffice, LibreOffice, Abiword, Kingsoft Office, Jarte, etc.) can be used to create the templates and read the created output files. There are no read/write limitations under Windows, Linux, iOS and other operating systems.

Microsoft Office supports this format from Office 2007 and onward.

Macros that help in building templates are only available in Microsoft Office (see additional details below).

To learn more about the office Open XML and .docx/.docm format, please refer to this link:

http://en.wikipedia.org/wiki/Office_Open_XML

Step by Step Guide

In order to start working, creating, and publishing documents based on templates, please follow the steps below:

1. Install the “Clarizen Document Publisher” app

The app includes all the relevant custom actions and business rules required to publish documents

2. Create a template

Create your own customized template as detailed below.  You can also download out of the box template samples and

Clarizen’s MS Word file with macros to help build your own templates in the “resources section” of this document

3. Upload the template into Clarizen

Once your template is ready, upload it as a file to Clarizen.

The item the file is attached to is not important, just ensure that it will be accessible by the relevant users. For instance,

if you want to make the template available to certain members of a group, upload the file onto the discussion group by clicking on “Add Related”

Now select file

Within the popup, select the “File Upload” tab and then select the file from your PC

Alternatively, you can drag and drop a file directly into the file panel, or onto a new discussion post, task, case, etc.

4. Publish your document

Select the Clarizen item you want to run the “Clarizen Document Publisher” on.

For example, to create an invoice for a specific project based on the reported and approved time entries, select the relevant project.

Click the 'Publish workitem document' custom action in the ribbon

Fill in the details in the popup, and select the relevant template, the output type (docx or pdf), if to attach the output, etc.

5. View the published document

You can choose to download the file and/or see the report attached on your selected item

Template Format

Below is an example of a template that displays data from a project.

The source item fields are ones that exist on the project, while the related fields are from the project’s team and sub projects.

A template can contain three types of "place holder" fields:

• Type A:  Source item fields – these fields come from the main/selected item (e.g. the project, organization or current user)
• Type B: Related item – enables you to define the related items you would like to access (e.g. the project’s team or sub-projects)
• Type C: Related item fields - these fields come from the related item(s) as defined in type B (e.g. resource name, sub-project start date, etc.)

Defining the exact fields/relations that need to be accessed is done using document comments.

Simply highlight the text that needs to be replaced and click to add a new comment.  You can then enter the relevant properties (as explained below).

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 ]

List of allowed filters per field type:

In order to support dynamic range of dates in the filter, you can use the followings constant "filter value" expressions

Note that you can add to all of the following values '+n' or '-n', when the 'n' represents numeric number, in order to enlarge the date scale. e.g.

·         'Today-9' to get the last 9 days

·         'ThisMonth+15' to get extra 15 days on the current calendar month

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

Resources

• Link to the app
• Download the template used in this document
• Download .docm file with the macros