OData Reporting with MS Excel

1. Login to Synergy Cloud Services at www.synergycloudapp.com using an account that has either 'Director' or 'System Administrator' level access to the 'app' area.
2. Open the 'App' area.
3. Select the menu button in the top toolbar > select the 'Reports' option.
4. Select the 'Synergy OData export' button to open the OData configuration page.
5. Setup your Synergy OData password by:
   • Type the password, and then type the matching password into the 'confirm password' field.
   • Save the password.

   • Tip: The password must be 7-50 characters long and must contain at least 1 number, 1 upper case character and 1 lower case character.

Click the image above to see where to setup your Synergy OData password.

6. In 'Step 2' apply any required filters to limit the data exported by status (project or sub project), invoice status, or date range.
7. In 'Step 3' locate the 'Microsoft Excel' box, and click the 'Get the Synergy sample file' button.
8. The download will start for the file called 'Synergy OData sample.xlsx'. Open this file in Excel once the download completes.
9. In Excel if prompted with security warnings for the downloaded file, select to 'enable editing', and 'enable content'.
10. Go to the 'Data' tab and select the 'refresh all' button.
11. The re-enter password pop-up is shown. Enter the following then click OK:
    • Username - Copy the 'Odata username' from Synergy Cloud Services.
    • Password - Type the password you setup in the Odata page in Synergy Cloud Services.

Click the image above to see where enter your Synergy OData password in Excel.

12. The data in the sample excel reports is updated to use your company information.
13. Customise these reports as required.
14. Click the refresh all button again when you need to update the data.

1. Contact attributes
2. Contacts
3. Dates
4. Expenses
5. Forecast
6. Invoice lines
7. Invoices
8. Invoice to contacts
9. Personnel
10. Personnel attributes
11. Planning board
12. Project attributes
13. Project invoice to contacts
14. Project managers
15. Primary project contacts
16. Projects
17. Project status changes
18. Resource planning
19. Staff
20. Staff attributes
21. Staff skills
22. Sub project attributes
23. Sub project managers
24. Sub projects
25. Sub project status changes
26. Transactions

 

Use the 'filter the data' options in Step 2 within Synergy Cloud Services, to restrict what data exported in your companies OData feed. Filter options available are:

1. Project, sub project, and transaction status

Select one or more status options to be included in the data export. The status options selected restrict which projects, sub projects and linked transactions are exported. The OData tables effected by this filter are: Projects, Sub Projects, Transactions, Forecast, Project Attributes, Sub Project Attributes, Project Managers, Sub Project Managers, Project Primary Contacts, Project Invoice Contacts.

2. From date

Type a date, or select a date from the drop down calendar. The date filter controls the start date for the data export from using your companies OData feed.

OData tables effected by this filter are: Invoice contacts, Supplier bills / Expenses, Invoices, Invoice Lines, Transactions, Sub Project Status, Project Status, Forecast, Date format variations.

3. Invoice status

Control which invoice types are exported using OData. Include or exclude any credit notes, write-off credit notes, cancelled invoice credit notes, and draft invoice.

OData tables effected by this filter are: Invoices, Invoice Lines, and Invoice Contacts.

1. Open Microsoft Excel, and create a new file.
2. Go to the 'Data' tab.
3. Select the 'from other sources' button and choose the 'from Odata data feed' option.

Click the image above to see the OData fee option in Excel.

4. The data connection wizard is shown. Enter the following details:
   1. Copy the Synergy Odata 'link' into the excel step 1 file called 'Link or file.
   2. In step 2, select the use this name or password option.
   3. Copy the Synergy 'Odata username' into the 'username' field in Excel.
   4. Type the Synergy Odata password into the Excel password field.
   5. Click Next.

Click the image above to see the Odata connection details.

5. Select which Synergy tables you want to import to Excel to include in your report. Click Next.

Click the image above to see the Odata table selection.

6. Update the file name (optional) to be used for your Excel OData connection. Click Finish.

Click the image above to view the saved OData connection.

7. Select the Excel method that you want to view your Synergy data with. e.g. a table, pivot chart etc. Click OK.

Click the image above to see the Excel Import data options.

8. Before creating charts or reports it is suggested that you setup the table relationships.
9. Setup a relationship by going to the Data tab > click the relationships button.
10. A list of existing relationships is shown. Click New if this is blank to create the required links.

Click the image above to see where to setup the table relationships in Excel.

11. The links required will vary based on the tables that have been imported.
12. Example of a table link using the ID fields is shown below, along with a list of other possible ID mappings.

Click the image above to see excel table relationships.

13. Create the required reports, charts or tables in Excel.
14. Use the 'refresh all' button on the Data tab in the file when you need to get the latest information for your company using the OData Connection to Synergy Cloud Services.

Click the image above to see sample Excel Odata pivot charts.