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:
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
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
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.