By using this website, you agree to our Terms of Use (click here)
Hi Tim
I've been tasked to write board pack Excel reports for a client. Many of these reports are Profit & Loss type reports. I thought that I could use Velixo Reports to quickly cobble these reports together for the client.
Unfortunately, this idea was quickly shot down by the powers that be - no third party software other than Excel to be used in the solution. All is not lost I thought. My solution has to be an Excel solution, with the ability to connect and refresh directly from Acumatica. Perhaps I could simply use the Generic Inquiries installed by Velixo Reports to quickly produce my reports using a combination of Power Query and Excel Pivot Tables.
It quickly became apparent that I had a problem with my logic. I'd pretty much exclusively used the bundled VelixoReports-GLHistory Generic Inquiry to produce my output and I noticed that I was not getting the expected results. Clearly there must be data within VelixoReports-GLHistoryLastActivity that is also required. In hindsight, this was obvious but I was looking for a quick solution. By trial and error, I identified the missing records I was looking for in the output of VelixoReports-GLHistoryLastActivity, with the correct Subaccount but now recorded under the Retained Earnings account.
Obviously I need to merge the required data from VelixoReports-GLHistory and VelixoReports-GLHistoryLastActivity, but how to revert to the Account number prior being changed to the Retained Earnings Account. Just looking at the Generic Inquiries involved, it is not clear to me how this can be done. I'm also looking for confirmation on how to identify which records qualify for merging (with Power Query) into VelixoReports-GLHistory from VelixoReports-GLHistoryLastActivity.
Just to clarify, I will be using Pivot Tables within Excel to allow the user to select "Ledger", Branch" and "Financial Period".
I've been stuck on this for days now and am pressured to provide a solution soon 🙁
Hoping that you can help.
Kind Regards - Grant
I hope you are charging by the hour! (That from a guy who NEVER charges by the hour!)
With Velixo or ARM, you can knock out pretty much any kind of P&L you will need. If you have to build it from scratch, in Excel, without a tool designed to do just that, you are going to end up cobbling together a solution that will require constant care and feeding. The TCO will to really high.
Having said that, I created a GI for a customer to recreate a detailed trail balance that I then exposed via ODATA and consumed in Excel via Power Query. I did this to help with complex allocation routines required by the customer. The process has to be done in Excel and is dependent on GL account balances, both YTD and Net Activity by account by individual subaccount. We also have to be able to filter by multiple segments within the subaccount. The customer has 6 segments in their 15 character subaccount.
To build the GI, I reverse engineered the Detail Trail Balance report in Acumatica. If you decide to do the same thing, be sure to pay close attention to the tables used, the columns in the join, the logic in the joins, and exactly which columns they pull. There are several columns in the primary tables that look like the should give what you need but don't.
I strongly recommend going back to the customer and presenting the options again and this time associate a costs with each: 'build you own', Velixo, and ARM. If you don't, the customer will think that the initial cost, TCO, and output results for all three are exactly the same. We know they are not.
If you give them your best advice and they still require you to take a path you know wont be successful, you have to decide how much of that risk you want to assume and how you want to be compensated for it.
Creating fully functional financial statements using these generic inquiries will be hard. The inquiries are the plumbing of Velixo Reports and are similar to the BQL queries used internally by ARM in Acumatica, but they're not designed to be used ad-hoc via Excel. If you only care about a P&L for a specific financial period you might be ok just fetching GLHistory, but for something like a balance sheet you're in for a lot of fun. I've worked on databases that have 500 000+ rows in the GLHistory table, and I cannot imagine having to refresh and process all of that every time I process Excel. I'd be happy to jump on a call and convince your management that their best bet for Excel-based financial reporting is to use Velixo Reports.
Gabriel
Hi Grant,
I had exactly the same thought as Shawn before reading his response. This really seems like a situation where the customer needs a presentation about Total Cost of Ownership. Pivot Tables in Excel are awesome, especially in Excel 2016 because Power BI is baked into them and you can do crazy things with the DAX formula language.
But Pivot Tables are not good at financial statements. There are some workarounds like this one using the Power BI features available in Excel 2016 (click here), but they still have limitations. Velixo Reports is EXACTLY designed for what you need.
I think that us IT professionals need some training on how to program humans to do what we want them to do. We know how to program the computer to do what we want it to do, but in this situation what you need is the ability to program a human.
All that being said, your post is news to me. I always thought that everything depended on GLHistory. I didn't realize that you need to include some other tables to have a complete historical picture. Can you share more about what specifically is missing from GLHistory?
Hi Tim
In response to your question "what specifically is missing from GLHistory?" Whilst cobbling together my attempt at creating P&L reports in Excel based on the Velixo Reports Generic Inquiries, I observed the following;
- As a benchmark I created a P&L report using Velixo Reports Add In functions. The results were what I expected. They compare exactly with the Acumatica P&L report.
- When creating my own P&L report with standard Excel Pivot Table functionality I noticed that there was a variance in the output.
To confirm this, have a look at the Sales Demo Acumatica P&L report with the parameters "ACTUAL" Ledger and Financial Period "03-2017". Click YTD value for "Sales Revenue" to view the General Ledger records for the period. You will note there is an entry for "VA" Branch for Subaccount IND-000 for the period "02-2017". If you cobble together Generic Inquiries for GLHistory and GLHistoryByPeriod you will note the following;
- In GLHistory, there is no record for "ACTUAL" Ledger, "VA" Branch, Account 40000 (Sales Revenue), "IND000" Subaccount for the period "03-2017".
- In GLHistoryByPeriod, there is a record for "ACTUAL" Ledger, "VA" Branch, Account 40000 (Sales Revenue), "IND000" Subaccount for the period "03-2017" and a LastActivityPeriod of "02-2017".
So my assumption is that a simple Odata query of GLHistory will not suffice and some additional work needs to be done to plug the gap. I might be wrong - I don't have an accounting background.
Kind Regards - Grant
Thank you all for your responses and advice which is much appreciated. I had already attempted to reverse engineer the Detailed Trial Balance Report into a Generic Inquiry as Shawn suggested, without any success I'm afraid. Perhaps fatigue played a part but I'm going to attempt it again today. I will post again today with my findings and observations.
Kind Regards - Grant
Ah, I see. Thanks for the example, that explains it perfectly.
So it seems that GLHistory only has a record for financial periods where there is activity. That must be why Gabriel is saying that Balance Sheets would be difficult because they are basically year-to-date numbers. Profit & Loss statements with year-to-date numbers have the same difficulty.
You could use a Power BI measure to find the most recent record based on the date (even if it's a prior period), then grab the amount in the FinYtdBalance column. But I really think you'd run into a lot more problems down the road trying to use a pivot table for financial statements. You should take Gabriel's offer to see if he can convince the client on a phone call 🙂