Reporting greater than 36 periods on various financials reports - append feature

📈 Reporting & Visuals
,
#1

I’m looking at building a dataset to use with Power BI. To get myself familiar with DD, I’m starting with the multiperiod reports for P&L and BS, with period as rows selected.

1 - This would work but a couple of of problems I face. The period is limited to 36 months. There does not seem to have an append feature to add on the the previous report data downloaded. Eg I run a P&L for 3 years from 2017, and want to add the next three years to Dec 2020.

  • What would be the best way to extend this over 36 months - basically for all time?
  • It is showing a lot of nil values I.e. each P&L item repeats each month, even though there were no transactions in the month - is there a way we can remove nil values from within the DD GET request?
  • These nil values appear to be P&L account name x tracking 1 x tracking 2 - so if I had 10 of each tracking category I would have 100 nil value rows for each P&L account.

2 - I’ve looked at the account transactions options - it looks like it retrieves every journal from the beginning of time. This would be useful, but perhaps the nuclear option. A simple month by month aggregate would suffice. I suppose I can run a separate sumifs matrix (rows P&L account names, and columns aggregate by date) and use that for each of my requirements?

  • Is there are built in solution for this?
  • What happens when the data is refreshed - how does it recognise what data to append and ensure there are no duplicates?

I could not find the question or answer on the forum, so apologies in advance if this has already been answered.

Thanks!

#2

Hey @data.joe - Welcome to the community!

We are currently exploring ways to help users manage these specific requirements so may update/change this advice in the near future, please find answers below;

  1. Xero API limits to a maximum of 36 comparative periods, meaning within 1 sheet/feed, using monthly data 3 years will be your maximum grab. You can start another request on a new sheet, grabbing the following 3 years and so on. Should you need to you can refresh all sheets within one go, but this approach typically works well as older data becomes static and only the most recent will require refreshing. I’d recommend using Power Query to append these tables together or whatever your current preferred approach may be.

Ability to remove nil values should be delivered first thing tomorrow morning in Decembers update! We appreciate this adds unnecessary size to your table and file.:rocket:

  1. Have you checked on our Trial Balance MP report? Sounds like this will deliver the simply month by month aggregate you’re talking about producing from the account transactions report.

We are currently working on a template that removes the noise (edits/voids) leaving you with the final account transactions, we expect to launch this in the new year.

The account transactions append functionality works by looking at the max journal number, and bringing in transactions from here. As this is from a full audit trail (nuclear option) with these numbers being system generated, no transactions will be missed. The only room for caution is if you manually delete earlier entries from the loaded data table.

I hope this helps and look forward to seeing what visualisations you deliver in Power BI!
Thanks,
Carl

1 Like
#3

Hi Carl,

Many thanks for your answers.

  1. Yes, that would have been the approach I’d have taken. Anything that is outside of the current year will generally be locked and so perhaps a refresh of the current year would be all that is needed. I’ll experiment and see how that goes.

That’s the way I was looking at it - If we can remove the nil values it will massively reduce the file size and hopefully allow faster processing elsewhere - Looking forward to see that version!

  1. I looked at the TB option, but I believe it doesn’t identify the tracking? I may end up using the P&L report for that side of it once we can exclude those nil values.

You mentioned voids/edits. I presume the JournalID remains the same. How would any edits be reflected - would a refresh append pick up the changes, what happens to the existing journal of the same ID? Would it replace the existing journal?

I’ll spend more time to understand the various options on DD. But it looks pretty good so far!

#4

Hey @data.joe ,

Great! Any further feedback do please let us know.

You will notice the P&L data table now removes your nil values :slight_smile:

Unfortunately, Xero do not currently expose tracking at TB level via the API (big ask from us developers), our users will normally handle this as you have mentioned, using the TB for balance sheet entries and P&L to bring the tracking in. If you need tracking for balance sheet accounts, you can either surface this 1 by 1 using our filters, or use the account transactions and wrap up as mentioned.

How Xero handles this is if a transaction is edited, 2 new journals will be generated, 1 reversing the original transaction, and 1 with the final. The sourceID will remain the same, but the journal number will grow in system generated order. Therefore, the logic you are after is the maximum journal number per source ID, where the count is odd.

Look forward to hearing/seeing what you come up with!
Thanks,
Carl

1 Like
#5

This nil value update looks fantastic.

Ah right, I wasn’t aware how Xero treats the transactions, but that makes sense. It would create a lot of unnecessary data but I see it would work!

I’ll continue playing around with it and will be sure to let you guys know if I find anything.

Cheers,
Joe