The information in this article applies to:
- DOVICO Timesheet (version 9.x) or higher
Introduction
The following procedure describes how to create an active link between Microsoft Excel and a DOVICO database. This example describes how to create a linked Microsoft Excel pivot table.
Please Note: Similar steps can be performed to create an active link using the "Get External Data" option located under the Data menu in Microsoft Excel.
1. Within Excel’s Data menu, select the "PivotTable and PivotChart Report…" menu item as show below:
2. Select "External data source" from the PivotTable and PivotChart Wizard dialog box and make sure "PivotTable" is selected for "What kind of report do you want to create?" and Click Next.
3. Select "Get Data" on the dialog box as shown below.
4. Select "<New Data Source>" and click OK on the Choose Data Source dialog box as shown below.
5. In the Create New Data Source dialog box (shown below) perform the following.
- Enter a descriptive name for the link being created (remember this is simply a link to the database table or view, and can be used over and over again for different purposes).
- Next select SQL Server from the drop down list.
- Click Connect
6. In the SQL Server Login dialog box (shown below), perform the following.
- Select the Server on which the database resides,
- Unselect the Use Trusted Connection option.
- Enter the Login ID (by default "sa") and the Password (by default "dovico").
- Select Options.
7. In the Options section of the SQL Server Login dialog box (shown below). Select the Database from the drop down list (in this case the DemoDataV9 database has been selected) and Click OK.
8. Select the default table or view from the drop down list. Select the "Save my user ID and password in the data source definition" option. Click OK.
9. The new data source has now been added to the system as shown below. Select the newly created data source and click OK.
10. The following dialog box is displayed. Select the > button to add all fields from the "DOVICO_VIEW_APPROVED_TIME" view. Click Next.
11. The Filter Data dialog box is displayed (shown below). Click the Next button.
12. The Sort Order dialog box is displayed (shown below). Click the Next button.
13. The Finish dialog box is now displayed (shown below). Select "Return Data to Microsoft Excel" and click the Finish button.
14. Click the Next button on the dialog box shown below.
15. Select the position where the pivot table is to be stored in the Excel spreadsheet and click Finish.
16. Next you will need to construct the actual pivot table.
17. Drag the fields that you would like to include from the PivotTable toolbox (shown below) to the appropriate drop locations on the Excel Spread Sheet (shown below).
18. The screen below shows one example of how the data can be displayed.
19. It is recommended to turn off the AutoFormat table option in the Pivot Table Options dialog if you want the table and cells format to be preserved while refreshing data. To get to this, right click on the pivot table and choose "Table Options".
20. The data can be refreshed any time by selecting the Refresh Data button on the PivotTable toolbox or in the Data menu.
Congratulations!!! You have successfully performed an active link with Microsoft Excel.
Examples:
NOTE: The following files are available for Track-IT Suite users only. DO NOT use these sample file to link to DOVICO Timesheet databases.
Below are two examples of Excel spreadsheets, which have been linked to a Track-IT Suite database.
Download the files below and extract the .XLS files to your computer.
- Detailed Payroll information: http://www.dovicofiles.com/Payroll.exe
- All time transaction information including billable, and non-billable time: http://www.dovicofiles.com/AllTimeView.exe
Follow the instructions in the following document to update the connection properties for these spreadsheets - Updating Connection Properties for an active link to Excel