Gemini Community Support Site

This Gemini community support site can be used to find solutions to product issues. You can log in using Open Id, Google Profile and even Facebook. Feel free to ask a question or browse FAQs and documentation. Product tour videos are also available along with how-to videos demonstrating key Gemini capabilities.

Index Ad Hoc Reporting with Excel
Previous  |  Next

 1      Installing Gemini 5
 1.1        Pre-requisite Checks
 1.2        Installing Gemini 5 Using Automated Installer
 1.3        Installing Gemini 5 Using Zip File
 1.4        Upgrading 5.0 to 5.1
 1.5        Upgrading 4.2 to 5.1
 1.6        Using Windows Authentication
 1.7        Setting up with Geckoboard
 1.8        Setting up with Lucidchart
 1.9        MS Project Connector
 2      Getting Started with Gemini 5
 2.1        Signing in to Gemini
 2.2        Navigation in Gemini
 2.3        AppNav Cards
 2.4        Gemini Terminology
 2.5        Features and Functions
 2.5.1          The Grid
 2.5.2          The Planner
 2.5.3          Activity Page
 2.5.4          Timeline Page
 2.5.5          Roadmaps and Changelogs
 2.5.6          Documents
 2.5.7          Standard Reports
 2.5.8          The Calendar
 2.5.9          Burndown and Burnup Charts
 2.5.10           Ad Hoc Reporting with Excel
 2.5.11           The Data Import Wizard
 3      Creating and Managing Tasks
 3.1        Creating Tasks
 3.2        Rapid Planning (Creating Multiple Tasks)
 3.3        The View Item Page
 4      Structuring Projects
 4.1        Creating Projects
 4.2        Components in Gemini
 4.3        Versions in Gemini
 4.4        Setting Project Defaults
 5      Breeze - Email-to-ticketing
 5.1        Introduction to Breeze
 5.2        Breeze: Queues
 5.3        Breeze: SMTP Servers
 5.4        Breeze: Mailboxes
 5.5        Breeze: Response Templates
 6      Sentry - Testing in Gemini
 6.1        Introduction to Sentry
 6.2        Test Cases
 6.3        Test Plans
 6.4        Test Runner (Executing Tests)
 6.5        Test Run History
 7      Project Templates
 7.1        Project Templates: Introduction
 7.2        Project Templates: Process
 7.3        Project Templates: Screens and Visibility
 7.4        Project Templates: Process Workflow
 7.5        Project Templates: Custom Fields
 7.6        Project Templates: Menus
 7.7        Project Templates: Status, Priority and Severity
 7.8        Project Templates: Resolution
 7.9        Project Templates: Links
 7.10         Project Templates: Time
 8      People and Permissions
 8.1        Users
 8.2        Groups
 8.3        Permissions
 8.4        Active Directory Integration and Windows Authentication
 9      Code Review & Source Control Connectors
 9.1        SVN Server Installation
 9.2        Client Side Gemini Tortoise SVN Integration
 9.3        Git Source Control Connector
 9.4        Mercurial Source Control Connector
 9.5        TFS Source Control Connector
 9.6        Using Saucery for Code Review
 10       Gemini 4
 10.1         Getting Started
 10.2         Installing
 10.2.1           Requirements
 10.2.2           Automated Installer
 10.2.3           Microsoft Web Platform
 10.2.4           Zip File
 10.2.5           Scheduler Service
 10.3         Upgrading
 10.3.1           Upgrading Existing Installations
 10.3.2           Upgrading 4.1.x to 4.2
 10.3.3           Upgrading 4.0.x to 4.2
 10.3.4           Upgrading 3.7.x to 4.2
 10.3.5           Upgrading 3.6.x to 4.2
 10.3.6           Upgrading 3.5.x to 4.2
 10.3.7           Upgrading 3.1.x to 4.2
 10.3.8           Upgrading 3.0.x to 4.2
 10.3.9           Upgrading 3.0.0 to 4.2
 10.3.10            Upgrading 2.2 to 4.2
 10.3.11            Upgrading 2.1.x to 4.2
 10.3.12            Upgrading 2.0.x to 4.2
 10.3.13            Upgrading 1.9.1 to 4.2
 10.3.14            Upgrading 1.9/1.9a/1.9b to 4.2
 10.3.15            Upgrading 1.8.x to 4.2
 10.3.16            Upgrading 1.7 to 4.2
 10.3.17            Upgrading 1.6.3 to 4.2
 10.4         Projects
 10.4.1           Project Concepts             Grouping Projects             Components             Versions             Roadmap             Change Log             Dashboard
 10.4.2           Project Customization             Project Settings             Single, Multiple Item Options             Item Type, Priority, Severity Options             Default Values             Components             Versions             Project Attributes             Which Fields Are Displayed?
 10.5         Item Management
 10.5.1           Overview
 10.5.2           Creating
 10.5.3           Editing
 10.5.4           Viewing
 10.5.5           Visibility
 10.5.6           Custom Fields
 10.5.8           Hierarchy
 10.5.9           Links
 10.5.10            Recurrence
 10.5.11            Estimates & Time Logs
 10.5.12            Source Control Links
 10.5.13            Audit Trails
 10.5.14            Workflow
 10.5.15            Filter Items              Using the Filter              Saving and Reusing Filters              Cross Project Roll-ups
 10.5.16            Patterns
 10.6         Planning Board
 10.7         Time Management
 10.7.1           Time Reports
 10.7.2           Recording Time
 10.8         Notifications
 10.8.1           Project Alerts
 10.8.2           Item Watchers
 10.9         Test Management
 10.9.1           Approach
 10.9.2           Plans
 10.9.3           Cases
 10.9.4           Runs
 10.9.5           Traceability
 10.9.6           Filtering
 10.10          Management
 10.10.1            Administration              General Configuration              SMTP and Notifications              Projects              Custom Fields              Item Management              Test Management              Licensing
 10.10.2            Users & Security              User Management              Global and Project Groups              Working with Global Groups              Working with Project Groups              Available Permissions              Project Resources              Assigning Permissions to Projects              Windows Authentication              Active Directory Integration
 10.11          Using Add-on Products
 10.11.1            Visual Studio 2008/2010
 10.11.2            Gemini Desktop
 10.11.3            Microsoft Outlook
 10.11.4            Converse              Installation              Core Mailbox Setup              Selecting Emails to Process              Manipulating Email Content              Outbound Notifications              Project Mapping
 10.12          API
 10.12.1            Introduction
 10.12.2            Enabling API support
 10.12.3            Authentication
 10.12.4            Response Formats
 10.12.5            .NET Quick-start
 10.12.6            Schema Reference
 10.12.7            Administration API
 10.12.8            Alerts API
 10.12.9            Custom Fields API
 10.12.10             Groups API
 10.12.11             Items API
 10.12.12             Projects API
 10.12.13             Users API
 10.12.14             Testing API
 10.13          User Profile
View  |  Print  |  PDF

194 documents found.

Ad Hoc Reporting with Excel


Gemini 5 introduces Ad Hoc Excel reporting. Excel reports are run from the Excel report button on the Grid.  All Excel reports are located in the Reports folder beneath the App Data folder on the Gemini web site.

Excel Reporting

fig 1.0 Pivot from the Breakdown Report

Locating Excel Reports

Excel reports are run from the Excel report button on the Grid. All Excel reports are located in the Reports folder beneath the App Data folder on the Gemini web site. When the Gemini application is started, the identity of all reports is loaded and cached for display on the grid. If you create a new report you must therefore recycle the Gemini Application Pool or restart the web server or it will not appear in the list.

There are a number of pre-defined sample Excel workbooks (e.g. AgeAnalysisReport, BreakDownReport and EstimatesActualsReport etc) that you can find from the Grid. Before developing a new Excel report we recommend you familiarize yourself with these reports as you might save yourself some time and effort by either modifying one of them or using it as the basis for a new report.

How Gemini provides data to the report

Gemini passes data to Excel from a selected AppNav card. Since the reports are run from the Grid it follows that the AppNav card must be based on the Grid view.

The data that is in the scope of the AppNav card filter is passed to the selected Excel workbook, which must contain at least the following two tabs:

  1. definitions
  2. data
*In the sample reports these tabs may be hidden by default.

The definitions tab

The definitions tab contains a list of fields to be exported from Gemini. It can have the following values, most of which are self-explanatory

IssueKey Item Id
Project Name Project Name
ProjectCode Project Code
Title Item Title
Description Item Description
Reporter Who reported it
Resource Delimited (if multiple) list of assigned resources
Type Type of item e.g Task/Bug/Story/Query
Revised Last revised date
Created Created Date
Comments Delimited list of comments
EstimatedEffort Estimate in hours
Age Age in days
Count() Always 1 so that formulae can be used to count the number of items exported
TestRun Identity of the test run that resulted in the creation of a defect

In addition to this, you can place formulae to the right of the last column (but not in between columns) and these will be copied down by Gemini. For example, you could have a calculated column “Elapsed Time” = “Start Date” – “Due Date”

The following cell references must always contain the following values

Cell Reference










The definitions sheet must also have the following names defined:


Cell Reference

Defined Name








Gemini will populate URL with the URL of the Gemini instance, DATE with the report execution date and NAME with the Gemini user name of the person who executed the report.

The data tab

The data tab will always be overwritten by Gemini, which will export data matching the columns specified on the definitions tab (including formulae) to the data tab, under identical column headings.

You can build pivot and other reporting from the data tab. To refer to the data on the data tab in a pivot report the pre-defined Excel workbooks use a dynamic name definition as follows:


In the example above the number 41 has been hard-coded to reference the number of columns on the definitions tab but you could also replace this with a COUNTA(1:1) (which will count the number of columns for you.

If Excel is not your particular area of expertise then please copy one of the sample workbooks delivered, secure in the knowledge that the definitions and data tabs are correctly defined for you and simply make sure that when you specify the data range for your pivot (or other) tables/charts you specify the name of your data data in the format “=data”. If you use the Excel Name Manager you will see that the pre-defined workbooks have the name “data” defined in a manner similar to (or exactly the same as) the OFFSET formula above.

Previous  |  Next