1.7		Setting up with Geckoboard	add-ons
2.5.7		Standard Reports	usage projects
2.5.9		Burndown and Burnup Charts	usage items time
2.5.10		Ad Hoc Reporting with Excel	Excel

4 documents found.

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.

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

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

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

The definitions sheet must also have the following names defined:

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:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),41)

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.