Excel Report Templates

You can define your own Excel report templates to create Excel reports based on them at runtime. Like 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 transmit items from the XML document to the correct places 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 element, while the items someone is working with represent 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 Room2</Project>
        <IssueType>MeetingIssue</IssueType>
        <Status>open</Status>
        <Manager>Administrator, Allegra System</Manager>
    </item> ....
</track-report>

Example of an expense 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>

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>

Example of 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>Development</Subproject>
        <Status>started</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>started</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] email test 1 this is not a joke Text: Successful
                    email test!</change-description>
            </commentElement>
        </commentList>
    </item>
</track-report>

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>open</label>
        <no>89</no>
        <date>2020-11-01 01:00:00</date>
    </s>
    ...
</track-report>

Tabular reports typically organize information in “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. Let’s go through a simple example for our Excel report template. We take the first data source from above as the basis for filling in 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 are expanded for each entry of type “x” in the data source. In the above case, “x” stands for “item”, i.e. an item.

Let’s extend this example to add a detail band that includes the processing 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 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.