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. |
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. |
```sql theme={"dark"}
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"
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
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.
1. Perform a **Collapse Columns** action in which you unpivot the 12 date columns.
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.
3. Perform a **Set Column Type** action in which you change the data type of the number of months column to a numeric type.
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.
Your output format should be as follows: one row per month instead of one column per month
