Excel reporting using OData

Excel can connect directly to your BUZ data through an OData feed, so you can build reports that refresh on demand rather than re-exporting from BUZ each time. For an introduction to the concept, see What is OData?.

Note

You need Excel’s data-connection feature: Get & Transform (Excel 2016 and later) or Power Query (Excel 2010 and 2013). If your version doesn’t have it, install the Power Query add-in from Microsoft first.

Before you start: find your organisation code

You’ll need your five-character BUZ organisation code to sign in. Find it in BUZ under Reports — it’s shown on the right-hand side of the Reports section. If you can’t locate it, contact BUZ Support at support@buzsoftware.com.au.

Connect your data via Excel

1. Open Excel and go to the Data tab.

2. Select New Query → From Other Sources → From OData Feed.

3. Enter your feed URL, replacing the placeholder with your organisation code:

   https://api.buzmanager.com/reports/YOUR-ORGANISATION-CODE

4. Sign in with your BUZ credentials. Your user name is your organisation code, a forward slash, then your BUZ username — for example ABCDE/jsmith.

5. Choose the report you want, then click Transform Data.

   Caution

   Choose Transform Data, not Load. You need to open the Query Editor so you can apply a date filter before the data loads — otherwise the query can time out.

6. The Query Editor opens and records each step you apply to the data. From here you can filter the data or write your own transformations in the M language using the Advanced Editor.

Add a date/time filter

Every OData query must include a date/time filter. Without one, the query keeps running until it times out and returns an error.

1. In the Query Editor, click the arrow next to the date column you want to filter (for example OrderDate or CreatedDate).

2. Choose a date filter. The Between option is usually the easiest to work with.

3. Select the “from” and “to” dates you want the report to cover.

4. Once your filters are set, select Close & Load to load the data into Excel.

Tip

Keep date ranges reasonable — during business hours reports are capped at 12 months and 5,000 rows. See OData restrictions for the full details.

Update an existing Excel OData report

1. Open the workbook and go to Data → Show Queries (or Queries & Connections).

2. If you’re no longer connected, Excel shows a connection error — reconnect using your BUZ credentials as above.

3. In the Power Query Editor, find your filter in the Applied Steps list. Right-click the settings icon next to it and choose Edit Settings.

4. Update the filter values (for example, a new date range).

5. Select Close & Load to refresh the report with the new settings.

Tips for using Get & Transform

Change the organisation you’re connected to

1. Go to Data → New Query → Data Source Settings.

2. Select the BUZ data source, then click Edit Permissions → Edit Credentials.

3. Enter the credentials for the other organisation and save. You can now get data for that organisation.

Refresh your data

To pull the latest data from BUZ, select Refresh from the Table Design (or Query) tab.

Note

OData reports are not real-time. Data is synchronised from BUZ periodically and can take up to 24 hours to appear after it’s created or updated.

Troubleshooting: errors loading data

If a report won’t load:

1. Clear the permissions and try again. Go to Data → New Query → Data Source Settings, select the BUZ source, and clear its permissions, then reconnect with your credentials.

2. Clear the cache if the problem persists. Go to New Query → Query Options → Data Load → Clear Cache, then click OK and reconnect.

Still stuck? Contact support@buzsoftware.com.au.