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.
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.
How to Setup and Use the OData Connector - Video
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
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.
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.
FAQ
What username do I enter when Excel asks for credentials?
On the Basic tab, enter your R365 domain, a backslash, and your username. For example, if your R365 URL is https://yourcompany.restaurant365.com and your username is r365user, enter yourcompany\r365user. Your password is your R365 password. Leaving off the domain is the most common cause of authentication errors. If the correct format doesn't fix it, contact R365 Support. See Restaurant365 OData Connector.
Which R365 users can connect to OData?
Users with the Accounting Clerk or Full Access role. OData access is tied to those roles, not to individual permissions. If each person signs in with their own R365 login, they see only the data for their Location Access. A dedicated connector user sees all locations. See Restaurant365 OData Connector.
Where do I find the endpoint URL to enter?
The OData Connector article lists every data point URL. See OData endpoints.
Why is my data load slow, and how do I limit it?
Loading an entire dataset pulls a large amount of data and takes longer to process, and R365 throttles some endpoints. Set up queries that pull smaller sets of data, such as a set number of weeks, and pull historical data month by month the first time. To filter an existing query, open the Power Query Editor, update the OData feed link with your filter, and load the results. See OData best practices.
How do I refresh the data after the first load?
Select Refresh All on the Data tab in Excel. You can refresh daily, weekly, or monthly, and combine the downloaded datasets in the same table to run reports. See OData Connector.
Does R365 support the application I use to connect to OData?
R365 Support helps you connect to OData and confirms the API is working. R365 doesn't support the application you use to connect, such as Excel, Power BI, or Domo. Review the OData documentation for best practices.