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.