Excel Report Templates

You can define your own Excel report templates to create Excel-based reports at runtime. As with the other report types, Excel reports are based on XML data sources.

We assume that our data source can be represented as an XML document. In our Excel report template, we will submit items from the XML document to the correct locations in the Excel worksheet. We need to distinguish between individual entries and lists of entries. For example, the person who created the report is a single item, while the items someone is working with are a list of entries.

<track-report>
    <createdBy>Administrator, Allegra System !</createdBy>
    <filter/>
    <item>
        <Project>Training room</Project>
        <IssueType>MeetingIssue</IssueType>
        <Status>open</Status>
        <Manager>Administrator, Allegra System</Manager>
    </item>
    <item>
        <Project>Training room 2</Project>
        <IssueType>MeetingIssue</IssueType>
        <Status>geöffnet</Status>
        <Manager>Administrator, Allegra System</Manager>
    </item> ....
</track-report>

Here is an example of an effort report:

<expense-report>
    <createdBy>Administrator, Allegra System</createdBy>
    <expense>
        <accountID>6</accountID>
        <personID>110</personID>
        <workItemID>3747</workItemID>
        <hours>2.00</hours>
        <cost/>
        <subject/>
        <responsibleLabel>Lindy, Walter</responsibleLabel>
        <subproject/>
        <subprojectLabel/>
    </expense>
    <expense> ... </expense> ...
</expense-report>

Here is an example of workload over time:

<budget-report>
    <createdBy>Administrator, Allegra System !</createdBy>
    <noOfCalendarUnits>9</noOfCalendarUnits>
    <budget>
        <personID>292</personID>
        <personName>Krastev, Boris </personName>
        <plannedHours>0.00</plannedHours>
        <plannedCost>0.00</plannedCost>
        <remainingHours>0.00</remainingHours>
        <remainingCost>0.00</remainingCost>
        <projectID>-1</projectID>
        <projectLabel/>
        <currencySymbol/>
        <currencyName/>
        <calendarUnit>1/2021</calendarUnit>
        <capacity>32.00</capacity>
        <unit>1</unit>
        <year>2021</year>
    </budget>
    <budget>...</budget>
</budget-report>

Here is an example of an item with history and comments:

<track-report>
    <createdBy>Administrator, Allegra System !</createdBy>
    <filter>
        <name>tamas</name>
        <expression/>
    </filter>
    <item>
        <Project>TryItOut</Project>
        <IssueType>Error</IssueType>
        <Subproject>Desarrollo</Subproject>
        <Status>iniciado</status>
        <Manager>Administrator, Allegra System</manager>.
        <Responsible>Somogyi, Stephanie</responsible>
        <historyList>
            <fieldChange>
                <changed-by>Somogyi, Stephanie</changed-by>
                <changed-at>2021-06-13 12:35:21.0</changed-at>
                <labelType>Status</labelType>.
                <firstValue>iniciado</firstValue>
                <secondValue/>
                <firstLabel> NewValue</firstLabel>
                <secondLabel> OldValue </secondLabel>
                <change-description/>
            </fieldChange>
        </historyList>
        <commentList>
            <commentElement>
                <changed-by>Somogyi, Stephanie</changed-by>
                <changed-at>2011-10-01 2:48:39.0</changed-at>
                <labelType>Comment</labelType>
                <firstLabel/>
                <firstValue/>
                <secondLabel/>
                <secondValue/>
                <change-description>An email was sent to.
                    \&lt;li\&gt;mlarracoechea@technisys.net\&lt;/li\&gt; Subject: [Allegra
                    14653][TryItOut] prueba de e-mail 1 esto no es un canchereo Text: Prueba de
                    e-mail satisfactoria!</change-description>
            </commentElement>
        </commentList>
    </item>
</track-report>

Here is an example of status over time:

<track-report>
    <createdBy>Administrator, Allegra System !</createdBy>
    <createdAt>2021-02-07 10:54:15</createdAt>
    <title/>
    <dataseriesType>issuesAccumulated</dataseriesType>
    <timeInterval>3</timeInterval>
    <s>
        <label>open</label>
        <no>84</no>
        <date>2020-10-01 01:03:22</date>
    </s>
    <s>
        <label>opened</label>
        <no>89</no>
        <date>2020-11-01 01:00:00</date>
    </s>
    ...
</track-report>

Tabular reports typically organize information into “bands”. A band is a placeholder for a list of entries. The band template is repeated for each entry in the list in the generated report

We will walk you through a simple example for our Excel report template. In this specific case, we take the first data source from above as the basis for filling out the template.

	A	B	C	D	E
1		=”@createdBy”			=TPF(“@createdAt”;”Date”)
2		=”@filter.name”
3	=”@item”	=”@item.Project”	=”@item.Status”
4	=”@item”			=”@item.Manager”	=TPF(“@item.TotalPlannedTime”;”Number”)

Column A is reserved to denote bands. If they contain the marker “@x”, they will be used for each entry of type “x” in the data source. In the case above, “x” stands for “item”.

Let’s expand this example to add a detail band that contains the edit history and comments.

	A	B	C	D	E
1		=”@createdBy”			=TPF(“@createdAt”;”Date”)
2		=”@filter.name”
3	=”@item”	=”@item.Project”	=”@item.Status”
4	=”@item”			=”@item.Manager”	=TPF(“@item.TotalPlannedTime”;”Number”)
5	=”@item.historyList.fieldChange”	=”@changed-by”			=TPF(“@changed-at”;”Date”)
6	=”@item.commentList.commentElement”	=”@changed-by”

To simplify the evaluation of date and numeric expressions during design time, we provide an Excel macro in the form of an add-in file (TrackplusAddIn.xlam) as part of an Excel report template supplied with the system.

Note

If you are creating an Excel report for the first time, you will need to add this add-in to your Office installation and update the link in the Excel document. How this is done depends on the version of your Office package.