DataDear Alternative for Xero

Home / Report Templates / DataDear Alternative for Xero

We have prepared a series of reports to assist Xero users transitioning away from DataDear. These replacement reports aim to replicate some of the common formats from DataDear.

These templates can either be used as is, or can be customised and enhanced further as required. They are not locked in any way.

We currently provide a single template that includes:

  • A Profit and Loss Report
  • A Balance Sheet Report
  • A Trial Balance Report
  • A Profit and Loss data set broken down by Tracking Categories

If you require a format that is not included, contact us to discuss it further.

Profit and Loss - DataDear Xero Template
Balance Sheet - DataDear Xero Template

Why choose OdataLink as a DataDear replacement?

OdataLink has been built to not require you to download, install or maintain any software on your computer.

Instead, it provides the data in such a way that it can be used directly within Excel.

This is possible because Excel includes two key technologies:

  • Power Query
  • OData Feed Support

Power Query is a very powerful querying engine. It allows anyone to pull, transform and manipulate most types of data. It’s the same engine at the heart of Power BI.

OData is a data format first developed by Microsoft as a way to standardise data access.

What all this means is that your Xero data will be accessible directly within Excel whenever you need it. It means you can use pre-built reports. It also means you can customise these templates as you see fit.

And even better, this technology works with both Excel and Power BI (and other software).

Getting Started

To get started with replacing DataDear with OdataLink, you will need to:

  1. Download the template
  2. Configure OdataLink
  3. Configure the template
  4. Refresh the reports

Download the Template

We recommend you first download the template from the following this link. You’ll be able to visually confirm if the layout we provide matches your existing DataDear reports. If you require a format that is not included, contact us to discuss it further.

Download the DataDear Xero PL, BS and TB Reports.xlsx

Configure OdataLink

In order to use the template, you will need to Setup a trial of OdataLink and connect it to your Xero Organisation (data file).

At the very list, your model must include the following endpoints.

  • Accounts
  • BalanceSheet
  • DatesByMonth
  • Organisation
  • ProfitAndLoss
  • ProfitAndLossByTrackingCategory
  • TrackingCategories
  • TrialBalance

Configure the template

Once OdataLink is configured, you configure the template:

  1. Link the template to your own OdataLink OData Feed.
  2. Enter the parameters for the reports in the Inputs sheet.
  3. Remove any reports you do not require.

Link the template to your own OdataLink OData Feed.

To link the reports to your OData Feed URL, follow these steps.

  1. Go to https://app.odatalink.com website.
  2. Select OData Feeds from the menu on the left-hand side.
  3. Copy the URL listed next to your Xero Organisation data file.
  4. Open the Excel Report.
  5. Select the Data tab in the Excel Ribbon.
  6. Choose Get Data then Data Source Settings.
  7. Select the https://data.odatalink.com/… source listed and choose Change Source
  8. Paste the URL you copied and click OK.
  9. Follow the prompts.

Enter the parameters for the reports in the Inputs sheet.

You will also need to configure the Inputs sheet. On this sheet, you can:

  1. Enter the Start Date and End Date for the reports.
  2. Enter the Tracking Category Names (if using the PL Tracking Sheet)

Remove any reports you do not require.

You can also remove any sheet from the template that you do not need. Note that the Inputs sheet is required for all reports. The template currently includes 4 report worksheets.

  • PL
    Provides a Profit and Loss report with months across the page.
     
  • PL Tracking
    Provides a Profit and Loss report listing amounts down the page.
     
  • BS
    Provides a Balance Sheet report with months across the page.
     
  • TB
    Provides a Trial Balance report with months across the page.
     

Refreshing the Reports

Refreshing the reports is simple. Simply go to the Data tab in the Excel Ribbon and click on the Refresh button. Excel will connect to Xero via OdataLink and download your data directly into the workbook.

Integrating the Reports into your workbooks

Finally, to integrate the report with your workbook requires you to follow a few simple steps.

First, you will need to copy the sheets FROM your existing workbook INTO this template. Because the OdataLink template we provide includes many Power Queries, it is simpler copying YOUR sheets INTO the template rather than the other way around.

Secondly, you will need to relink your reports to use the new tables. The approach you take for this depends if you used Pivot Tables or formulas.

Integrating the template with Pivot Tables

If your reports are built from Pivot Tables, it’s very simple. Simply Change the Source of your pivot tables to point to the new data tables.

Integrating the template with formulas

If your reports are built using formulas, it is a bit more complex.

You can either edit your formulas so that they source the data from the new worksheet.

Alternatively, you can attempt to copy the new worksheet’s table over your older data dear data table.

If you need advice as to the approach that will be easier for you, we are just one live chat away.