Last week I mentioned the recent announcement by Acumatica which connects Acumatica to Microsoft Excel, Power BI, Power Pivot, and Power Query.
Then I made my case for why I think that Excel and the “Power” tools are a big deal.
What about Report Designer?
But where does the Report Designer in Acumatica fit into all of this?
Hold onto your seats, the world’s fastest growing Cloud ERP software (Acumatica) is about to collide with the world’s most popular Reporting and Business Intelligence software (Microsoft Excel).
Acumatica made the announcement this morning (click here) at Microsoft Build, the annual conference aimed at developers who use Microsoft’s next generation technologies. I just watched the event live at www.buildwindows.com and the Acumatica OData solution occupied the stage for 5 minutes which is a lot of time considering how much they cram into the keynote (watch and you’ll see what I mean).
The OData feature in Acumatica allows users to connect Excel to virtually any Acumatica data that they want. No more manual exporting and importing. You can now build an Excel report with Acumatica data and have it refreshed with the click of a button.
So what’s the big deal about Excel?
“Excel,” I hear you asking, “isn’t that just a spreadsheet program?”
Ah, yes, it is a spreadsheet program, but it is also the most popular Reporting and Business Intelligence software on the planet.
Wall Street would collapse without Excel, the government’s budget and census projections would be nonexistent without Excel, and university research would be nothing more than data, lacking any analysis, without Microsoft Excel.
Ok, maybe I exaggerate a little, but take a look at the following video featuring the two creators of VisiCalc, essentially the first version of Microsoft Excel, and maybe you’ll agree with me.
Click the picture to see the video.
In the June 1981 issue of Fortune magazine, VisiCalc was featured in an article entitled, “Software’s Greatest hits.”
The January 1982 issue of Inc. magazine featured the creators of VisiCalc on the cover highlighting the birth of the new computer industry. Notably, Bill Gates was pictured on the inside story, but not prominently on the cover.
In September 1985, The Wall Street Journal made the following comment in an editorial:
We’ve been reading stories this week about all the returning Members of Congress who say that virtually none of their constituents are interested in Ronald Reagan’s tax reforms. Could be, but we doubt it. Our guess is that people everywhere have by now filled Visicalc spreadsheets, endless pages of eight-column accountant’s paper, yellow legal pads, blank stationery and envelope backs with calculations of how they’d fare with the president’s tax-revision proposals…
It’s notable that in 1985 the spreadsheet had already become synonymous with important decision-making.
VisiCalc eventually became Microsoft Excel and Microsoft Excel has now been installed on over a billion computers worldwide.
We have the same situation today that we had in the 1980s. Walk the halls of any company, duck into a meeting, and look at the reports that are being used to make critical business decisions. You will probably find that the majority of those reports were created in Microsoft Excel.
The hidden truth about Excel
By now, all the reporting professionals are nodding their heads. Why? Because they share a common experience with Mr Excel (aka Bill Jelen).
Click the image below and listen from the 2 minute mark to the 5 minute mark as Bill tells his story of how he used spreadsheets to do what a $100,000 reporting software package couldn’t do.
Bill discovered what every reporting person eventually discovers. That is, the most popular button in any reporting software:
I personally have experienced this many times, whether it was during my consulting days or now at the manufacturing company that I work for. As an IT person, I used software like Crystal, SSRS, and FRx to create reports. But, time after time, people would ask me how they could export the reports to Excel.
Eventually I started to pay attention to the reports that were being brought to meetings. It was then I discovered that my reports were simply exported to Excel where the “real” reporting actually took place.
Fancy Reporting Software
These days, the Reporting and Business Intelligence software market has become very crowded: Tableau, Qlikview, Domo, Logi Analytics, Solver, BizNet, Renovo, and the list goes on…
Some of these vendors (notably Tableau, Qlikview, and Domo) try to promote what I call “out of the box analysis” where they imply that, if you buy their software, you will instantly have insightful dashboards at your fingertips.
It’s as if their software can somehow magically understand your business and spit out meaningful reports without you having to do any thinking.
Rob Collie sums this up with the 3 Big Lies of Data which are:
A new era for Excel
In late 2006, a Microsoft employee launched a secret incubation project called “Gemini” with a goal to make SSAS (SQL Server Analysis Services) available to users of Microsoft Excel.
Microsoft SQL Server Analysis Services is one of the most popular analysis software solutions for medium sized businesses in the world.
It’s important to note that “project Gemini” was initiated by someone from the SSAS team, not the Excel team, at Microsoft.
“Project Gemini” was released as Power Pivot in 2009 as a free add-in for Excel. The goal was achieved in that Excel users now had access to the power of SSAS, but in an interface that was familiar to them.
Power Pivot gained popularity very quickly and it eventually became the centerpiece of the entire Microsoft BI strategy.
This strategy eventually landed them in a very prominent position on the Gartner Magic Quadrant for Business Intelligence and Analytics Platforms:
After Power Pivot, Microsoft added Power Map, Power Query, and Power View to the stack. All these solutions were built as add-ins for Excel.
More recently, Microsoft launched Power BI which makes the stack available in your web-browser or on your phone, without the need for Excel. You can also query your data by typing sentences and the software will create a graphical report for you based on what you typed.
These tools basically amount to “Excel on steroids” and they have taken what was already the world’s most popular Reporting and Business Intelligence software and made it 100 times more compelling.
I personally have been using these “power” tools since 2012 at the company I work for and have found them to be better than any reporting software that I have ever worked with.
Acumatica and Excel with Power Pivot, Power Query, and Power BI
Most ERP packages for the mid-market have their own proprietary reporting tools.
Or, you can pay for a 3rd party solution.
By connecting directly to Excel, Acumatica just picked up 30 years of reporting experience (Excel was introduced in 1985).
The Acumatica reporting tools just went from a bicycle (Report Designer) to a Lamborghini (Excel with Power Pivot, Power Query, and Power BI) overnight.
My Personal Direction
When I started PerpetualAcumaticaLearner.com in April 2013, I was just interested in learning about Acumatica in general.
The idea to connect Acumatica to Excel first occurred to me in January 2014 when I was trying to do it with Power Query (click here), but Gabriel from Acumatica had a much better idea to use OData (click here).
When I moved things here to TimRodman.com in January of this year, I did it with the expectation that Acumatica would eventually be connecting to Excel with OData.
I had become very excited about Power Pivot because of what I experienced at the company that I work for. For more on that experience, click here.
I figured I would learn Report Designer while I waited for Acumatica to implement OData.
I just never thought that they could implement it so quickly. I think that part of the reason why they were able to do it so fast was because they partnered with the Microsoft engineers. This is a pattern with Acumatica that I have noticed in that their development team doesn’t operate as a closed unit. They aren’t afraid to reach out and build on top of other solutions. This is a philosophical difference between a synergistic web-based vendor like Acumatica and the siloed approach that most traditional ERP vendors have become enslaved to. And it’s another reason why I think Acumatica is more of a platform than just an application.
I have already used OData to deploy Excel reports for two of my clients and it works great.
The Future of This Website
I think that Report Designer is only for 5% of the reports that a company needs. It’s great for things like Checks, Purchase Orders, Sales Orders, Invoices, Customer Statements, etc.
As for the other 95%, I believe that Excel with Power Pivot, Power Query, and Power BI is going to become the go to solution.
With this in view, my posts going forward will focus on Excel with Power Pivot, Power Query, and Power BI. However, I will continue my review of the standard reports on Thursdays because they give me insight into DAC which is critical to building the Generic Inquiry screens that the Acumatica OData connections depend on. But more on that in future posts.
Suffice to say, I am very happy about the OData announcement today, and thrilled that it happened on the 2 year anniversary of my first post (click here).
There are a lot of new possibilities to explore and it’s going to be a fun adventure.
Please feel free to comment with your thoughts and let’s keep learning together.
My last post was about hyperlinks and I’d like to continue on the same theme in this post, but use an actual example in Excel.
Sometimes it would be nice to make an Excel file that contains hyperlinks to the actual screens in Acumatica for each record that you’re viewing. In this example, I’m going to take a simple list of vendors in Excel and add a hyperlink to each vendor ID that will drill into the Vendors (AP303000) screen in Acumatica for that vendor.
Basically, all you need to do is the following:
- Start with an Excel file that has a list of vendor IDs.
- Go to the Vendors (AP303000) screen in Acumatica, pick a random vendor, and click Help -> Get Link in the upper right-hand corner.
- Copy the External Link: value to your clipboard.
- Paste the External Link: into an Excel formula like this:
- Change the hard-coded vendor ID to an Excel cell reference like this:
- Wrap the link in the Excel HYPERLINK function like this:
- Hide the original column in Excel that had the vendor ID and just display the new vendor ID with the hyperlink.
- Clicking the hyperlink for a vendor should take you to the Vendors (AP303000) screen in Acumatica for that specific vendor.
- You will need to login to Acumatica and your login will need permission to the Vendors (AP303000) screen.
Here is a quick video which walks through the steps:
Acumatica is very good at integrating with Microsoft Excel. In this video I demonstrate the ability to export to Excel and import/update from Excel. I am using the Chart Of Accounts (GL202500) screen, but this feature is available on a number of different screens.