Excel Reporting using OData

Owned by Allison Derome (Unlicensed)
Last updated: Jun 03, 2022 by Alex Morrisson
4 min read

Connect BUZ Data via Excel


The first step is to get your Organisations Code, this will be required when logging in

 

Connecting your data via excel, if you don't already have Get & Transform (Excel 2016) or Power Query (Excel 2010 & 2013)  you can download here → https://www.microsoft.com/en-au/download/details.aspx?id=39379

Get & Transform (Excel 2016) steps:

  1. Open Excel, click on Data Query tab

2. Go to → New Query→From Other Sources → From OData Feed  

3. Type in your URL → https://api.buzmanager.com/reports/ Organisations Code

4. Sign in using your credentials

Your user name will have your organizational code that we found in the first step in the Reports section (xxxx) forward slash your username for BUZ. Can be found in BUZ under Reports ->  Right hand side ( 5 Character code) or contact BUZ Support to supply your Organizational Code.  (Example: support@buzsoftware.com.au)

 

5.  Select the report to access → EDIT → Do not use the load button



6. The Query Editor will open. Query Editor keeps track of everything you do with your data, it records and labels each transformation, or step, which is applied to the data. Excel then has a snapshot of the refined dataset and loads it to a workbook.

You can now run filters on the data as required or write your transformations using the M Language using Query Editor's Advance Editor 

 

Date/Time Filter

You must add a Time/Date Filter, if not the search will not end and will time out, you will see an error message

 


To add a Date/Time filter you can

Click the arrow next to heading that you want to add the filter to.

There is a variety of options you can use, I find it best to use the “Between” Options

 

 


Then select the dates you wish the report to cover

8. Once all the required Filters are run → Close & Load the data to Excel

Updating an existing Excel OData report

  1. Open Excel go to→ Data tab → Show Queries 



2. If you aren't connected to the Odata you will get the below error and need to follow the required steps as outlined in the screen shots below.

  



3. Power Query Editor will open any filters will be listed in the applied steps → right click on the settings icon next to the required filter → Edit Settings option



4. Update the required settings



5. Close & load

 

TIPS for using Excel Get & Transform 

Change organisation your connected to

 1. Excel → Data → New Query button ! → Drop Down → Data Source Settings

2. Click Edit Permissions → Edit credentials 

 

3. Update the required details → Save → Now you can "get data" for this organisation 


 

Edit loaded OData

 1. Edit Queries will allow you to filter the data downloading into the reports. Select Query tab

Refreshing Data

To Retrieve new data from BUZ select "Refresh" from your Design tab

Errors loading the data

  1. Clear the permissions and try again

Click here for instructions

2. Clear the Cache if still an issue after clearing the permissions

Go to → New Query → Query Options

Data Load → Clear Cache → Ok


Please contact support@buzsoftware.com.au for any further questions.