Intro
Xero develops cloud-based accounting software for small- and medium-sized businesses. You can use this connector to retrieve data about bank accounts, transactions, expense claims, contacts, and the like. To establish the connection between Domo and Xero, a user must first authenticate to Xero and then grant Domo permission to access his or her data. To learn more about the Xero API, visit their page (http://developer.xero.com/documentat.../api-overview/).
You connect to your Xero 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 Adding a DataSet Using a Data Connector.
Prerequisites
To connect to your Xero account and create a DataSet, you must have the email address and password you use to log into your Xero account.
Connecting to Your Xero Account
This section enumerates the options in the Credentials and Details panes in the Xero 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 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 credentials. Once you have entered valid Xero 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.
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:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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.
- Create a Domo SQL query as follows:
Input: Xero DataSet created using the report "Budget"
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
- 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.
- Perform a Collapse Columns action in which you unpivot the 12 date columns.
- 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.
- Perform a Set Column Type action in which you change the data type of the number of months column to a numeric type.
- 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.
- Perform a Collapse Columns action in which you unpivot the 12 date columns.
Your output format should be as follows: one row per month instead of one column per month
Comments
0 comments
Please sign in to leave a comment.