Creating Your Own Reports

You can create and use your own Excel reports that will be available to all users with the appropriate permissions.

Granting permission to a user to run a report does not override the field protections controlled by Screens of the Project Template. A field must be visible to a user for it to appear on a report they execute.

Starting a New Report

Use an existing report as the basis for your own report.

Show the two hidden Excel Worksheets entitled "Data" and "Definitions"

Each report consists of two Excel Worksheets:

  • Data: age of open/closed items, breakdown by resource and status
  • Definitions: occurence breakdown with pivot table and charts

Data Worksheet

This worksheet is populated with the data for the report.

You should specify the columns in the report in the first row. Available columns are specified below.

During testing you can specify your own mock data - this will be replaced every time report is generated.

Definitions Worksheet

This worksheet is populated with the data for the report.

Do not remove or otherwise change the system fields as highlighted above.

You should specify the columns in the report in the first row. Available columns are specified below.

NoteThe Data and Definitions tab should both list the columns for your report.

Column Reference

Custom Fields

Any custom field can also be included in your reports by simply prefixing your custom field with "CF_".

  • CF_Segment
  • CF_Number of Employees

Core Fields

The following columns can be used in your custom report.

Column Output
IssueKey The id of the item (e.g. 23456)
ProjectName The project name that the item belongs to (e.g. My Project)
ProjectCode The project code that the item belongs to (e.g. ACME)
Title The title of the item (e.g. Fix Cross-browser issues)
Description The full description of the item (HTML stripped)
Reporter The fullname of the user who reported the item (e.g. Joe Bloggs)
ResourceNames The resources assigned to the item (comma separated)
Status The current status of the item (e.g. Unassigned)
Priority The current priority of the item (e.g. High)
Severity The current severity of the item (e.g. Showstopper)
Resolution The current resolution of the item (e.g. Complete)
Type The type of the item (e.g. Bug)
Created The date and time when the item was created
Revised The date and time when the item was last revised
ClosedDate The date and time this item was closed
ResolvedDate The date and time this item was marked as resolved
StartDate When work should commence on the item
DueDate When work should complete for the item
PercentComplete How much work has been completed (e.g. 25%)
LastComment Latest comment for the item
Comments All comments for the item (comma separated)
Points Allocated points for item
OriginatorData Any associated source data for item (e.g. email address)
ComponentNames Components associated with item (comma separated)
FixedInVersion Any associated version
AffectedVersionNumbers Any associated affected versions (comma separated)
Age Number of days since item created
Repeated Repeat interval for item if any
EstimatedEffort The number of hours and minutes to complete item (0h 0m format)
{EstimateEffortMinutes} Total minutes estimated for the item
{estimatedtimewithkids} Total minutes estimated for the item and its dependencies
TimeLogged The number of hours and minutes to logged against item (0h 0m format)
{timeloggedminutes} The total minutes logged against the item
{loggedtimewithkids} Total minutes logged for the item and its dependencies
RemainingTime The number of hours and minutes to remaining for item (0h 0m format)
{remainingtimeminutes} The total minutes remaining for the item
ExcessTime The number of hours and minutes exceeding estimate (0h 0m format)
{excesstimeminutes} The total minutes exceeding the estimate for the item
Votes The number of votes for this item
Visibility The visibility of the item (e.g. Everyone)
Elapsed Number of minutes since item opened till closure
{ParentItemId} The item id of the parent of the item
{projectlabel} The label of the project that the item belongs to
{versionstartdate} The start date of the version that the item belongs to
{versionreleasedate} The release date of the version that the item belongs to
{slaname} The name of the item's SLA
{slatimeleft} The time left for the item's SLA clock (in minutes)
{slatimepassed} The time passed for the item's SLA clock (in minutes)
{slatimeallocated} The time allocated to item's SLA clock (in minutes)
Count() Output is always 1 - useful when you need to count individual items

Advanced Fields

The advanced fields will cause the item row to repeat for each entry associated with the item.

Column Output
{Time} Total minutes logged for this time entry
{TimeLogger} The fullname of the user who create the time entry (e.g. Joe Bloggs)
{TimeType} The type of the time entry (e.g. Billable)
{TimeEntryDate} The date and time of the time entry
{TimeComment} The comment of the time entry
{HistoryField} To see name of the field which have been created or amended
{HistoryUser} The fullname of the user who create or amend the data
{HistoryDate} The date and time when the history field was created or amended
{HistoryBeforeValue} The value of data before the changes
{HistoryAfterValue} The value of data after the changes

Formula Columns

In addition to the above columns list you can also place formulae to the right of the last column (but not in between columns) and these will be applied to all items.

Report Deployment

Custom Excel based reports should be placed in the App_data/Reports folder.

NoteYour Gemini Administrator should deploy custom reports