Users can connect to OData via Excel so that data can be pulled from R365, stored locally, and then ran in reports. Data can then be refreshed in Excel as needed through each saved query.
This article reviews the following:
Important Note
R365 can help with connecting to Odata to ensure that the API is working for you. R365 does not support applications that are used to connect to OData.
Assistance around best practices can be provided but it is recommended that OData documentation be reviewed and utilized.
Connect to the OData Connector
To begin, open Excel and navigate to the 'Data' tab. From here, click the 'Get Data' selector, hover over 'From Other Sources', and select 'From OData Feed'.
A pop-up modal will appear asking for the URL of the desired endpoint. For a listing of all data point URLS, review OData Endpoints. Once the URL is entered, click 'Ok'.
An additional window will appear where your OData Connector login credentials will need to be entered. Navigate to the 'Basic' tab and ensure to enter your username as 'your domain\your username'.
For example, if your R365 URL is https://domain.restaurant365.com and your username is r365user, then you would enter:
- Username: domain\r365user
- Note: Ensure to include the backslash ( \ ) when entering the username
- Password: Your R365 password
Click 'Connect' to connect to OData.
The desired data endpoints will then be displayed as a preview in the modal. Click 'Load' to load the data into Excel.
The Excel file will then autofill the columns based on the selected data endpoints. If this file needs to be filtered, review the following section.
- Note: It is recommended to set up queries that will pull datasets for a set period of weeks rather than an entire dataset load, as that will pull extensive data and will take a longer processing time.
Click here to view a gif of this process.
Filter the Query
The imported query can be filtered when the initial connection is set up, but it can also be filtered by following the steps listed below.
Navigate to the 'Query' tab and click 'Edit'.
The 'Power Query Editor' modal will open where the query can be filtered by updating the OData feed link with the desired filter. The entry displayed above is set to filter for pay rate that is greater than or equal to $10.
Other filters can be placed on the data as desired. To do so, refer to the OData Query Options documentation for further guidance.
Ensure to click 'Enter' on your keyboard to load the results. Close the modal and then the data will update the Excel sheet.
Refresh the Query
After an initial sync, data can be refreshed daily, weekly, or monthly by clicking the 'Refresh All' button on the 'Data' tab. Downloaded datasets can then be put together in the same table so that reports can be ran from there.