Open topic with navigation

OData reporting with MS Power BI

Open the App area in Synergy Cloud Services > select the menu button in the top toolbar > select the 'Reports' option. In the reports page click the blue 'Synergy OData export' button.

Tips:

What is OData reporting with Microsoft Power BI?

OData is an industry standard method used to export information from databases, to allow the information to be used in external reporting tools. Learn about the technical side of Odata at the OData website.

The Synergy OData interface lets you export many tables of Synergy data, and quickly refresh the data in third party reporting tools using a direct connection your companies database stored in Synergy Cloud Services. This direct connection to your database lets you get the latest reporting information for your Synergy data to be easily imported into third party reporting tools.

Power BI is a suite of business analytics tools to analyse data, create reporting dashboards using your Synergy data, and share the reporting information with your team. Monitor your business and get answers quickly by viewing the reporting dashboards on any device with a web browser, or by installing the Power BI app on a mobile device. Read the Power BI guide on importing data from external sources here.

Why use OData reporting with Microsoft Power BI?

Create a reporting dashboard that can be viewed in Power Bi on any device, and easily refresh your reporting data from your Synergy Cloud Services database.

Using Synergy OData in Microsoft Power BI

ClosedUsing the Synergy sample file
  1. Power BI online steps:
    1. Go to www.powerbi.com and sign-in or create a new user account.
    2. Download the Power BI Desktop application here: https://powerbi.microsoft.com/en-us/desktop/
    3. Install the Power BI Desktop application following the steps shown on the above page.
  2. Synergy Cloud Services steps:
    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.

    1. In 'Step 2' apply any required filters to limit the data exported by status (project or sub project), invoice status, or date range.
    2. In 'Step 3' locate the 'Microsoft Power BI' box, and click the 'Get the Synergy sample file' button.
    3. The download will start for the Power BI sample file to your PC called 'Synergy OData sample.pbix'. Open this file in Power BI Desktop application once the download completes.
  3. Power BI Desktop Application steps:
    1. The Power BI sample file opens to show the reports using data from a Synergy demo environment.
    2. Click the 'refresh' button in the top toolbar of Power BI.
    3. The 'access an OData feed' pop-up is shown in Power BI.
    4. Click the Basic tab.
    5. Copy the Synergy 'OData Username' into the Power BI 'Username' field.
    6. Type the password you setup for OData in Synergy Cloud Services.
    7. Click the Connect button.

    Click the image above and see how to setup Power BI to use the Synergy OData connection.

    1. The OData connection will obtain the latest data for your company using a connection to Synergy Cloud Services.
    2. After the data load of your companies data has completed, the dashboard reports will update to display your data.
    3. (Optional) Adjust these reports to meet any of your companies reporting requirements.
    4. Publish the reports to the Power BI online portal. Learn more in the Power BI documentation.

    Click the image above and see how to setup Power BI to use the Synergy OData connection.

  4. Power BI online portal steps:
    1. Open the online Power BI at www.powerbi.com.

    2. Setup your Power BI dashboard by 'pinning' your favourite reports to a dashboard. Follow the steps in the Power BI documentation: https://powerbi.microsoft.com/en-us/documentation/powerbi-service-dashboards/

    3. (Optional) Share your reporting dashboard with your team in Power Bi. Follow these steps in the Power Bi documentation: https://powerbi.microsoft.com/en-us/documentation/powerbi-service-share-unshare-dashboard/
ClosedSynergy tables available
  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. Sub project attributes
  22. Staff skills
  23. Sub project managers
  24. Sub projects
  25. Sub project status changes
  26. Transactions

Tips:

  • After the tables or fields are imported into power bi change the type to be percentage or currency to see the data as expected. e.g. Budget % complete, or Budget $ value for a sub project or project will require you to update the data type in power bi.
  • Date columns need to be updated to the 'short date' format after import to match how they are displayed in Synergy.
  • Both active and inactive records are exported. e.g. all contacts are exported - active and inactive status contacts.
  • The data that is exported using Odata has no filters applied by default. e.g. all the transactions are exported by default. Use the filter options in step 2 to control what data is exported to your BI report.
  • No sorting is applied to the columns in the tables.
ClosedSynergy OData filters

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.

Example

The report should only have the items that are not linked to a projects that is complete or unsuccessful (lost).

  • Uncheck complete and unsuccessful status options, and you will get the projects and linked sub projects at Proposal and Active (All) statuses. Any sub projects that are complete or unsuccessful are excluded.
  • The transactions are only exported if the project or sub project they are linked to is included in the status list.

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.

Tip: The export or refresh time for your report will be faster if you use the date restriction filter.

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.

Tip: Draft invoices are only shown in the invoice header table export, not in the invoice lines table.

ClosedSetup an Odata connection in your Power BI file

When creating a Power BI file you can setup an OData feed to report on your Synergy data by:

  1. Open Power BI desktop, and create a new Power BI file.
  2. Select 'Get Data' > Odata Feed.

Click the image above to see the OData feed menu option.

  1. The 'OData feed' window is shown. Copy the 'link' from the Synergy Odata page into the 'URL' field, then click OK.

Click the image above to see the Synergy URL / Link for OData.

  1. The access window is shown requesting advising that the 'user was not authorised.

Click the image above to see the Synergy URL / Link for OData.

  1. Setup the Odata user details by:
    1. Click the 'basic' tab.
    2. Copy the 'Odata username' from Synergy Cloud Services into the 'username' field in Power BI.
    3. Type the password that you setup in Synergy Cloud Services for OData.
    4. Click Connect.

Click the image above to see the username and password entry for OData.

  1. On the 'Navigator' page select which tables from Synergy that you want to import into this Power BI file, then click 'load'.

Click the image above to see the Synergy OData tables.

  1. The main Power BI reporting page is shown, and the tables are loaded on the right.
  2. Before setting up any reports, check that the 'relationships' between the tables have been setup by auto detect.
  3. Select the 'manage relationships' button to see what links have been setup.
  4. In this example both the 'contact attributes' and 'expenses' tables are linked to the 'contacts table using the 'Contact ID' field.

Click the image above to see sample Power BI relationship links.

  1. Create your own Power BI reports and dashboards. Learn more in the Power BI documentation.

Want to learn more?

Looking for more help? Try reviewing the following topics: Concept Link IconView Topics

© 2018 Total Synergy Pty Ltd