Skip to main content

​
Intro

Xero gives small businesses a firm grasp on their financial health, with tools to monitor cash flow, invoices, expenses, and more. When you connect Xero to Domo, you get a platform to collaborate around real-time data so you can move forward with a single version of the truth. Use the Domo Xero connector to bring your expenses into Domo, where you can easily track your finances and see exactly where your money is going. To learn more about the Xero API, visit their page (http://developer.xero.com/documentat…/api-overview/ ). After your data is in Domo, you can assemble, organize, and visualize your Xero finance data any way you would like. Get instant notifications when your financial metrics hit thresholds that you define. You connect to your Xero Custom account in the Data Center. This topic discusses the fields and menus that are specific to the Xero connector user interface. General information for adding DataSets, setting update schedules, and editing DataSet information is discussed in DataSet Using a Data Connector.

​
Prerequisites

To connect to your Xero Custom account and create a DataSet, you must have the email address and password you use to log into your Xero account.
  • Client ID
  • Client Secret
  • Scope

​
Connecting to Your Xero Custom Account

This section enumerates the options in the Credentials and Details panes in the Xero Custom Connector page. The components of the other panes in this page, Scheduling and Name & Describe Your DataSet, are universal across most connector types and are discussed in greater length in Adding a DataSet Using a Data Connector.

​
Credentials Pane

The Domo Xero Custom connector uses OAuth to connect, so there is no need to enter credentials within Domo. Click Connect (or select Add Account if you have existing Xero accounts in Domo) to open the Xero OAuth screen where you can enter your Xero custom credentials. This pane contains fields for entering credentials to connect to your Domo developer account as well as your Xero Custom account. The following table describes what is needed for each field:

Menu

Description

Client ID

Enter your Xero custom connection client id.
Client Secret Enter your Xero custom connection client secret.
Scope enter a space separated list of scopes that were specified when the Xero custom connection was set up. You can find the full list of scopes available here.
Once you have entered valid Xero custom credentials, you can use the same account any time you go to create a new Xero DataSet. You can manage connector accounts in the Accounts tab in the Data Center. For more information about this tab, see Managing User Accounts for Connectors.
Note: You can only have one Xero Custom account authenticated at a time with a given set of credentials. If you try to use OAuth with Xero anywhere else, it will invalidate your Xero Custom account in Domo.
Note: If you are already logged into Xero when you connect in Domo, you are authenticated automatically when you click Add account. If you want to connect to an account that is different from the one you are logged into, you must first log out of Xero.

​
Details Pane

This pane contains a primary Reports menu, along with various other menus which may or may not appear depending on the report type you select.

Menu

Description

Report

Select the Xero report you want to run. The following reports are available:

Accounts Returns a list of accounts in your Xero Organization.
Aged Payables by Contact Returns aged payable for all invoices within the specified time range.
Aged Receivables by Contact Returns aged receivable for all invoices within the specified time range.
Balance Sheet Returns a balance sheet for the current month to date.
Bank Statement Returns bank statement lines for each bank account in your Xero Organization.
Bank Summary Returns the balances and cash movements for each bank account.
Bank Transactions Returns a list of any spend or receive transactions.
Bank Transfers Returns a list of bank transfers.
Branding Themes Returns a list of branding themes defined for your Xero organization.
Budget Summary Returns a summary of your monthly budget.
Contacts Returns a list of contacts.
Credit Notes Returns a list of credit notes.
Currencies Returns a list of currencies for your organization.
Employees Returns a list of employees used in Xero payrun functionality.
Executive Summary Returns a summary including monthly totals and some common business ratios.
Expense Claims Returns a list of expense claims and their status.
Invoices Returns a list of invoices or purchase bills.
Items Returns a list of items.
Journals (deprecated) Returns a list of journal entries. Xero will no longer support the functionality of this report, and its accuracy cannot be guaranteed. Please use the Journals (Append) report instead.
Journals (Append) Returns a list of journal entries created or modified after the specified date.
Manual Journals Returns a list of manual journal entries.
Organizations Returns information about your Xero organization.
Payments Returns a list of payments for invoices and credit notes.
Profit and Loss Returns a profit and loss report.
Profit and Loss Aggregated Returns a profit and loss report with provided garnularity for selected date range.
Purchase Orders Returns a list of purchase orders.
Receipts Returns a list of draft expense claim receipts for any user.
Repeating Invoices Returns a list of repeating invoice templates.
Tax Rates Returns tax rates for your Xero organization.
Tracking Categories Returns tracking categories and options for your Xero organization.
Trial Balance Returns a trial balance for the current month to date.
Users Returns a list of users for your Xero organization.
Past Days Enter the number of past days that represents the start date of the report. You can use any of the following formats: x , x day , or x days , where x is a number between 1 and 365.
Contact Select the contact for which you want to retrieve data.
Bank Account Select the bank account for which you want to retrieve data.
To Days Enter the number of past days that represents the end date of the report. You can use any of the following formats: x , x day , or x days , where x is a number between 1 and 365.
Offset Enter a journal number. The report will retrieve only journals with a number higher than this number.

​
Other Panes

For information about the remaining sections of the connector interface, including how to configure scheduling, retry, and update options, see Adding a DataSet Using a Data Connector.

​
Troubleshooting for Consulting/Support

​
Dates in Column Names

Some Xero reports return data in which the run date is used as a column name. For example, if you were to run the “Profit & Loss” report on Feburary 17th, 2017, an amount column called “Row__17_Feb_17” would appear. This prevents these columns from being used in a DataFlow or card, as the name of the column changes with each scheduled DataSet update. To resolve this issue, we recommend replacing dates in column names with generic column names. You can use a Domo SQL query to replace the faulty column with a generically named column, which can then be referred to in a Magic DataFlow. In the following example, we replace the column name “Row__16_Feb_17” with the name “ProfitLossAmount.” 
select NULL AS Row_Id, NULL AS Row_ProviderName, NULL AS Row_DateTimeUTC, NULL AS Row_ReportName, NULL AS Row_ReportDate, NULL ​AS Row_UpdatedDateUTC, NULL AS Row_ReportTitle, NULL AS Row_column_0, NULL AS ProfitLossAmount, NULL AS _BATCH_ID_, NULL AS _BATCH_LAST_RUN_ from xero_profit_and_loss_report
where 1 = 0 --this row returns nothing but allows setting the column names for the query
union all
select * from xero_profit_and_loss_report

​
Unwanted Pivot Tables

Often, data with the column naming issue described above is represented as a pivot table. You cannot use the Collapse Columns action IN ETL to unpivot this data because the column names change for each scheduled run. An example of this is the “Budget” report. To resolve this issue, unpivot columns that have been named with dates, but retain date values as rows so the output DataSet has one date row per column. In the following example, we first use a Domo SQL query to rename the columns to generic names. Then we use a Magic ETL DataFlow to perform a Collapse Columns action and then convert the generic column name (which is now row data) back into a date.
  1. Create a Domo SQL query as follows: Input: Xero DataSet created using the report “Budget”
    xero_unpivot_1.png
    Transform: Replace the column names “Row__Feb-17,” “Row__Mar-17,” etc. with “Month1,” “Month2,” etc. using the method described above. Output Format: Renamed columns
    xero_unpivot_2.png
  2. In a Domo Magic DataFlow, unpivot the output data defined above and replace the Month1, Month2 etc. values with dates based on the _BATCH_LAST_RUN date.
    xero_unpivot_3.png
    1. Perform a Collapse Columns action in which you unpivot the 12 date columns.
      xero_unpivot_4.png
    2. Perform a Value Mapper action in which you create a new field that contains the number of months to add to the BATCH_LAST_RUN_ date to get the correct date field for the row.
      xero_unpivot_5.png
    3. Perform a Set Column Type action in which you change the data type of the number of months column to a numeric type.
      xero_unpivot_6.png
    4. Perform a Date Operations action in which you use the Add Months to Date operation to add the number of months to the _BATCH_LAST_RUN_ date to determine the date for the row.
      xero_unpivot_7.png
Your output format should be as follows: one row per month instead of one column per month
xero_unpivot_8.png