External Reports

Saved reports in TimeTrex Professional, Corporate or Enterprise Editions can be a data source for external reporting engines such as Jasper Reports, Crystal Reports and Google Sheets.

First follow the instructions under the title, TimeTrex, then continue to the titled section for the particular external reporting program you wish to use.

TimeTrex

• Sign in to TimeTrex.
• Create a saved report that contains the data that you want to publish to external reporting engines. For e.g. Report→Timesheet Reports→ Timesheet Summary, Regular Time+Wage by Employee
• Define the maximum Time Period that the report covers and if necessary this can be filtered in the external reporting engine. e.g. Last Pay Period
• View the report to confirm that it displays the proper data.
• Save the report to publish it as a data source for the external reporting engine.
• Give the saved report a unique name that does not contain any spaces or special characters. It is recommended that you prefix the report name with “DS_” to represent that it is a data source. e.g. "DS_Punch"
• Continue to one of the following sections: Jasper Reports, Crystal Reports or Google sheets.

Jasper Reports

• Launch Jasper Reports to begin creating a custom report.
• Create a new data source
• Select Remote XML file datasource.
• Name the data source the same as the saved report.
• Specify the full URL to TimeTrex with the name of the saved report at the end.

  For example: http://ondemand1.timetrex.com/api/report/api.php?name=DS_Punch

• File→New, Query
• Select the newly created data source.
• Configure the query to define the data that you want to display on the report.
• Change the query language to xpath2.
• When prompted, enter your TimeTrex user name/password.
• Select the first row element as the record node.
• Drag the nodes that you want to display on the report and drop them into the fields table.
• Preview the report

Crystal Reports

• Launch Crystal Reports to begin creating a custom report.
• Create a new data source connection.
• Select XML and Web Services as the connection type.
• Specify the full URL to TimeTrex with the name of the saved report at the end.

  For example: http://ondemand1.timetrex.com/api/report/api.php?name=DS_PUNCH

• Make sure you disable Specify Schema File.
• When prompted, enter your TimeTrex user name/password.
• Confirm that the HTTP parameter name matches the exact name of the saved report in TimeTrex.
• Select the name of the report saved in TimeTrex. e.g. "DS_Punch/row"
• Choose the fields you want to display on the report.
• Select the name of the report and preview the report.

Google Sheets

• Because Google Sheets doesn't support loading data from secure authenticated sources out-of-the-box, you will need to add a custom script that supports this functionality. Open ImportJSON custom script in your browser, then select all and copy its entire contents to your clipboard.

• Open Google Sheets and create a new sheet.
• Go to Tools -> Script Editor, clear out any default code that you may see, then paste the custom script copied from ImportJSON.
• Click File -> Save, enter a new project name if asked.
• Go back to your new spreadsheet and click on cell A1, then enter the line from the box below. Be sure to replace the variables like <URL to TimeTrex>, <username>, <password> and <saved report name> with your specific information.

For example: =ImportJSONBasicAuth("https://demo.timetrex.com/api/report/api.php?format=json&name=DS_Punch", "demoadmin1", "demo")

 

 

• The report should appear on the screen.