Reporting with time cockpit and Microsoft Office Excel

Microsoft Office Excel is the prevailing spreadsheet software today. Time cockpit accommodates this by offering powerful features for exporting data to Excel in the full client as well as in the online browser client. In this article you learn how you use them to implement reports based on time cockpit data. The article covers the following topics:

• Quick Export - export data to Excel with one click
• Export Templates - create advanced reports with time cockpit export templates
• Avanced Reporting With Scripting - solve advanced reporting problems with time cockpit scripts
• References - links for further readings

Quick Export

Let us start with the basics: You can export every list you see in time cockpit in Excel with a single click on the Quick Export ribbon button.

Quick Export will export all columns and rows of the list to Excel. The following picture shows the result of the quick export process of the time cockpit list shown above. Note the filter area in the upper left corner of the spreadsheet. It contains your filter criteria from time cockpit so that you could reproduce the export if necessary. You could attach such an export to e.g. an invoice that you send to your customer.

If you export a larger amount of data and you want to analyse it or create a more advanced report e.g. for project management, you might want to use Excel's Pivot Tables for that. You have to save your quick export to a file and let your pivot table reference it. The following screenshot demonstrates, how you create a pivot table based on a time cockpit quick export file. Of course you can add multiple pivot tables or charts in a single Excel report even if they reference multiple time cockpit export files.

It becomes even easier to create additional pivot tables and charts once you have created your first pivot table from the quick export file. The reason for this is that Excel saves the connection (i.e. name of the folder and the file) for subsequent use.

It might happen that you want to create your report periodically. In this case you just have to run your exports in time cockpit again and refresh the Excel reporting sheet with your pivot tables. You can make your life easier by setting your data connections to auto-refresh:

Export Templates

Time cockpit offers advanced support for reporting scenarios. As shown before, you could create your report by exporting data to Excel files and referencing them in a central report file with Excel pivot tables. As an alternative you can create a template file for exporting data from time cockpit. Here are the steps that you need to follow to create a template file:

1. Create a new Excel worksheet.
2. Add template headers and formulas for the columns that you want time cockpit to export. Use the Time Cockpit Query Language (TCQL) for the formulas. If you want to learn more about TCQL, check the References section at the end of this article for related links to the time cockpit online help. The following screenshot shows an example of how you add the formulas to your template worksheet:
   
3. After you specified all columns with their formulas, you have to mark the row with the formulas with the name TemplateRow. Note that TemplateRow must not contain the row with the column headers. If you change your template file afterwards and you need to change the definition of TemplateRow, you can use Excel's Name Manager (Ctrl + F3) for that. The following screenshot shows how you set the name in Excel:
   
4. Add additional worksheets with e.g. pivot tables or charts. They can reference the template worksheet that you created in step 3. It is a good practise to reference the source without restricting the number of rows. This is necessary because the number of rows that you export from time cockpit may vary from export to export. The following screenshot shows how you create such a reference in Excel. Note that the Excel formula does only contain a column letter but no row restriction (i.e. $A:$N instead of $A$1:$G$17).
   
5. In most cases it is a good idea to enable auto-refreshing for the data connections (already described above).
6. Save your template as a regular Excel .xlsx file. Note that you have to close the template file before you can use it in time cockpit.

Now that you have created your template file, you can use it in time cockpit when exporting data. The following screenshot shows how to do that. Note that time cockpit saves the template files that you have used in the past. In subsequent exports you just have to choose from the list of recently used template files. Did you recognize that the Destination File field is empty? If you leave it empty time cockpit will create a temporary file for you and will open it in Excel immediately.

When you are done your report could look like this:

Currently the use of templates is only support in the time cockpit full client. In the online browser client you can only use the Quick Export function described above.

Advanced Reporting With Scripting

If your reporting needs go beyond of what you have seen above, you can use time cockpit's scripting functions. Here are some examples of what you can achieve with scripting concerning reporting:

• Automate the export process and e.g. send the resulting Excel sheets to project managers via email.
• Create custom Excel exports by using Excel's automation API from time cockpit Python scripts.
• Add more complex business logic to calculate certain KPIs in order to include them into your reports.

The time cockpit online help contains samples for accessing the Excel export function from time cockpit scripts. The References section below contains links to the  chapters of the online help dealing with the topics mentioned in the list above.

References

• time cockpit online help
  • time cockpit Query Language (TCQL)
  • HowTo: Export Data To Microsoft Excel
    
  • Automating Scripts
• Excel Object Model Reference (MSDN)