— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
— Tim Rodman (@TimRodman) January 30, 2018
There are a few low hurdles that you need to overcome. Checkout this post and video.
From Data Hell to Bliss: Getting the Most Out of Your Acumatica Data
Seven weeks ago I started working for Acumatica. During the past seven weeks, I have been considering whether or not to continue posting on this blog.
I have a lot of ideas for posts (some written down, some in my head), but I wanted to see how the first seven weeks would go as an Acumatica employee before deciding whether or not to continue blogging here.
Here is what my Evernote blog post ideas document looks like at the moment:
- Power BI Top 5 Customers by Revenue
- Creating a data source based on ARTran
- Connecting to Power BI and adding a monthly bucket column, hardcoding the revenue account filter
- Add monthly filter and drilldown to item
- Top 5 mobile
- Add an alert if top 5 are more than 50% of total
- Power Update Introduction
- Mainly needed if you want a simple way to “mashup” with on-prem data sources
- Tableau Public map visualization of sales by state connected to OData
- Power Query Intro in Power BI Desktop
- Add custom calculated column -> PT/PC
- Data shaping (more than just rows and columns) – IT tools vs new Business tool called Power Query
- Doug Johnson SQL View vs Power Query
- Link to Miguel Escobar training course
- Power Pivot Intro and link to Rob course
- Chandoo dashboard stuff
- Why ERP summary tables are obsolete
- Talk about the experience with a CFO where he was using a report based on summary tables and they didn’t have enough detail when he wanted to slice and dice
- Power BI lets you filter or pivot on anything you want. You don’t get handcuffed. You are free to ask whatever questions you want about your data.
- Talk about pivoting with Power Query, basically on-the-fly summary tables
- What lurks behind CSV?
- What is CSV and why does it exist?
- Rob Collie’s comment that most of his engagements are sourced from legacy CSV files
- ERP (French) and Excel (Chinese)
- Until now, you had to hire a translator named CSV
- Acumatica decided to go out and learn how to speak Chinese (OData)
- Acumatica Reporting – Where to Start?
- Graphic with Report Designer logo and arrows to “no options”
- Graphic with Excel logo and arrows to Power BI, Office 365, Power Update, Report Designer
- Excel is the best prototyping tool and many times good enough for the end user
- Acumatica and Microsoft are a winning combination
- I noted a while back that Acumatica is aligned with Microsoft while NetSuite is aligned with Oracle.
- Since Satya took over the reigns at Microsoft, they have sped ahead with their technology. So much so that NetSuite recently ditched Amazon for Microsoft Azure.
- Of course, it pays to go with Acumatica which allows you to deploy with SaaS or on Azure, Amazon, 3rd party hosting, or on-premise.
- Simple, low-powered devices that display location-relevant KPI metrics throughout an office:
- Steve Jobs design of Apple headquarters intentionally to help people run into each other and interact
- The importance of collaborative working space and displaying relevant information throughout a three dimensional environment, not just on a screen in a cubicle
- The danger of the all-in-one remote or the cell phone screen that does too many things
- The reliability of the digital stadium scoreboard being due to it’s fixed geographical location and that it only delivers one piece of information and delivers it well (without the interruption of commercials like on the jumbotron)
- The human brain’s ability to remember things based on their geographical context
- Binary vs Analog
- Human beings will always be analog
- Implementing ERP is more about the analog side of things than the binary side of things
- FRx and multiple data source reporting
- Remodeling vs building from scratch
- Building up vs tearing down
- There are two philosophies: fit your business to the ERP software or fit the ERP software to your business
- Pick a product that can meet your current needs or pick a product that can conform to your current and future needs
- Acumatica and the SSRS Rumor
- Bye bye paper
- Paper separates Gen X from Millennials
- Living Information vs Information Corpses
- Almost want to replace the word Report with the phrase “Information Delivery Vehicle” (IDV)
- Organic, biological, static, inorganic, DNA
- The unchangeable trust factor: why paper lives on
- Paper is illicit, like cash to a drug dealer
- I still use paper occasionally, not because I’m old but because I’m human. Human beings are analog, not digital, beings.
- Democrats vs Republicans and Domo vs Excel
- Are we destined for 50/50?
- Does this same debate apply to Hosted vs On-Premise
- Waterfall Income Statement
- A Pivot Table Income Statement
- My Story (Part 1)
- Started at Deloitte and Touche in audit. Clients always had trouble pulling information from their systems to give to us in a usable format.
- Serendipitously landed in ERP in 2005 where I learned SQL and how to pull whatever information I wanted out of the system. A dream come true!
- I eventually was introduced to the world of Business Intelligence, Data Warehousing, and Data Discovery tools. So much better than Crystal reports!
- My Story (Part 2)
- From “focus on tools” to “focus on people”. The real Business Intelligence is people. Reports don’t make decisions, people make decisions.
- From working with reporting tools that were like “working with concrete” to those that were like “working with clay”
- From focusing on “how to” geeky technical nuances to philosophical musings which is how we got to where we are today with this website
- The importance of numbers
- The numbers themselves tell a story: Height, Weight, Age, Grade Point Average, Batting Average, Salary, Annual Revenue, Headcount, Outside Temperature. All of these numbers paint a picture and tell a story.
- Data viz is the cherry on top, not the ice cream sundae.
- The importance of data visualization
- The importance of infographics
- Trust – The foundation of enterprise reporting
- A number queried directly from the ERP system versus a number from the Excel spreadsheet on a VP’s laptop. The VP’s laptop will always win because there is a greater level of trust.
- Report Designer MICR
- Dynamically splitting the GL segment using Power Query in Excel
- Integrating Power BI tiles into Acumatica screens
- Building ERP-specific visualizations with D3 and the Power BI open framework
- Why Power BI makes sense for Mid-market ERP consulting firms
- Zero price barrier to entry for clients
- Allows you to have a conversation with EVERY SINGLE ONE of your clients, not just the big spenders
- Easy to prove massive value in a short amount of time
- Most clients are already reporting in Excel, the BI Trojan Horse (at least to some extent).
So, ya, have a lot of ideas for future blog posts.
However, after a lot of careful consideration, I have decided to hit the pause button.
Things are much busier at Acumatica than they were at my last job and I just don’t have the time or energy at the moment to continue blogging. I really want to, but I’m just not able to at this time.
When I started work at Acumatica, I also started going to the gym again (it’s been a while), in the morning before work, and I LOVE it. If I were to continue late night blogging, it would kill my morning gym time and I’m just not willing to give it up.
In order to hit the gym in the morning, I have to go to bed at a reasonable time (10ish to 10:30ish). Basically I get home, play with the kids for a little bit, help put them to bed (a long process), and then it’s time for me to go to bed.
So, that’s it. Things will go quiet here on the blog.
I might pick it up again at some point in the future, but we’ll have to wait and see.
It’s a shame because there are many, many exciting things going on with Power BI and I would have loved to tie them in with Acumatica and the world of ERP. I firmly believe that paper-based reports will continue to give way to reports on your laptop screen, on your phone, or on the tv monitor in the conference room. Power BI is in a great position to deliver these kinds of reports and Acumatica is in a great position to feed Power BI with the kind of ERP data it needs to be valuable.
Blogging was a very fun hobby for me and I have enjoyed it very much. Regarding hobbies, I have always liked Mark Twain’s explanation of a hobby in the book The Adventures of Tom Sawyer:
If he had been a great and wise philosopher, like the writer of this book, he would now have comprehended that Work consists of whatever a body is obliged to do, and that Play consists of whatever a body is not obliged to do. And this would help him to understand why constructing artificial flowers or performing on a tread-mill is work, while rolling ten-pins or climbing Mont Blanc is only amusement. There are wealthy gentlemen in England who drive four-horse passenger-coaches twenty or thirty miles on a daily line, in the summer, because the privilege costs them considerable money; but if they were offered wages for the service, that would turn it into work and then they would resign.
If I were a wealthy gentleman in England, then I would probably continue blogging about Acumatica and Power BI.
But, I’m not.
I will focus instead on hitting the gym in the morning, keeping up with my new job responsibilities, and getting to bed at a reasonable hour.
Thanks to all of you who have stopped by over the last two years.
It’s been fun.
Guest post by Haibin Zhou
Quick intro from Tim: I first saw Haibin’s posts on LinkedIn sometime last year. He is an independent ERP consultant in China and he does Acumatica development work, including creating reports like the one that he takes us through below. Take it away Haibin!
Note: This posted originally appeared on LinkedIn and is reposted here with Haibin’s permission
A Landed Cost Report in Acumatica
Guest post by Robert Lightner
Quick intro from Tim: Robert Lightner (like Brade Kostreva last week) is one of a growing number of Acumatica users who have been using the product and really like what they see. So much so that they do crazy things like writing posts on their own time to share with other Acumatica aficionados. What we are witnessing here is a grassroots community that is forming before our very eyes. Speaking of community, Robert has expressed interest in starting an Acumatica User Conference. Today though, he shares with us a report that he created and asks for feedback.
Note: This posted originally appeared on LinkedIn and is reposted here with Robert’s permission
Acumatica – Fiscal Year Monthly Analysis Report by Salesperson or by Inventory Item or both (On Demand)
Guest post by Brad Kostreva
Quick intro from Tim: I was thrilled when Brad asked me last month about doing some guest posts here on the blog. We first connected about a year ago through the blog and have kept in touch ever since. I mentioned him recently on the bottom of the What is Power BI post. Unlike me, an Acumatica reporting hobbyist, Brad is actually using Acumatica, Excel, and Power BI in the real world. I’m looking forward to hearing his real world experiences in this and future posts. Take it away Brad!
Of Acumatica and Power BI – A Tale Loosely Inspired by the title of a classic novel by John Steinbeck
Tomorrow, September 17th, I will become an employee of Acumatica, filling the ERP Solutions Consultant position in the Columbus, OH office.
Acumatica has been a fun side project / hobby for me since April 30th, 2013. But I’m happy that I will now be working with it full-time. Of course, I’m also happy that I will now be getting paid too 🙂
Am I Excited?
Currently, Acumatica is the only ERP product listed on PowerBI.com:
But Power BI is a fairly new thing and many people aren’t exactly sure what it is.
So, in this post I’ll attempt to answer the question, “what is Power BI?” [Read more…]
The Current Excel Online Problem
Three weeks ago we looked at using Excel Online to deliver your awesome Excel reports to people without them having to know that you made it in Excel.
However, there currently is a problem with Excel Online. The problem is that there are actually different versions of Excel Online. There is a Free version, there is a Business version, and there is an Enterprise version. For more details, checkout this post by Mike Alexander (click here).
I personally do not understand all the differences between the versions or even if these are indeed all the versions of Excel Online. There might be others that I am not even aware of yet. This is the downside of the Microsoft BI strategy at this time. It can be tricky to navigate all of the different options.
Wow, I can’t believe that it’s been two months since my last substantial blog post! But it is Summertime and family activities take priority over the blog. We did some camping, made a trip to California to see family, and have generally been having a blast enjoying the nice Summer weather.
Now it’s time for a long overdue blog post.
Two months ago we built a very simple example of a dashboard in Excel (click here). Since we built it from an Acumatica OData connection, we were able to refresh it with live, up-to-date data by simply clicking a button in Excel.
Of course, I am a big proponent of Excel because it has a long history of being a mature reporting product. It has been built over three decades and has over 800 million users. These users have used Excel for all sorts of different applications in the real world. They of course generate feedback which has in turn steadily transformed Excel into a very mature reporting solution.
Currently, in my opinion, Microsoft Excel is the best application for consuming the OData connection that is available in Acumatica for two main reasons:
- Most companies already own Excel
- Excel is already a robust calculation and reporting software application
How robust is it? Consider this, if you go to business school, the chances are that you are going to be using Excel heavily in order to solve real world business problems. If you go to work on Wall St., you will not be able to survive, unless you acquire advanced Microsoft Excel skills. There is even an annual competition called ModelOff (click here) designed for these Wall St. types and the competition attracts over 4,000 people.
Schools like UT Austin in Texas have entire business courses that are dedicated to teaching their students Microsoft Excel. One such course is taught by professor Clint Tuttle, a man who is so passionate about Excel, that he even makes music videos like this one (click the picture to see the video):
I already mentioned the over 800 million users who are constantly placing demands for more and more features. When you consider that Excel has been around for 30 years, you realize that there are a lot of features available. It is a very mature application. Even Bill Jelen (aka Mr. Excel) admits that he rarely walks into a room to give a seminar about Excel without learning something new himself.
Bottom line, even though Excel is not an all-encompassing reporting solution, it is a lot more powerful than most people realize.
The Excel Problem for Acumatica Users
This is all great, but we have a problem.
With the simple dashboard that we built two months ago, someone has to open Microsoft Excel on their desktop computer in order to use it.
This is especially problematic for an Acumatica user because an Acumatica user is accustomed to being free to access Acumatica anywhere they want. They can access it from their computer, they can access it from their phone, and they are not tied to their Microsoft Windows desktop environment.
I’ll admit that it can seem archaic to an Acumatica user when they are told that they need to use Desktop Microsoft Excel in order to do some reporting.
Wouldn’t it be nice if I could open an Excel file in my web browser just like I open a regular web page, just like I open Acumatica?
The good news is that there already is such an application and it is called Excel Online.
Excel Online is web-based Excel.
Applications like Excel Online have received a lot of attention from Satya Nadella, the new CEO at Microsoft. Satya has talked a lot about making Microsoft a cloud company, a company that delivers their software in the cloud, in the browser, on mobile devices, and not just on Desktop computers.
What has been interesting is to see how quickly he is taking Microsoft in this new direction and I think many are surprised to see how quickly Microsoft is actually executing on the vision outlined by Satya.
Excel Online is one of many examples which demonstrate what the future looks like for Microsoft applications. As time goes on, Excel Online is beginning to feel more and more like Desktop Excel. Interestingly enough, Desktop Excel is beginning to feel more like Excel Online. For example, you can now embed web apps into Desktop Excel documents by inserting them from the ribbon.
This just goes to show that the entire Excel experience, both with Excel Online and with Desktop Excel, is beginning to feel like one giant web application. This is not an accident, it is simply the execution of Microsoft’s new strategy. It seems like eventually there will only be one Excel product with two versions: a Web version and a Desktop version.
Microsoft has already accomplished this with Windows. The next version of Windows, Windows 10, is one Windows code base. It is just one collection of computer code, but it can be deployed on a Desktop computer, on a Tablet, on a Phone, or even in the Cloud. I believe that we will eventually see the same thing happen with Microsoft Excel.
If this “one code base, any device” philosophy sounds familiar, it should. Acumatica has been doing this since it began in 2008 and it is a part of the Acumatica DNA.
Web-based Acumatica, meet Web-based Excel (Excel Online)
Recently I realized that, since OData is a secure way to deliver Acumatica data over the internet, it might be possible to connect to an Acumatica data source from the Excel Online web application, not just from the Excel Desktop application.
I was pleasantly surprised to find that this is indeed possible using Excel Online and Office 365.
In the video below, I would like to show you how to take the dashboard that we created two months ago, upload it to Excel Online, and actually refresh the Excel Online dashboard with live data from Acumatica, all within your web browser, without ever having to open the Desktop Excel application.
Then, as a final touch, we can take our Excel Online dashboard and embed it within Acumatica, completely hiding the fact that we used Excel to make the report! We just need to use the following secret code: &action=embedview&wdbipreview=true
Checkout the video!
The VAR 100 lists the Top 100 Value Added Resellers, ranked by revenue. Click the link above to see the report.
Included in the report is a one page summary of what happened this past year. I was very happy when I read the second paragraph (underline added for emphasis):
Meanwhile, of the VAR 100 that partnered with new software providers, Acumatica was the most popular portfolio addition, with Intacct and NetSuite also forging new partnerships with a few of the top 40 resellers.
Holy cow, this is big news! And it doesn’t even include the recent NexTec announcement because Acumatica isn’t listed under their products on this report.
It has taken a little while for Acumatica to gain traction in the mid market, but I think that we can officially say, “traction gained”.
The consulting companies that are the boots on the ground, the forces in the trenches, have recognized that Acumatica is serious about sticking to their original game plan which I like to summarize as, “make an awesome product that is delivered by an awesome partner channel”.
Many ERP software companies get sidetracked and they keep shifting their priorities around to invest in different areas of the business. The result is a confusing situation where consulting companies don’t know what to expect year after year. Acumatica has bucked this trend by continuing to invest heavily in their product and by continuing to demonstrate that they are 100% devoted to their partner channel. Bottom line, you know what you’re getting with Acumatica.
I personally think that this steady approach has a lot to do with John Howell. John sits on the board at Acumatica and he has been through this process before with Solomon ERP Software, taking a steady long-term approach.
Whatever the cause, the results speak for themselves. VARs understand the market better than anyone and they only pick up a new product like Acumatica if they truly believe that it is going to meet the needs of their clients in the market.
That’s why the popularity of Acumatica in the VAR 100 is such a big deal.
The journey of a thousand miles begins with one step.
– Lao Tzu
The two year journey that I began on February 5th, 2015 has turned out to be only a 4 month journey.
I have decided to stop posting a review of one of the out-of-the-box reports on Thursdays. Basically, I think that I have reviewed enough reports to become comfortable with the process and there is no need to continue. I will review more reports in the future on an “as needed” basis.
However, I’m still glad that I started the journey. Rather than try to determine where a road heads, sometimes it’s best to just start walking. Then you’ll know for sure if it’s heading in a direction that you want to go. If the journey doesn’t appear to be worthwhile, then you can always stop. And that’s what I have decided to do.
Summertime, family vacation, etc. has caused my posting on this blog to suffer. But I still plan to continue posting on Tuesdays. It just might take me a little while to get back into the swing of things.
This post is part of a two year series of posts related to the out of the box reports in Acumatica. For a full list, click here.
The CA Edit (CA612000) report in Acumatica shows you the transactions that have been entered through the Transactions (CA304000) screen and that are not On Hold and not Released.
Here are some screenshots from the report:
This post is part of a two year series of posts related to the out of the box reports in Acumatica. For a full list, click here.
The Reconciliation Statement (CA627000) report in Acumatica assists you went reconciling your bank accounts. You can see a list of the transactions that have not yet been reconciled in the period that you select.
Here are some screenshots from the report:
This post is part of a two year series of posts related to the out of the box reports in Acumatica. For a full list, click here.
The Cash Account Details (CA633500) report in Acumatica allows you to see the individual transactions in a cash account for a chosen date range. The Cash Account Summary (CA633000) report shows a summary of the transactions per day, but this report actually shows you each individual transaction.
Here are some screenshots from the report:
Last week we built a very simple dashboard in Excel by connecting Acumatica data to Excel using OData.
This week I have been super busy so I wasn’t able to put together a video post.
However, I did find a couple of helpful links that demonstrate the power of Microsoft Excel Dashboards in Acumatica ERP:
The Cash Account Summary (CA633000) report in Acumatica gives you an overview of your cash situation. You can see the daily balance of each of your cash accounts along with a summary of the receipts and disbursements.
Here are some screenshots from the report:
Easy to Use and Massive Community
There is a reason that Microsoft Excel is the world’s most popular BI reporting tool. It’s easy to use and you can find tons how-to information online.
If you aren’t sure how to do something in Excel, just type your question into Google and the chances are that you will find an answer.
Excel formulas are very powerful and there are myriads of ways that you can combine them together to create powerful calculations.
The GL Reversing Batches (GL690010) report in Acumatica allows you to enter a batch and then see all the reversing entries that have been made against that batch, including their individual debits and credits. This isn’t my favorite report, but I suppose it could come in handy when you aren’t sure if there have been any reversing entries made against a batch.
Here are some screenshots from the report:
Update February 5th, 2017: Alas, the TryAcumatica.com links don’t work anymore since that site has been taken down. So, I removed the links.
Then, at the very end, we checked a box to enable it for OData.
Checking that box allows us to connect to the data in our Generic Inquiry screen using other applications, including the world’s most popular business intelligence application: Microsoft Excel.
The Open GL Documents (GL656000) report in Acumatica allows you to view all the unposted GL documents in Acumatica. It’s easy to forget about an unposted transaction. This report helps you to track them down and clean them up.
Here are some screenshots from the report:
The GL Recurring Transactions Detailed (GL640500) report in Acumatica shows your recurring transactions that have been setup in the Recurring Transactions (GL203500) screen. It is very similar to the GL Recurring Transactions (GL641500) report except that it shows the individual debits and credits for each scheduled batch.
Here are some screenshots from the report:
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 augforums.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.
The GL Recurring Transactions (GL641500) report in Acumatica shows your recurring transactions that have been setup in the Recurring Transactions (GL203500) screen.
Here are some screenshots from the report:
Great news for Acumatica fans. There is a new website for Acumatica University and it’s open to everyone.
Joel Gress has a nice write-up about it (click here).
To access, just go to AcumaticaOpenUniversity.com.
I have to say that this move by Acumatica doesn’t surprise me at all. It is consistent with the “we have nothing to hide” company culture that I have been observing since April 2013.
Acumatica has an “open” philosophy. You can open any screen and see the code behind it without having to pay for source code. You can take the application and deploy it wherever you want: SaaS, Hosted, or On-Premise. And you can try out an actual installation of the latest version at TryAcumatica.com without having to put in any of your contact information.
This kind of culture is Engineering-led, not Marketing-led and it indicates to me that Acumatica is planning on staying in this for the long haul. A product-focused organzation takes longer to gain momentum, but does very well in the long run because the product begins to market itself.
Acumatica is a company of geeks who focus their attention on getting the core product perfect so there is a strong foundation to build on when implementing in a customer’s unique operating environment. Acumatica appears happy to focus on building an awesome platform and leave the vertical solutions to their growing network of ISV partners.
Things have definitely started off on the right foot with Acumatica
Now we just need to get an Acumatica User Group in place. The group should be independent of Acumatica in every way so we can ensure that this “open” culture continues even after Acumatica becomes the dominant player in mid-market ERP and the money starts rolling in.
Robert Lightner is working on creating such a group (click here) and I personally think that this is one of the strongest signs that Acumatica has a bright future ahead.
The GL Register Detailed (GL621000) report in Acumatica shows your all the batches in the system that have been Posted or Released, regardless of the module.
The format of the report is almost exactly the same as the GL Edit Detailed report, except the GL Edit Detailed report shows batches that have not been Posted, Released, or Voided whereas the GL Register Detailed report shows batches that have been Released and Posted.
Here are some screenshots from the report:
Would anyone out there be interested in a comprehensive Acumatica Report Designer Training Course?
I’m thinking about creating one…
The training course would be comprehensive, covering everything from beginner topics like “creating your first report” and “creating fields that use calculations” to advanced topics like “determining the proper DAC relationships” and “delivering embedded reporting with subreports and hyperlinks”.
The GL Register (GL620500) report in Acumatica shows your all the batches in the system that have been Posted or Released, regardless of the module.
This is the opposite of the reports that we looked at the past few weeks because it shows batches that HAVE been Posted or Released, not those that HAVE NOT been Posted or Released.
Here are some screenshots from the report:
The GL Edit Account Distribution (GL611000) report in Acumatica shows your GL batches that have not been Posted or Released.
It is similar to the GL Edit Account Distribution (GL611000) report that we looked at last week (click here), except that it shows the Batches underneath each Account instead of the Accounts underneath each Batch.
Here are some screenshots from the report:
The GL Edit Detailed (GL610500) report in Acumatica displays your GL batches that have not been Posted, Released, or Voided.
It is exactly the same as the GL Edit Summary (GL612001) report that we looked at last week (click here), except that it shows the individual debits and credits underneath each batch.
Here are some screenshots from the report:
The GL Edit Summary (GL612001) report in Acumatica displays your GL batches that have not been Posted, Released, or Voided.
You have the option to include batches that are on hold if you want.
Clicking on the hyperlink in the Batch Nbr. column will take you to the Journal Transactions (GL301000) screen for the batch that you clicked on.
Here are some screenshots from the report:
The GL Budget Edit (GL612001) report in Acumatica shows you the amounts that you have budgeted for each GL Account.
You have to pick a Ledger and a Financial Year that you want to view and then the report shows you the following:
- Amount – The total amount that has been budgeted
- Distributed Amount – The amount that has been distributed to financial periods (most commonly months)
- Released Amount – The amount that has been released through the Release Budgets (GL505510) screen so that it is approved
You can also click on the link in the Account column to be taken to the Budgets (GL302010) screen for that Account.
Here are some screenshots from the report:
The Transactions for Account (GL633500) report in Acumatica allows you to see all the individual Journal Transactions that posted to a specific General Ledger Account.
The General Ledger Account is required and you can only pick one so you can only look at one account at a time. If you want to look at more than one account at a time, you can use the Transactions for Period (GL633000) report which we looked at last week (click here).
The report is grouped by year so you can see a transaction total amount for each year.
You can also click on the link in the Batch Nbr. column to be taken to the Journal Transactions (GL301000) screen for that batch.
Here are some screenshots from the report:
The Transactions for Period (GL633000) report in Acumatica allows you to look at each General Ledger account and see all the individual Journal Transactions that posted to a financial period range that you get to specify.
You get to see all the journal entry batches that were posted during the period range so you can easily explore the detailed general ledger activity. Accountants like this kind of stuff.
You can also click on the batch number to be taken to the Journal Transactions (GL301000) screen for that batch. Note: the screenshot below is only showing batches that originated in the GL module, but the report will show you any batch, regardless of the originating module.
Here are some screenshots from the report:
The Trial Balance Detailed (GL632500) report in Acumatica is very similar to the Trial Balance Summary report that we looked at last week (click here), but with one additional column added.
The main difference is that the Trial Balance Detailed report shows a separate row for each Account/Subaccount combination whereas the Trial Balance Summary report only shows a separate row for each Account. Bottom line, the Trial Balance Detailed report is more, um, DETAILED.
Even the Account hyperlink field functions the same as it does in the Trial Balance Summary report by taking you directly to the Account Details (GL404000) generic inquiry screen. The only difference is that it pre-populates both the Account and Subaccount fields instead of just the Account field. When you leave the Subaccount field blank in the destination generic inquiry screen, it shows all subaccounts for the Account selected.
Here are some screenshots from the report:
The Trial Balance Summary (GL632000) report in Acumatica lets you view your Balance Sheet for a financial period of your choice. A Balance Sheet, or Trial Balance, is a very common report in an accounting system. You see the balance at the beginning of the period, any debits and credits that happened during the period, and finally the ending balance for the period.
When you first run the report, you need to enter a Ledger and a Financial Period, but you can leave the Branch empty which will give you a Consolidated Balance Sheet across all of your branches. This is pretty cool because most ERP systems require special software if you want to do consolidated reporting. And although you can’t do consolidated reporting across multiple Companies in Acumatica Report Designer (at least not that I know of), Branches are very powerful and can often replace the need for a separate Company in Acumatica.
You’ll notice in the second screenshot below that the Account field is a hyperlink. Clicking an account hyperlink will take you to the Account Details (GL404000) generic inquiry screen with the Account field pre-populated.
Here are some screenshots from the report:
UPDATE June 25th, 2015 – I have decided to stop this two year journey. For more on why, click here.
I have decided to embark on what will likely be a two year journey to explore the out of the box reports in Acumatica.
Each week I will post a review of one of the existing reports in Acumatica. This will be a long and sometimes tedious process, but I expect to learn a lot from it.
If you have had to manage an ERP system for a company, then you know how difficult it can be to keep track of your customized reports. While you are making changes to a report, you always think that you’ll remember what changes were being made and why. But as time rolls on, month after month, and year after year, the reports in your ERP system can become chaotic.
When was that report changed last? Who requested the change? What changes were made? Was this report even customized or is it the stock report?
Or maybe you need to roll the report back to a prior version, some version in between the original out of the box version and the most current version. Did you save an archived copy of the report every time you made a change?
One of my favorite features in the Acumatica Report Designer program is the ability to save a report as a new version.
When saving a report, all you have to do is click the Save as new version box, then put something in the Version description field:
Have you been wondering if anything has changed with Acumatica Report Designer in Acumatica 5.0?
Well, I have. So, I decided to install Report Designer 5.0 alongside Report Designer 4.2 to see if I could spot any differences. Then I opened them both next to each other:
If your user has the ReportDesigner role assigned to it, things can get a little confusing.
First, check to see if you have the ReportDesigner role by going to Configuration –> User Security –> Manage –> Users (SM201010) and checking to see if the ReportDesigner role is checked like this:
Last week we covered how to login to Acumatica Report Designer (click here) so you can retrieve a report directly from the Acumatica ERP application.
This week we’ll cover how to build a simple report from scratch that will display the list of our customers and run it from the Acumatica application.
Step 1: Open the Acumatica Report Designer application
After it opens, you should see a blank page like this.
When I modify a report using the Acumatica Report Designer, I personally like to open the report directly from the server. Then I don’t have to download a copy and then upload my new version.
However, when I first tried to use the File -> Open From Server… option, which looks like this:
There is a big movement in the ERP market these days involving Cloud ERP vs On-Premise ERP and every analyst out there is predicting big things for Cloud ERP. It seems like there are new Cloud ERP vendors popping up every day. Even the traditional ERP vendors are building Cloud products. Of course, the traditional vendors are pretending that they are moving their existing product into the Cloud, but the reality is that they have to build an entirely new product because the underlying technology framework is so different.
Most of the analysts out there sound like Buzz Lightyear: “to the Cloud and beyond.” There seems to be infinite expectations and grand projections about where things will be in the next 5-10 years.
But where are we today?
Things are beginning to take shape with my new Acumatica Reporting business. I have begun work on a new website and already picked up a customer in need of some Acumatica reports. There is a lot of opportunity here and I’m very excited.
The new website and blog will be located at augforums.com
Up until today, this was my approach towards Acumatica:
Last week was the 1.5 year anniversary for this blog and my personal Acumatica learning journey (click here). That milestone, coupled with a rejuvenating two week vacation, caused me to consider where I would like to go next.
Up until now I have considered Acumatica to be a hobby. But, while on vacation, I decided to take this thing to the next level: from “Acumatica Hobby” to “Acumatica Business”…
I had a great time on vacation in Southern California the past two weeks.
We visited my side of the family in San Diego, then we stayed in San Diego for a few more days for my brother-in-law’s wedding. The wedding was in Mission Beach so we rented a beach house right on the boardwalk and stayed with my wife’s siblings and their kids. We had 8 adults and 8 kids in the house and it was a blast. Here we are eating dinner in the living room with the extended family:
No post this week. We’re back home in Southern California for a wedding and to visit family.
The title of this post is actually a joke because Acumatica doesn’t utilize Windows Terminal Server.
But I was reminded of that fact recently when someone at work was getting a windows error upon trying to login to our ERP software (Sage 500). The problem was that we use the remote application feature in windows terminal server so that when a person closes Sage 500, it doesn’t actually close completely. The windows session continues to run in the background.
Currently, you can’t build Microsoft Excel reports in Acumatica. However, if Acumatica implements OData in the future, this will become a possibility.
In the meantime, I created a simple Excel report as an example of the powerful things that you can do with Excel. This report was built for Sage 500 and is being sold through The Report Store (click here), but it would look exactly the same for Acumatica if the OData option is included at some point.
So, maybe this is a glimpse into future Acumatica? I hope so
Here is a quick 5 minute video showing the report:
This week I started the Distribution courses in Acumatica University and I’m having a great time! It’s nice to be out of the technical courses and back into learning more about the application functionality.
The first course up in Distribution is called D100 Inventory Management: Basic and it walks you through the Inventory module.
I’m still making my way through, but I couldn’t help notice that the LIFO inventory valuation method seems to be missing in the Stock Items (IN202500) screen. What’s going on?
I added a new button to the blog called Ask a Question. My hope is to receive questions highlighting real-world problems that people are trying to solve with Acumatica. Nothing helps me learn better than real-world examples. So fire away! I can’t promise an answer, but I can promise a response, maybe even in the form of a blog post.
Questions help take me down the byways on my Acumatica road trip (click here).
Last night (technically early this morning) I finished the S400 Introduction to Acumatica Technology course. This course mainly focused on two things: the Customization Engine and Web Services.
It feels a lot like climbing into the cockpit of an airplane. There are lots of options to choose from.
Last week (in part 1 of 2), I covered why I think BizNet BizInsight and Solver BI360 are important for Acumatica (click here).
I currently work for a company that uses Sage 500 ERP and we were looking for an Excel-based reporting solution (BizNet BizInsight and Solver BI360 have the same functionality for Acumatica that they have for Sage 500 ERP). A colleague and I sat through demonstrations of both products and asked lots of questions. Based on that process, we chose Solver BI360 over BizNet BizInsight for our company. Here are 5 reasons why.
Note: I would love to hear comments from others who have compared these two products. This post is in no way intended to be a comprehensive comparison, just my two cents.
In this post (part 1 of 2), I’d like to cover why I think BizNet BizInsight and Solver BI360 are important for Acumatica. Next week, I’d like to give my opinion on which one I personally think is better and 5 reasons why.
First, some background
Last week was a big week for Acumatica news because of the annual Partner Summit.
Here is a quick recap of 15 recent Acumatica news stories that I am aware of:
Recently I completed three courses in Acumatica University: S125 Document Management, S200 Integration Services, and S300 System Management.
Only one more course to go (S400 Introduction to Acumatica Technology) before I complete the Application Engineer badge. Although I don’t like to focus too much on the system maintenance stuff since I personally find it boring, it’s still good to go through the courses so I can at least be familiar with things. Once I finish the Application Engineer badge, I’ll be able to move onto the Distribution courses which I think will be a lot more interesting.
Here are some things that stood out to me in these three courses (note: Some of these features have been improved in Acumatica 5.0 which was announced earlier this week at the Acumatica Partner Summit). [Read more…]
Acumatica handed out some awards last night (August 25th, 2014) at their partner summit and they created a special category for this blog. See below for the full announcement.
So far, this blog has landed me a bottle of wine (click here) and now an award. This whole Acumatica thing is purely a hobby at this point for me, but it’s still very nice to get recognition.
I’m looking forward to see what news comes out of the annual Acumatica Partner Summit which takes place this week in Denver, Colorado.
Continuing the “Real-World” theme from last week (click here), I wanted to cover another real-world Acumatica situation. This question came to me through the Contact page here on the blog (click here). That’s right, this came from a person who is actually using Acumatica at their company! Here’s the question and a solution. I say a solution because I’m sure there is more than one way to tackle this.
Note: I’ll keep this shorter than usual because I’m writing this in an empty living room, tired from a long evening of packing. 90% of our stuff is in the moving van that is currently sitting in our driveway. We’ll load up the remaining 10% in the morning and head to our new house in Columbus (click here).
How do I prevent users from changing the status of a customer?
Basically what we want to do is make the following field read-only for certain users.
Recently at my job I was confronted with a challenge to create a new business process for capturing shipping information and integrate that process into our ERP system. I’d like to describe the solution that I came up with and how I think it would have been easier to do with Acumatica.
First, Some Background
I work for a company that makes machines that dig tunnels. We are an old-school company with a proud owner who takes care of his employees and employees who are proud to work here. And we make a product that everyone here is proud of too.
Basically, our machines are used to dig tunnels all around the world. Here are a couple of pictures as examples of our machines. The machine in the first picture was used to dig the Chunnel from England to France. The machine in the second picture was used to dig a hydro tunnel near Niagara Falls.
Acumatica has taken a very aggressive marketing approach over the past couple of years with some crazy stunts, but I guess that’s what you do when you are a small company looking for recognition. Last week at Sage Summit, the annual Sage conference in the US, Acumatica once again took an “in your face” approach by offering free coffee at the event.
This time, however, Sage fought back. Here is the news as told by Bob Scott:
Last week I wrote in general about the Acumatica email engine (click here). This week I’d like to go further and cover something more specific related to email in Acumatica. Acumatica can basically act as an email client, just like Microsoft Outlook, Gmail, Yahoo, or other popular clients. It just needs to connect to an email server. In order to test out the Acumatica email functionality, I think it’s easy to use a Gmail account. Why? Because if you don’t have one you can easily create an account on Gmail.com without having to call your IT department.
Once you have a Gmail account, setting it up in Acumatica is actually very easy. How easy?
Step 1: Navigate to Configuration –> Email –> System Email Accounts (SM204002)
Acumatica isn’t just a flashy new ERP product with a nice user interface and fancy dashboards. There are a lot of substantial features under the hood and the email engine is one of those features. Note: this post is an expansion of the last point from a previous post (click here).
First, Some Thoughts On Email
I was planning to do a post this week on the email features in Acumatica, but the announcement by Lebron James last Friday was such HUGE news here in Cleveland that I thought this post would be better. I’ll pickup the email features post next week. [Read more…]
For the past three months, I have been posting on Mondays and Tuesdays with links to other Acumatica-related articles. I was following the pattern of Mark Polino who is one of the prominent bloggers in the Microsoft Dynamics GP community. Mark regularly posts links to other blog articles on his blog (click here). [Read more…]
For those who don’t already know, you can hide columns in Acumatica inquiry screens. This feature is a perfect example of why there is always more to learn with Acumatica. I’ve been using Acumatica for over a year now, but didn’t notice the icon until recently.
Personally, I don’t like what Larry Flynt stands for and I don’t think that the empire he built is anything to be proud of. But, he does have a recognizable name and I thought it was interesting that his company appears to be using Acumatica. At least that’s what I gather from the job that was posted today on Indeed.com.
The first part of the job description makes it clear that it’s a position at Flynt Management Group:
Acumatica allows you to attach input validation preferences to fields. This is very useful when you can’t use a drop-down list field for your scenario so you need a text field, but you want to put some amount of control on the text field. Good data entry is very important in an ERP system. The idea is that you can use the data in your ERP system to make critical business decisions. However, if the data isn’t very clean, you might waste a lot of time cleaning the data before it can be usable. Input validation helps to keep your data clean as it is getting entered into the system.
This kind of thing is probably best demonstrated by example. [Read more…]
Bob Scott recently released his 2014 Top 100 VARs. This is an annual listing of the top mid-market ERP resellers (based on annual revenue) which gives you a very good idea of which mid-market ERP products are the most popular.
Regarding the top 100 VARs, the cloud products aren’t yet dominating the list, but they are definitely gaining traction: [Read more…]
Sergey reminds us that Acumatica supports the latest version of Microsoft SQL Server (2014). This is the kind of stuff that you get when you go with a modern ERP platform like Acumatica. They support other modern tools like the latest version of Microsoft SQL Server. For those who don’t know what Microsoft SQL Server is, it’s the engine that powers most of the mid-market ERP systems. SAP and Oracle (the top ERP systems) have their own database engines, but most everyone else uses Microsoft SQL Server to store their data.
The Field-Level Audit feature allows you to track changes to any field on any screen (I haven’t actually tried this on all fields on all screens, but this seems to be the case). This can help your company comply with Sarbanes-Oxley requirements, or just give you better visibility into change log history on some of your data.
Where I think this feature is especially useful is on screens that are used to maintain your master level records. Master level records are things like Customers, Vendors, Purchase Orders, Items, etc. Many times it’s important to know what information changed on a record and when. This is where Field-Level Auditing can help.
How do I use it?
A few months ago, Christian Lindberg posted about Acumatica and the Millennial Generation (formerly known as Generation Y).
It reminded me of the Esurance commercials, “born on the web, raised by technology” (click here). I like his point that “Acumatica’s technology was built from scratch in the millennial years.” Acumatica is not an older product getting a Facelift or a Botox injection.
Acumatica was born less than 10 years ago: it’s brand new. Yes, that means it isn’t as mature as the older ERP products out there, but it does have the strength and vigor of youth, born in an age where computer applications are built for web browsers, not for operating systems.
I barely squeaked into the millennial generation (I was born in 1981). So, I’m old enough to have had early childhood years full of playing outside (year-round since I grew up in San Diego). We rode bikes and roller blades, played roller hockey and football in the street, and played baseball in an old neighborhood dirt lot using a ball made of old socks and duct tape. We went camping, fishing, played in the sand at the beach during the Summer, played in the snow in the mountains during the Winter, and generally went around without wearing any socks or shoes. [Read more…]
Sergey has a new post up about how to reuse a project lookup field. I don’t really understand all the code, but I do understand that he is adding a project lookup field to a screen that didn’t have a project lookup field. And, in order to do this, he is taking the code from an existing screen. Also, there is something about this being possible in the latest version of Acumatica (version 4.2). Again, I don’t completely understand what is going on here, but it’s encouraging to see that you can add a project lookup field to a screen that didn’t have one before. I would LOVE to be able to do this in Sage 500 ERP, but, alas, it’s not possible unless we were to pay tens of thousands of dollars to buy the source code. It’s definitely much simpler in Acumatica.
Recently I passed the S100 Installation and Site Management and S120 Configuration Settings courses.
Personally, I’m not a big fan of software installation because I find it boring, but it’s an important part of implementing Acumatica so it needs to be done correctly. It’s also good to be aware of the different deployment options available since Acumatica allows you to choose.
I found the configuration course to be much more interesting because it introduced some new features that I’d like to learn more about in the future. Here is a quick recap of my takeaways from the two courses.
I’m pretty much done going through the archives on Zaletskyy’s blog (click here). Going forward though, I’d like to continue to highlight interesting posts as he makes them available. Here is a recent one regarding Acumatica and Microsoft Visual Studio. Personally, I prefer to focus on the power user features rather than the super geeky technical stuff, but there is definitely the need to “open the hood” if you need to do some serious customization and that’s what Microsoft Visual Studio offers you. The nice thing about Microsoft Visual Studio is that it’s a very popular development tool which a lot of developers are already familiar with. This is a further illustration of the Microsoft Stack point that I’ve mentioned before (click here).
Christian Lindberg has a new post up about the Acumatica Cloud XRP platform. I’m not really sure if this is anything new or if it is just a new marketing way of describing the Acumatica platform that has always existed. This press release seems to indicate that it is indeed something new, but I’m still not sure. It will be interesting to hear more about this as time goes on.
This post is just a random thought about Acumatica and a historical event that I remember learning about as a kid. I remember learning about the “Golden Spike” in my American history class when I was in high school. The “Golden Spike” is the ceremonial final spike which was driven to join the rails of the First Transcontinental Railroad across the United States connecting the Central Pacific and Union Pacific railroads on May 10, 1869 in the Utah Territory (click here for the Wikipedia article).
There are a few things about this historical event that stand out to me: [Read more…]
This morning I got stuck in the waiting room at the Doctor’s office so I pulled out my iPhone and went over to Acumatica University to get in a little Acumatica learning. Acumatica University actually works very well on an iPhone. You can see all the courses, all the .pdfs/videos for a specific course, and even take an examination. The only downside is that it’s hard to see the details in the videos since the screen is so small.
It took 30 minutes after I saw the doctor before someone came in to discharge me. Not sure why it took so long (maybe just the inevitable lethargy that Obamacare is injecting into the American medical system), but I can’t complain too much because I was able to keep my Acumatica learning going.
Then I realized, “hey, not only could I be learning Acumatica, I could also be using Acumatica!” Since Acumatica is 100% web-based and works wonderfully on mobile devices, I could just as easily have been finishing my timesheet for the week, filling out an expense report from a recent business trip (and uploading the receipt images since I always scan them immediately with my TurboScan iPhone app instead of keeping the paper copy), or reviewing a customer quote that a salesperson put together this past week. Full access to my company’s front and back office information, all on my phone. That is, only if my company used Acumatica and if I had been granted access to that information in Acumatica.
Of course, this kind of mobile access can be a bad thing. It’s probably not a good idea to learn or use Acumatica during your kids’ baseball games, while crawling along the 405 freeway in Los Angeles at rush hour, or when you’re at a restaurant on a first date and you’ve run out of things to talk about.
There are some situations though where you are stuck somewhere and mobile access can be a good thing:
- You’re on Jury Duty, stuck in a room all day with nothing to do.
- Your Flight is Delayed and you just heard the voice over the speaker announce that it will be delayed at least another 2 hours (click here).
- You took your car in for an Oil Change, but decided to take advantage of the discount that they offered on all the preventative maintenance stuff that was offered (you’ll be waiting for a while).
- You are at the Doctor’s Office for a checkup!
This week I passed S130 Reports, Dashboards, and Inquiries. I first started this course back in July 2013 (click here). Now, almost a year later, I finally finished, thanks to a renewed interest in the Acumatica reporting vision (click here).
Learning a new ERP product: An endurance race
Continuing my stroll through Zaletskyy’s archives (click here), here is another cool post about Active Directory integration in Acumatica. Active Directory is the Microsoft feature that allows an application to know who you are so you don’t have to setup a separate login with yet another password that you have to remember. If you setup Active Directory integration in Acumatica, all you have to do is login to your computer in the morning and you’re good to go. When you go to use Acumatica, you don’t have to login because it already knows who you are!
Ok, I give up. Exactly one month ago (click here) I attempted to abandon the Acumatica Data Access Classes. Now, at the time I wasn’t sure if it would work out, but I thought I’d at least try it temporarily. One month later and I’ve given up. I feel like I need to go to a chalkboard and write “Don’t abandon the Acumatica Data Access Classes” over and over again.
I recently discovered Yuriy Zaletskyy’s blog (click here). He is a developer and writes about Acumatica from a technical perspective. I took some time and skimmed through all of his posts. For the next few weeks I’m going to highlight the ones that I found interesting.
For this week, here is a post about how to create a brand new screen in Acumatica. This is over my head right now, but I hope to come back to this post in the future and get comfortable with it so I can create my own screens in Acumatica when the situation calls for it. Anyways, check out the post:
Sergey has a great post up about Acumatica branch security. Branches are a part of the multi-company setup configuration strategy in Acumatica (click here). The nice thing about branches is that they aren’t just accounting buckets because they also carry security along with them. So you can have multiple locations or business units and you can keep your users from seeing things outside of their respective location or business unit. It seems to me that situations which used to required you to setup multiple companies in older ERP applications only require you to setup multiple branches in Acumatica which allows you to keep things nicely organized in one company.
Sergey also touches on Multitenancy. I only learned recently that the SaaS version of Acumatica supports Multitenancy (click here). If you’re like I was when I first heard this, you’re probably saying, “what’s Multitenancy?” I think it’s a pretty new word because my spell checker doesn’t even recognize it (maybe I need to add a dash?). If you want to know more, this Wikipedia article on Multitenancy is helpful to get you up to speed (click here). As I see it, you have to be pretty confident about your security setup if you’re going to offer customers Multitenancy. Acumatica must be confident!
Anyways, click below for Sergey’s post:
After taking a detour last week and exploring how to add new fields to existing screens in Acumatica (click here), I decided to return to the task of learning to create Excel reports that point directly to the Acumatica database. I began with this idea three weeks ago (click here) and continued two weeks ago by taking some baby steps to create a very simple Excel report (click here). This week I decided to pickup with where I left off a couple weeks ago and attempt to continue reproducing the Vendor Summary (AP401000) screen in an Excel report.
Continuing With Baby Steps
I ended the post a couple of weeks ago (click here) with the following report in Excel:
Jean Gea has a new post up on the Acumatica blog with a nice simple overview of how multi-company works in Acumatica. I blogged about this last year after taking the F300 Inter-Company Accounting (V3.0) course (cilck here). It looks like what they used to call companies, they are now calling entities. Acumatica is definitely in a good position to appeal to companies with multiple distinct business units because of how easy it is to deploy web-based technology and because of the strong multi-language support that Acumatica offers.
Sergey gives us a tip on how to overcome the basic authentication error that you get when you try to refresh an exported Excel file over a connection that is not using SSL. This is a common occurrence when you are giving a demonstration of Acumatica because there is a high likelihood that you won’t be using a secure server for the demo.
Sergey’s post interests me because it’s related to the Excel Export feature in Acumatica. I have also been interested in this feature recently and have been trying to find a better way (click here) to pipe Acumatica data into Excel. While Sergey’s post doesn’t solve my current problem, it does comfort me to know that others are interested in this feature too.
Acumatica has a new blog post up. I really like this post because I didn’t know that such a matrix ranking even existed for mid-market ERP products. This is good stuff! I downloaded the entire 13 page report and read it very carefully. The report includes a write-up on each vendor included in the matrix.
A few of my random thoughts:
Update (October 4th, 2016): This post was written on Acumatica 4.2, but things have changed since Acumatica 4.2. To see this done in the latest version of Acumatica, checkout this post by Mark Franks.
Michael Coman provided another great Acumatica learning experience for me. The last one was related to creating a Project Quote (click here). This time he responded to a post on this blog and asked a question about adding new fields to existing screens in Acumatica (click here).
At first I didn’t want to get distracted from what I’m doing with reporting in Microsoft Excel directly on the Acumatica database, but eventually I realized that a real-world example is too good to pass up.
It’s just a lot more fun when you are learning something that you know will actually be useful to someone. So, I put on my PAL hat and went to work learning how to do this. The result is the video at the bottom of this post.
Sergey has a new post up about how to access your Acumatica database from outside your network. This is interesting to me because I’m currently experimenting with making Excel reports that point directly to the Acumatica database (click here). Sergey’s trick would be needed if these kinds of reports were to be implemented in a production environment.
Richard Duffy put together a nice video about how to publish your implementation of Acumatica as a Google Chrome App. This isn’t anything fancy from a technology standpoint, but I think that it provides a nice user experience and has the “cool factor” which is very important.
I personally am a big fan of using Google Chromebooks (click here) for internal training. It’s very easy to bring a stack of ten Google Chromebooks into a conference room, setup a projector for sharing your screen, and conduct a simple one hour training session. It makes it easy to have training sessions on a regular (maybe weekly) basis. If you had to carry in ten computers, ten computer monitors, ten keyboards, and ten computer mice then you would be much less likely to conduct regular training sessions. The other advantage is the cost. I just checked Amazon.com and they are selling Google Chromebooks for $250 (click here) which is very reasonable.
Google Chromebooks make it easy for you to conduct simple regular training sessions in your organization and regular training sessions are very important to ensuring a successful Acumatica implementation.
Here is the video from Richard:
I discovered yet another Acumatica blog out there. This is great for the Acumatica community.
Zaletskyy has a great post about what the screen IDs mean in Acumatica. I did a post a while ago about how great screen IDs are (click here), but I didn’t realize that there was a method to the numbering.
So it’s been a week since I temporarily abandoned the Acumatica Data Access Classes (click here to see what I mean). This is just a temporary thing and I’m sure that there is a better way to learn this, but I decided to take the blunt approach by hunting around the Acumatica database to see if I can get comfortable with how things are related. That’s right, no Acumatica University (click here), no help pages, no glossy manual, just me out in the cold alone with the Acumatica database.
Acumatica has a blog post up about what differentiates Acumatica from the other cloud ERP options out there:
I still have a lot of hope for learning how the Data Access Classes in Acumatica can contribute to better reporting. As far as I can tell, I think the Data Access Classes function like the Acumatica Reporting??? side of the picture below. However, I think that I’m going to revert to my comfort zone for a little while to see how well I can create reports directly against the Acumatica database like in the Traditional Reporting side of the picture below. In more traditional ERP systems, your only option is to report directly on the database so this is what I’m used to. [Read more…]
I haven’t gone through the P200 Project Management Configuration course yet. I’m just poking around the screens in the Projects module.
What, No Phases?
In the Project Accounting module in Sage 500 ERP, I’m used to having three levels: Project, Phase, and Task. You have to charge costs to a Project and to a Phase, but the Task is optional. However, every Task must belong to a Phase. The Phase is kind of like the parent to the Task.
However, in Acumatica I can only see Tasks, no Phases. So, I want my Phases!
It has been almost a year since I started this blog and I wanted to revisit what I mean and what I don’t mean by the PAL acronym (Perpetual Acumatica Learner).
My first post on the topic of PAL (I called it APAL at the time) was mainly to describe the difference between a learner and an expert.
However, recently I was feeling pressure from the word “perpetual” because I haven’t been spending enough time to learn Acumatica. I was beginning to think that I shouldn’t call myself a Perpetual Acumatica Learner anymore if I wasn’t spending every waking hour on Acumatica learning.
But, it’s just not possible to be learning Acumatica all the time. My wife and I have two kids, a mortgage, and a car payment. I also have a full-time job during the day. So, it’s just not possible to be learning Acumatica all the time.
What is possible is to always approach Acumatica as a learner. That I can do.
So, I am comfortable to call myself a Perpetual Acumatica Learner. When you are a learner, there are no such things as stupid questions or dumb ideas. Every time I sit down at my computer to spend time with Acumatica, my goal is simply to come away having learned something new.
It doesn’t matter to me where I am in the learning process and it doesn’t matter how fast I am learning. Others may pickup things faster than me and require less time to learn a new thing like Acumatica. But, when I spend time to learn Acumatica, I am simply content to have learned something new.
I am determined to take the approach of a tortoise.
So what about you?
If you are also learning Acumatica, I hope that you will also take the way of a Perpetual Acumatica Learner.
Even if you somehow reach a point where you know everything there is to know about Acumatica, there will always be different ways to apply that knowledge because every company is different. That is what makes the ERP world so interesting.
There is no such thing as a standard cookie-cutter implementation. Every company has unique requirements and many times those unique requirements are what give them a competitive advantage in the marketplace.
You may find yourself in a company that is implementing Acumatica and you are afraid of all the changes that are taking place. Don’t be afraid, just do your best to make progress, one step at a time.
You can ignore all the fast-talkers and those who try to make it seem like they know everything even when they don’t.
Just keep your head up and know that you are not the only one on this Acumatica learning journey. There are others out there and there will be more and more as Acumatica gains popularity.
I still have a lot to learn about Acumatica and I hope that will never change. No matter how much I learn, I will always realize that there is more to learn.
So, it’s time to settle into a nice long marathon pace and continue on as a Perpetual Acumatica Learner. Who’s with me?
Recently I was working on a Purchases Clearing reconciliation as a favor to the Controller at my company. It wasn’t a very fun project, but we needed to make sure that the Purchases Clearing account agreed to the General Ledger so our auditors would be happy. I was asked by the Controller to help out and so I went to work.
In Sage 500 ERP, the individual debit and credit transactions to the General Ledger maintain a link back to the source transaction that they came from. For example, if you post an Accounts Payable Invoice, the individual debit and credit transactions that get created in the General Ledger maintain a link back to the Accounts Payable Invoice in the Accounts Payable module. This allows you to go into a screen that lists General Ledger transactions, select an Accounts Payable Invoice debit/credit, and click a button to have Sage 500 ERP automatically open the related invoice screen. It’s a nice feature and I thought that I would be able to leverage it to help me reconcile the Purchases Clearing account to the General Leger. HOWEVER, I quickly discovered that the link back the Accounts Payable Invoice is only to the invoice itself, not to the specific line on the invoice. So, if there were five lines on the invoice which generated five General Ledger transactions, each of the five General Ledger transactions would have the same reference value, that of the Accounts Payable Invoice (but not the specific line).
Eventually, I had to group my Purchases Clearing transactions by day and compare them to the General Ledger (also grouped by day) in order to do the reconciliation. It would have been nice if I had the link back to specific lines because I could have reconciled things by Purchase Order Line level instead of by day. But, alas, the world of ERP isn’t perfect and many times you are forced to choose the best option available, even if it isn’t the most elegant.
Now, this is a post on an Acumatica blog so I should probably say something about Acumatica. This little reconciliation project that I did got me wondering how I would have approached this in Acumatica. So, I took a look at the General Ledger table that holds the debits and credits. I was very pleased to find TWO relevant columns. Not only does Acumatica store the header information (in the GLTran table). It also stores the line information (in the GLTran table). So, in my example above, it would not only have stored the Accounts Payable Invoice, but also the specific LINE in the Accounts Payable Invoice that triggered the individual debit/credit to be posted to the General Ledger. I haven’t looked at this specifically for Purchase Order receipts, but it definitely looks promising.
Sergei has a nice post up on geeky hardware recommendations. This is especially useful if you are running Acumatica on-premise.
Here is a good article pointing out that the cloud hype hasn’t reached the ERP world yet and maybe never will. Acumatica is positioned for this by allowing their customers to buy and host Acumatica on-premise. NetSuite, on the other hand, only allows the SaaS option.
This reminds me of the Western Digital commercial that I posted a link to a few months ago:
Michael Coman started a discussion on how to create a Project Quote form in Acumatica over on the Acumatica ERP Software User Group. I came up with something very simple which hopefully will be a good start. This was my first experience actually doing a real report/form with the Acumatica Report Designer. It definitely has a long way to go before becoming a user-friendly report designer like Crystal or SSRS. But, it is pretty flexible and, once you get the hang of it, it’s not too bad.
First I opened Acumatica Report Designer on my Windows Start Menu.
Sergey has a nice post up about creating separate numbering sequences for data that is being imported from other systems like Oracle, etc.
Jon Roskill, the new CEO at Acumatica, posted something on the Acumatica website:
There are two things that he said which stood out to me that I completely agree with and have noticed about Acumatica since I have been following it for about a year now: [Read more…]
I thought it might be interesting to periodically check how the Acumatica groups on LinkedIn compare to some other ERP groups on LinkedIn. I looked up 18 different ERP groups on LinkedIn (including Acumatica) to compare their number of members and number of discussions posted this month in order to gauge how large and how active they are. Now, this is not a very accurate method to try and determine how large the user base is for these ERP products and how active they are, but I think it’s still an interesting thing to track. See graphics below.
Some things to consider:
- A large group membership doesn’t necessarily mean that there are a lot of people still using the product as it is likely that many people won’t bother to leave a group after joining it, even if they haven’t used the product for a long time.
- Acumatica is almost on the bottom, but at least it’s ahead of rival Intacct!
- The Acumatica ERP Software User Group had by far the highest number of discussions this month per member. I’ll be putting a link to this post on this group which will make this number even higher.
- It’s probably a little unfair to include Dynamics AX in this group because it is really a Tier 1 product like SAP and Oracle, but I included it because the other Dynamics ERP products belong in this group.
- NetSuite has a very active group and a large number of members. Let’s see if Acumatica can gain ground this year.
This week I put together a document called Case For Ending Sage 500 Maintenance (click the link to see the PDF document). My company currently pays a very sizable sum of money every year to Sage for the privilege of upgrading to the latest version of Sage 500 ERP. In recent years, however, it has become apparent that this money would be better spent on other things.
In contrast, each new version of Acumatica is providing substantial new features. Even the upcoming release of the lowly version 4.11 is full of many new features (the number of the version suggests that version 4.11 is supposed to be only slightly better than the current version 4.1 and significantly less “cool” than version 5.0 which is due this Summer).
I would gladly recommend that my company pay maintenance on Sage 500 ERP if it continued to deliver substantial new features like Acumatica.
It looks like Acumatica will be adding a customer portal:
This is good news. Customers will now have access to Acumatica University which has become a great repository of Acumatica learning material.
Also, this is another logical step in the direction of Acumatica becoming an ERP provider that is here to stay.
This week I was reviewing the security options in Acumatica and I was pleased to discover that Acumatica offers field-level security on screens. [Read more…]
I’ve written on this blog before about the advantages of using a web-based ERP application. In this post, I’d like to focus on the fact that the client in the old school client/server architecture basically gets eliminated when you move to a web-based product like Acumatica. This allows Acumatica to easily track which screens a user is accessing. If the screens are all stored on the user’s local Windows machine, it’s not so easy for the central database to know who is launching which screens and when. But when you move to a web-based application like Acumatica, there is no local client so your web browser needs to talk to the central server every time it wants to access a screen. It’s more like a server only rather than a client/server architecture.
So, why does this matter?
First, a little background. This month, at my current company, we have had 85 different people login to our Sage 500 ERP application (I had to write a customized routine to even be able to figure out that 85 number since it’s not a feature of Sage 500 ERP). So, with 85 people logging in, it can be difficult to know who is using what screens and when. This is especially relevant when it comes to reports. We have created many different custom reports over the past few years, but we don’t know for sure who uses which reports and how often they are used. I would LOVE to be able to analyze this. But, alas, I don’t have a reliable way to track the usage on our screens, including all of our custom reports. It would be great if I could have this kind of visibility.
Enter Acumatica and the Access History (SM201045) screen. If you filter on Operation: Access Screen within this screen, you will be able to see a full list of who accessed which screens and when. Has that report that you created become a sensation and it’s now being used by everyone in the company? Or maybe it’s a complete flop and the person who asked for it isn’t even using it. Acumatica tracks this for you. Yet another advantage of using a web-based ERP application.
In the video below, I’d like to walk through a quick example of analyzing the screen usage in the GL module. In the video I will export the Access History (SM201045) screen data for the GL module into Excel and do some quick analysis. Now, my Acumatica data is pretty boring. I pretty much always login as admin and I haven’t used that many screens yet, but I hope the video at least gives you some ideas. For the sake of the video, I did create one other user and launch a screen just to make the data look a little more exciting (wow, two users!). If you have 85 different people using your ERP application like my company does, this kind of analysis can be very useful.
One other note, in the video I use Power Pivot for Excel. I’m only using it so I can count the number of distinct users because I think it’s useful. You can do a lot of meaningful analysis using Excel without Power Pivot.
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:
I noticed on the top of Acumatica screens, under the Help menu, there is an option called Get Link. I tried it out and got very excited about the potential. Basically, you can open a record on a screen and then get a hyperlink that will open the screen for you and take you to the exact record that you were looking at.
This got me to thinking about the lowly hyperlink. There are lots of great advantages to using web-based software like Acumatica. When we think of web-based software, things like “always on”, “accessibility”, “bring your own device”, “cloud”, “redundancy”, “scalability”, “SaaS”, etc. come to mind. Not many people think of the lowly hyperlink, but it’s a huge part of why we like web-based software so much. Think about it, when you click on a hyperlink, it takes you to where you want to go, it happens fast, and it’s very reliable. It works on any computer, on any smart phone, on any tablet, and in any application regardless of if you are in Microsoft Office, Apple iTunes, Adobe Acrobat, etc. There is nothing to install or configure, it just plain works. I can put a hyperlink in a Microsoft Word document, a PowerPoint presentation, or a slick looking Marketing PDF file and have the confidence that it is going to work. The only thing that prevents a hyperlink from working is if the person trying to use it doesn’t have an internet connection, but that is increasingly rare these days.
Also, hyperlinks can be very intelligent, containing lots of information. When you click on an advertisement on a webpage, for example, you are taken to the advertisers website. However, the hyperlink that sent you there had the webpage that you came from embedded in it. This allows the advertiser to know which advertising affiliates are giving them the most leads.
There is even the new QR Code which many people are using like a physical hyperlink. They put it on printed brochures so a person can scan the code to be taken directly to a relevant web page for more information. Real estate agents can put one on an info sheet about a house so a buyer can scan and easily be taken to a webpage with more information about the house. A grocery store can put one on their weekly mailer with a caption that says “scan this for exclusive web coupons” in order to drive more traffic to their website. A business person can put one on their business card which will take a person directly to their LinkedIn profile. It’s basically a hyperlink that exists on a physical piece of paper or sign, etc. Here is an example of a brochure about QR Codes that uses a QR Code.
Another way web-based applications use hyperlinks is by tying them to images. Many applications use images that look like buttons and then tie them to a hyperlink. For example, take these image hyperlinks from Google as examples. They look like buttons, but they are really just lowly hyperlinks.
So, basically the hyperlink is a very under appreciated, but very important part of web-based applications. It’s kind of like the tires on your car. You might like to focus on the powerful engine, the immaculate paint job, or the shiny rims, but you aren’t going anywhere without the tires. Hyperlinks are like tires, they enable you to get to where you want to go.
So enough about hyperlinks, let’s get back to the Help -> Get Link feature in Acumatica. Let’s say that you work in the Purchasing department and, while you are looking up a vendor using the Vendors (AP303000) screen in Acumatica, you notice that the phone number is not correct. Now, when your company implemented Acumatica, they put the Accounts Payable department in charge of maintaining the vendor information. So, you can view the information, but you aren’t able to make any changes. Instead, you need to ask someone in the Accounts Payable department to make changes for you. You can send the AP person a link to the specific vendor record that you are looking at. You can click on Help -> Get Link in the upper right-hand corner of the screen. This will open a window containing two hyperlinks.
Notice how the hyperlink has both the screen id (AP303000) and the vendor id (V000005) embedded in it. This is what allows it to take you directly to the specific vendor record that you want. Now, you might want to change the text of the hyperlink to be more pleasant looking. You can change the text without losing the hyperlink. Just type in something new like this:
I’ll admit that this wasn’t a very exciting example, but at least it demonstrated the point. I do think that there are many different interesting applications of this. Some others that I can think of off the top of my head:
- You notice that a salesperson’s expense report is incomplete so you send them an email with a link to their expense report in the email. Especially if the salesperson is travelling, it makes it much easier for them to open the expense report on their phone when they have a direct link to it.
- A customer who submitted a support case using the Customer Management module in Acumatica calls to get a status update. While you are on the phone with them, you update the notes on the case and send them an email with a link to the case with the updated notes.
- You print a small QR Code on your customer invoices. When a salesperson is out at a customer, the customer shows them the invoice and complains that they shouldn’t have been billed for the service. The salesperson scans the QR Code using their phone which takes them to the invoice screen in Acumatica where they are able to see further information about the invoice, including private notes which give them a better understanding of why the customer was billed.
Acumatica 5.0 will launch at the partner summit this coming Summer. I had a random thought that, since Acumatica is already so closely aligned with Microsoft, maybe they should follow Microsoft’s lead when it comes to naming new versions of Acumatica. Microsoft uses the year of the release in many of their products (eg. Microsoft Dynamics AX 2012, Microsoft Excel 2013, and Microsoft SQL Server 2012). Also, Acumatica seems to have slipped into releasing new versions once a year anyways, so using the year as the version would work well.
What do you think? Should we lobby for the next version to be called Acumatica 2014?
I completed the R180 Abbreviated Business Intelligence course tonight which is a really short course that just points you to some videos on the Acumatica website and then you take a test.
I had seen these videos before when I first started looking into Acumatica almost a year ago. However, true to APAL form, I always seem to learn something new. This time around, there were a few things that caught my attention:
- In the Favorites and Usability Features video, it talks about how you can be reviewing a customer invoice, then a vendor calls wondering if a bill has been paid. You can jump to another screen, then go back to the customer invoice screen, and it will remember which invoice you were looking at. I hadn’t noticed this because I always use the Google Chrome Duplicate feature when I need to jump quickly to another screen. I have to admit though that this is a nice feature. Sometimes it’s the little things like this that make a person really like an ERP system.
- The ability to add an inquiry screen to a dashboard. I haven’t really looked into the dashboard feature that much yet, but I did recently “discover” the inquiry screens and the way that you can create your own custom inquiry screens. I also got a terminology lesson. Even though the inquiry screens are in the Explore folder, they are called “inquiry” screens, not “explore” screens as I was calling them. I leaned toward calling them “explore” screens because I was influenced by my Sage 500 ERP terminology. I stand corrected!
- The financial report writer. I did one post on this a while ago, but, other than that, I haven’t really looked at the financial report writer. The more I think about it, the more I realize how bold of a move this is. In the 1990s, most mid-market ERP systems relied on FRx for their financial reporting which was a 3rd party application. Acumatica’s approach to rely on 3rd party developers to build industry vertical applications on top of Acumatica allows them to focus on the core product which includes their own report writer program. It’s nice to have this built into the product without having to integrate a 3rd party application, especially when it comes to something as sensitive as your company’s financial data.
- The respecting of security policies during imports and in inquiry screens. I haven’t look at security much yet, but I know that Acumatica allows you to go beyond screen-level security to control things like who has access to certain GL Accounts or certain Vendors. There might also be the ability to control security at a field level, such as read-only access to the primary salesperson field on a customer (however, I’m not sure if this is possible yet). The point is though that the imports and inquiry screens use the same business logic that the regular screens use and they respect the security settings for the specific user. No more setting up a separate user that has unlimited permissions for the sake of importing data. Also, no need to design multiple reports with different sections hidden because the security will automatically restrict access to certain data depending on the user who is running the report. You can design one master report with the confidence that the users will only be able to see the data that they have permission to see. I made a previous post about how I wondered why Acumatica needed it’s own report writer when it could have utilized another web-based report writer like SSRS. I hadn’t considered the affect of security. By making their own report-writer, Acumatica was able to make it respect user security. This is pretty cool.
I wasn’t able to spend much time on direct Acumatica learning this week, but I did get to entertain an Excel reporting idea that I think would be very powerful if I can get it to work.
First, a word about Power Pivot. I began using Power Pivot early last year and have become a big fan. At my company, we now do a lot of our reporting in Power Pivot. Recently, over the past few months, I have been attending the Cleveland Excel User Group which focusses on Power Pivot and is organized by a guy named Rob Collie. Rob is the most vocal Power Pivot voice on the planet. If you haven’t heard of Power Pivot, Rob has a nice overview on his blog. He also wrote a book on Power Pivot which does a great job of introducing the Power Pivot formula language (called DAX). Rob has a rare gift for writing about technical stuff in an entertaining way.
Microsoft is structuring their whole BI strategy around Power Pivot and they are adding components to the “Power” family. One of these components is Power Query. Now, Excel has had data connections for a while. You can use data connections by going to Data -> Get External Data on the Excel ribbon. Here is a screenshot from the latest version of Excel (Excel 2013).
Power Query is like a revamp of the Excel data connection feature. Actually, the end result of using Power Query is that an Excel data connection still gets created, but Power Query makes it MUCH easier while giving you A LOT more functionality. You can download Power Query for free here. When it installs, it will show up on the Excel ribbon like this:
There are some interesting options in the From Other Sources menu drop-down which include SharePoint, Email (Exchange only currently), Active Directory, and Facebook. Microsoft continues to add features to Power Query so I would expect this list to continue to grow.
Now, you might ask, “what does all this have to do with Acumatica?” Well, I’m glad you asked. In the most recent version of Acumatica (4.1), an Excel Connectivity feature was introduced. This feature allows you to export the results of an explore query to Excel without losing the live link back to Acumatica. During the export to Excel, Acumatica creates an Excel data connection which allows you to refresh Excel with live Acumatica data by simply clicking the Refresh All button on the Data ribbon.
Now you might ask, “what does this have to do with Power Query?” Great question! Power Query has the ability to connect to web pages and extract the data that is currently being displayed in tables on those web pages.
For example, you could connect to this Wikipedia page (http://en.wikipedia.org/wiki/List_of_United_States_cities_by_population) to bring the list of largest US cities into Excel using the From Web button in Power Query. Once you do this, you have created a link to the webpage. Now, if Wikipedia ever updates the data in the table on that webpage, a simple click of the Refresh All button in Excel will instantly grab the current contents of that table and bring them into Excel.
Why not just use the Excel Connectivity feature that Acumatica worked so hard to develop? What’s the big deal about Power Query? The reason to use the From Web button in Power Query rather than the old From Web button under Data -> Get External Data on the Excel ribbon is that Power Query provides much more functionality. You can remove columns, filter, group, insert calculated columns, etc. all before the data even comes into Excel. Power Query has it’s own language so you can do even more, but I haven’t looked into it too much yet. Also, the Excel Connectivity feature loads the Acumatica data into your Excel file, while Power Query gives you the option to load the data directly into the Power Pivot data model by checking the Load to Data Model box. I hope to write a future power about the benefit of bringing the data into Power Pivot rather than into a normal Excel worksheet.
The Excel Connectivity feature that was recently introduced in Acumatica version 4.1 utilizes the old Excel data connection From Web feature. When you open an Excel file that was exported from an explore query in Acumatica, you can go to Data -> Connections on the Excel ribbon to see the Excel data connection that Acumatica creates during the export. I tried exporting the Account Summary explore screen to Excel and the Data -> Connections screen looks like this:
and finally the Edit Query… button. A login prompt appears and I entered my Acumatica credentials. Don’t forget to enter both your username and the company that you want to connect to with an @ symbol in between like this:
Now, for some reason, I get a The webpage cannot be found error. However, I am able to refresh the Excel file so something must be working. This puzzles me, but more on that later.
I can click the disk icon in the upper right next to the Options button to save the data connection web query definition to my desktop as a .iqy file. I then open the file using Notepad to see the details of the data connection definition:
And this is where I get stuck. I think that I should be able to take the http://localhost/AcumaticaERP/Export/ExcelQuery.axd?companyid=F100%20Examination URL and paste it into the Power Query From Web feature like this:
I actually had an exchange with a very helpful Microsoft employee named Curt on the Microsoft forum:
He pointed out that res://ieframe.dll/navcancl.html# portion before the URL in the data connection definition file looks funny.
I also tried pasting the http://localhost/AcumaticaERP/Export/ExcelQuery.axd?companyid=F100%20Examination URL into my Chrome web browser, but I get a You can’t be here right now!!! error which seems similar to me to the 404 error that Power Query gives.
So, basically I’m stuck because I don’t understand the Excel Connectivity feature well enough and I’m wondering if someone out there can help me out.
My next step will be to see if I can get myself into the Acumatica forum to get help on this issue.
I definitely think that the combination of Acumatica, Power Query, and Power Pivot would be great for reporting.
Yesterday was my first day back in the office in four weeks. Our holiday travel plans took us to California for three weeks where I used PTO on some days and worked remotely on other days. Then the cold spell last week halted our flight, extending our stay in California by another week. No complaints from us though since I was able to continue working remotely without having to burn vacation days.
So, when I finally got back into the office yesterday, I was surprised to see a package on my desk from Acumatica. Inside was a cool wooden box with a bottle of wine and a card inside. I can only assume that someone from Acumatica saw this blog and then got the address of my company from my LinkedIn profile. I was tickled by the gesture. As APAL, I get the satisfaction of knowing that I am investing my time in a cutting-edge ERP product that I expect will pay dividends in my career later on down the road. In the meantime, it’s still nice to get something tangible. Thanks Acumatica!
This week I passed the F140 Advanced Cash Management (V4.0) course. It’s a pretty short course and it didn’t take too long.
The payment reclassification feature stood out to me this time around. It’s nice that you can get all your deposits entered even though you might not know who the customer is, then associate the customer later if needed. This way you can easily do daily bank reconciliations every morning, even if you don’t have all the information for each deposit.
This week I passed the F130 Advanced Accounts Payable (V3.0) course.
I like that you no longer need to schedule an appointment with an instructor in order to take the exam. You can just answer the exam questions while logged into the Acumatica University website. Then, once you pass, your certificate appears immediately next to the course name on the Achievements tab.
While going through the course features, I noticed the Approve Bills for Payment (AP502000) screen which I hadn’t noticed before. I’m assuming that this would be used by the CFO (or someone with approval authority) to approve payments, but I’ll have to look at it further in the future to figure out exactly what it does.
Another thing I began to wonder while going through the course is if Acumatica has the ability to handle invoice approval routings where you could scan invoices and key-in information to be routed to people in the organization who have approval authority on invoices before they become actual bills in the Accounts Payable module. Maybe there is already a 3rd party developer working on this?
Lastly, I noticed on the bottom of my course certificate that Acumatica is registered with the National Registry of CPE Sponsors. I didn’t even know about this registry since I haven’t been renewing my CPA license in an active status and therefore haven’t had to deal with getting CPE credit. So, it’s interesting to know that there is an official registry for CPE sponsors. I looked up the Acumatica sponsor ID # (116008) on this website because I was curious why the hours aren’t listed on my course certificate. Unfortunately, it appears that Acumatica is only approved for the Group Live delivery method which means that you would have to attend one of their courses live in order to qualify for CPE credit. This explains why the hours aren’t listed on my course certificate. I also noticed that the address is the old Virginia address for Acumatica which makes me wonder if this whole CPE thing is something that they still care about. I hope it is, because it would be nice to somehow get CPE credit for Acumatica learning in the future.
One of my favorite features in Sage 500 is Business Insights Explorer. Recently though, I was pleased to discover that Acumatica has a similar feature.
Each module in Acumatica has an Explore section in the Work Area area. For example, in the Accounts Payable module, there are five menu options available in the Explore section: Vendor Summary, Vendor Details, Bills Pending Payments, Checking Pending Printing, and 1099 Year Summary.
However, you have the option to add your own menu options using the Generic Inquiry feature located at System -> Customization -> Manage -> Generic Inquiry.
Just create a title for your inquiry and fill out the tabs on the bottom. I’m going to create a simply list of vendor invoices by filling out the following information in the Generic Inquiry screen:
As long as you fill out the Site Map Location and Site Map Title fields, your new custom inquiry should be available on the Acumatica menu like this (after you save it of course):
In my Vendor Invoices inquiry, you can now just input a beginning and ending invoice date, then click the Refresh icon, and the list of invoices will appear in the grid below. Since Acumatica knows what each field is, it makes the file attachment and record note icons available for each record in the grid. It also gives you a hyperlink on the Reference Nbr. and Vendor fields which allows you to click the link and automatically be taken to the actual invoice or vendor screens.
In conclusion, this is a very flexible and powerful feature that allows you to create data inquiry screens on the Acumatica menu that are customized to fit your company’s specific business needs.
I think this recent TV commercial does a good job of illustrating why it’s good to have the option to run your own private cloud.
I was doing one of the exercises for the F130 Advanced Accounts Payable (V3.0) course today (I know, I’m still not done re-taking the courses, grrr). I was supposed to enter the word “Certification” in the Description field on the Bills and Adjustments (AP301000) screen, but I accidentally entered “Certificatin” instead. However, because I’m using Google Chrome, the spelling error was highlighted with a red line underneath it. It jumped right out at me. Then it dawned on me, I now have a spell checker built into my ERP system! The best part is that I didn’t have to install anything for Acumatica, it’s already available as a feature of my browser. This is yet another great example of why web-based applications are so great (and I’m sure there are more examples to come).
This is a pretty nice feature to have. Imagine if you are an accountant and you are typing a wordy record note on journal entry line to explain why that particular debit or credit is being made. Or maybe you are a salesperson entering your notes from a call with a customer into the CRM module. Or maybe you are doing an inventory physical count and you want to enter a record note on a line that is going to cause a large discrepancy in order to explain the details behind why the inventory can’t be found. In all these situations, you might find yourself writing a few paragraphs and it would be nice to have a spell checker. Before today, I never even thought of the benefit of having a spell checker built into an ERP system, but now I can think of many scenarios where this would be beneficial.
Come to think of it, I rely on the same Google Chrome spell checker while I’m writing this blog post because I’m writing this post in my Google Chrome browser. So, if I need to add a word (like “Acumatica”) to my spell checker dictionary while writing my blog post, that word is now added to the dictionary in my Acumatica ERP spell checker as well because it’s the same spell checker!
Here is a screenshot of the Google Chrome spell checker catching my “Certificatin” spelling error:
Recently, on November 18th, Acumatica announced that it had raised $10 million in funding. At first $10 million didn’t look like that much to me because Xero, a SaaS ERP product aimed at smaller companies, announced on October 14th that they had raised $150 million in funding. However, the problem with Xero is that they are still not profitable, while Acumatica CEO Yury Larichev claims to be already profitable with the comment, “We do not need more funding because we are already making money.” However, the latest funding will enable Acumatica to continue its aggressive growth strategy (they are on track to grow 350% again this year).
Another comment in the announcement caught my attention. An early Acumatica investor says, “We see Acumatica growing to more than a billion in revenue in less than 10 years.”
When I saw the one billion revenue number, it got me wondering about the other major players in the ERP market and how much they do in annual revenue. According to the graphic below from Gartner, here is a breakdown of the market share for the worldwide ERP market. I decided to focus on SAP, Sage, and Microsoft since they are the major players that I’m most familiar with.
SAP is the cadillac of ERP products. They provide the most sophisticated (and expensive) solution. It looks like their most recent annual revenue number is about $18 billion (I multiplied 13.165 by a recent exchange rate of 1.37 USD per EUR). I excluded the consulting dollars because Acumatica doesn’t provide consulting, instead choosing to rely on their partner network (which I think is a great idea).
I’m most familiar with Sage because most of my ERP experience is with the Sage 500 product. Sage is a UK-based company that has a number of different business software products. It looks like their most recent annual revenue is about $2.2 billion (I multiplied 1.376 by a recent exchange rate of 1.63 USD per GBP).
Microsoft doesn’t publish revenue for the Dynamics division which includes their CRM and ERP products, but it looks like a good estimate is between $1.2 billion and $1.7 billion.
Bottom line, if Acumatica can get to the point where they are doing $1 billion in annual revenue, they will become one of the major players in the ERP market. This is especially true due to the following facts:
- Acumatica is one software product, while both Sage and Microsoft rely on a number of different software products in their portfolio in order to reach their revenue numbers.
- Sage and Microsoft got to where they are today by acquiring existing products while Acumatica has organically grown their one product from scratch.
November wasn’t a very perpetual month for this endeavoring PAL. My wife and I had our second child and we have been adjusting to life with two kids. So Acumatica took a backseat (as it should have).
However, in this post I’d like to revisit the previous post on my experience with Acumatica on iPhone. Thanks to Gabriel for commenting on the post and setting me straight that indeed Acumatica does work on the iPhone. I tried using my iPhone to connect to the Acumatica Partner Portal which runs Acumatica of course and which I have access to thanks to ARCVAR. Just as Gabriel said, the Partner Portal works fine on my iPhone. So, of course the problem is not with my iPhone, but with my local laptop installation of Acumatica.
At first I was tempted to try and figure out what was wrong with my laptop version of Acumatica. I concluded that it must have something to do with my installation of Microsoft Internet Information Services (IIS). When I initially installed Acumatica, I didn’t do anything more with IIS other than to turn it on because I wanted to get things up and running as soon as possible. I do know that getting a web server like IIS configured correctly requires a good deal of expertise, especially when you start considering the need for strong security due to the fact that a web server is under constant attack. Since I have a local laptop installation of Acumatica that I don’t care to make available on the internet, I didn’t really care about securing my IIS. However, I didn’t consider that I would run into functionality problems with Acumatica due to my lack of IIS configuration.
The first time I noticed a problem was when I tried to use the new Excel Connectivity feature in Acumatica 4.1. It didn’t work for me on my laptop Acumatica installation. I can’t remember the exact error, but it had something to do with me not configuring a public certificate for https (which I don’t really know how to do).
My inability to use my laptop Acumatica installation on my iPhone is now the second time that I have run into a problem due to my lack of IIS configuration. And, I won’t be surprised if I run into more issues in the future.
So, I decided to try and learn a thing or two about IIS configuration. Maybe I could even get it configured so that I could use my laptop Acumatica installation on my iPhone. I opened up the Internet Information Services (IIS) Manager program located under Administrative Tools on my Start menu and this is what I saw:
I stared at this for a few seconds and then it dawned on me, “this is why why people like SaaS!” This is a perfect concrete example of why SaaS makes sense. Of course, I could spend a few hours going through the different IIS configuration options and using Google to help me understand things. But, my wife and I just had our second child. I don’t have time for this. I want to get back to learning Acumatica. Someone already got Acumatica working on the iPhone as evidenced by the Acumatica Partner Portal. Why should I waste time reinventing the wheel?
Now, of course, there comes a certain point when it makes sense for a company of large enough size to bring their Acumatica installation on premise and invest in people who know how to make it work properly. But, for a guy like me who just wants to learn the product or for a business who just wants to get up and running quickly, why not go with the SaaS option?
So, needless to say, I won’t be spending anymore time on trying to get my laptop Acumatica installation to work with my iPhone. But, at least I know that it works and that’s good enough for me.
Update 06/16/2015: Now, with TryAcumatica.com, I rarely use my local laptop installation of Acumatica.
When I first began using Acumatica, I tried to connect using my iPhone but was unable to. Tonight I decided to try again, but I’m still having the same problem. I can login, but I’m not able to click on any of the navigation links (like Finance or Time & Expenses in the screenshot below).
I’m not sure what I’m doing wrong. Does Acumatica work on the iPhone? This article seems to indicate that it does, but it’s old (from 2010) and the link that it refers to (http://www.acumatica.com/iphone) doesn’t work anymore. So, maybe Acumatica doesn’t work on the iPhone. If that’s the case, I’ll be really disappointed.
It seems like everything that we hear about these days in the IT arena is focused on the cloud. Of course, Acumatica can be run in the cloud and even was the first ERP application to be available for the Windows Azure cloud.
However, I think that we will see the cloud burst at some point, or at least have a hole punched in it. Why?
- People still own houses and cars. We don’t all rent and we don’t all lease our cars. If you have a long-term view, there is an economic advantage to owning a house or a car. I think that there is also an advantage to owning your own digital real estate. Now, of course, the cloud makes sense if you have a smaller scale need, but there are economies of scale associated with technology, just like with anything else.
- The cloud bubble has to burst or at least have a hole poked in it at some point. The dot com bubble and the recent housing bubble are good examples of bubbles that burst. I also think that our society is increasingly susceptible to bubbles because of the flash mob mentality related to trends. The news cycle has shortened, the “what’s cool in school” fads are more short lived, and the well-thought-out research paper gets passed over for the 60 second sound bite. All this creates more frenzy around bubbles.
Eventually, when the cloud hype begins to wane and people return to common sense, Acumatica will still be positioned to accommodate them. You can start in the cloud, but bring Acumatica on-premise when you become large enough. Then, if your company downsizes or your business unit breaks off on its own and you’re small again, you can take Acumatica back into the cloud. Bottom line, it’s nice to have both options.
One of the many benefits to using software that is web-based is that you do all your work in an internet browser and internet browsers are pretty flexible. There are lots of them to choose from and most of them have a pretty extensive list of features.
For me, I prefer Google Chrome. It’s fast (this is the most important feature), all my bookmarks and browsing history sync with the Chrome app on my iPhone, it’s minimalistic design makes more room for viewing webpages, it has one field for typing in a website or doing a Google search, etc. One of the features that is also prevalent in most major browsers (including Chrome) is the ability to do tabbed browsing.
Now, since Acumatica runs within Google Chrome for me, I can take advantage of tabbed browsing in Acumatica.
Right-click on a tab and choose Duplicate to make a copy of your tab to another tab. Some ERP systems don’t even allow you to have the same screen open more than once. With Acumatica, you can duplicate a screen as many times as you want. Maybe you need to run the same report for five different customers. You can quickly duplicate the report screen five times and then run each report simultaneously rather than waiting for each report to finish before starting the next.
Open in new tab
Drag to new window
If your Google Chrome browsing window is getting too cluttered, you can drag a tab to a new window. Just hold down the left mouse button and drag your desired tab down. You will see the window turn into a funny see-through window. Once you let go of the left mouse button, the window will become its own standalone window on the bottom of your screen. You can use this method to group similar screens together.
You can hold down Ctrl on your keyboard and press the Tab key to alternate between Google Chrome tabbed windows. Of course, holding down Alt and pressing Tab will move you through each window, such as Journal Transactions and Invoices and Memos in the screenshot above. But Ctrl -> Tab will move you between tabs that are within the same window.
Tonight I passed the F120 Advanced Accounts Receivable (V3.0) course.
I had some trouble with the customer statement feature in the beginning because it seemed to me that you could create multiple customer statement cycles for the same set of customers. It wasn’t clear to me that you have to assign a customer statement cycle to a customer in order for it to do anything. I was using the Prepare Statements (AR503000) screen and things seemed to be processing fine, but when I would go to Print Statements (AR503500) there was nothing to print for my statement cycle id. I was even able to delete my customer statement cycle in Statement Cycles (AR202800) after preparing the statement which indicated to me that no statement records were actually getting generated. However, once I finally put my statement in the General Info -> Statement Cycle ID field for a customer in the Customers (AR303000) screen, I was able to find records in Print Statements (AR503500) after preparing the statement. Bottom line, each customer can be on one statement cycle which makes sense. You wouldn’t want a customer to be getting statements on two different cycles simultaneously.
I’m starting to gain a little bit of momentum in getting through the Acumatica University courses correctly. Tonight I passed the F110 Advanced General Ledger (V3.0) course.
One of the things that stood out to me this time around is the hard closing of sub modules. The product that I currently use (Sage 500) is just now finally incorporating this into their current version (7.5). It’s nice that Acumatica is including this from the beginning.
I wanted to test that the hard closing of sub modules feature actually works, so I went into the demo company and closed everything in AP up through period 01-2013. After closing the periods, the Financial Periods (GL201000) screen looks like this for year 2013:
Then I tried to create a bill in the Bills And Adjustments (AP301000) screen with a date that falls in the closed 01-2013 period. To my surprise, I was able to post the bill, although I did get this warning:
As first I was disappointed and thought that the feature wasn’t working. However, I then remembered the Allow Posting to Closed Periods checkbox that is in the General Ledger Preferences (GL102000) screen. Apparently, this is checked by default in the demo company.
So, it looks like the feature is working (whew!). Also, it’s nice that Acumatica gives you the option to hard close sub modules or not. It’s up to you.
One of the reasons that I like posting videos that are zoomed in (rather than showing my entire computer monitor) is that I like consuming content on my iPhone while I’m on the go. I like listening to the Tech Tuesday series on the Acumatica University site, but I don’t like that the video portion is showing the full screen of a computer monitor because it makes it impossible to watch on my iPhone. In the case of Tech Tuesdays, I don’t think there is an alternative because they need to show the entire screen due to the large amount of information being conveyed (it works out though because I can listen on my phone while mowing the lawn without being tempted to watch the video). However, in other cases, it’s nice to be able to view the video on a phone. The point is, learning ERP on a phone is nice. You can learn while at the gym, while driving (audio only of course), taking a walk, mowing the lawn, etc.
This week I discovered two new ways to learn ERP stuff on my phone.
The first is a Podcast from a guy named Rick Nielsen. He has insightful things to say about implementing ERP. The Podcast is called Implement ERP FAST! and it’s available in the Podcast app on the iPhone.
The second thing I discovered is that I can listen to the new book on Data Warehousing that I downloaded to my Kindle app. I always knew that I could read books on the Kindle app, but I didn’t realize that I can listen to them too. The key is to use the iPhone VoiceOver feature by going to Settings -> General Accessibility -> VoiceOver and turning the feature off. In the new iOS 7, you can even make a shortcut to turning this feature on and off by going to Settings -> General Accessibility -> Accessibility Shortcut. When the feature is on, all you have to do is go to your Kindle app and swipe down with two fingers on the iPhone.
Since I finally passed F100, I decided to upgrade to version 4.1 before proceeding any further with the Acumatica courses. My experience with the upgrade was similar to my experience with the installation, very simple and straightforward.
There are a few new things in version 4.1 that caught my attention: [Read more…]
It took me a while to get through the exercises again because of family vacation and Summer house projects. Hopefully I’ll be able to tackle the next exam, F110 Advanced General Ledger (V3.0), in less time.
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.
Update (May 31st, 2016): For a more detailed post on this topic, click here.
One of the most consistent things across ERP systems is the General Ledger Account Structure where the GL Accounts are given a certain structure according to your business needs. While this isn’t complicated, sometimes it’s nice to see how the screens look so you get a feel for the product. It’s kind of like a first date with your ERP system. So, with that in mind, here is a 4 minute video showing the relevant screens in Acumatica related to the General Ledger Account Structure setup.
I’m not positive what the length limits are, but, as far as I can tell from looking at the database structure, the maximum length of an ACCOUNT segment is 10 and the maximum length of a SUBACCOUNT segment is 30. As far as a limit on the number of segments within ACCOUNT or SUBACCOUNT, I’m not sure that there is one, theoretically. After recording the video I went back in and added a bunch more segments, all of length 30, to the SUBACCOUNT segment and didn’t get any errors (see screenshot below). I then defined some valid values for a few of the segments using the Segment Values (CS203000) screen and I still didn’t get any errors. I’m not sure why you would need so many segments, but it’s always nice that you could make things incredibly complicated if you wanted to.
A couple of weeks ago I took a trip home to meet my nephew who was just born a couple of months ago. My wife and daughter had flown out a week ahead of me so I made my trip solo.
My connecting flight in Philadelphia was supposed to take off around 9:00p, but we didn’t end up leaving until around 2:00a. Note to self, don’t take US Air ever again. We had two planes fail on us before the third one finally came through.
However, because I had my laptop with me, I was able to take advantage of the situation and continue my Acumatica learning. I found a comfortable seat in the corner of the terminal next to a power outlet, put my headphones in, and went to work. It really did make the whole experience less frustrating since I knew that I was being productive.
Note: If you are looking for an Acumatica partner in Philadelphia, checkout The LLB Group.
Sergey has a nice post up about how to easily translate all the field labels in Acumatica into another language:
I’ve never worked on an ERP translation project, but Sergey’s post makes translation in Acumatica look very simple.
I wanted to create a new company in Acumatica that would only be used for the F100 Hands-On Financials (V4.0) course that I am retaking, but I couldn’t remember how to do it. So, after figuring it out again, I thought I’d document it here so I can use these instructions next time I need to create a company. Please note, these are not official instructions, but I think they get the job done.
7. Click the Next > button
11. Login with Username: admin / Password: setup
It has been 5 days since I failed the examination, but I have been under the weather this week so I am just now getting around to starting the redo. I am starting by deleting the folders with the course material since I know that Acumatica University is frequently changing and the material might have changed since I last downloaded it. I plan to put in two Pomodoros tonight and then get back to painting the bathroom. Here we go!
I didn’t realize until reading this article today that NetSuite is so aligned with Oracle.
“A pioneer in the SaaS (software as a service) market, NetSuite has Ellison as an investor. Its software has typically run on the Oracle stack including the database.”
I don’t know that much about NetSuite, but this article helped me further differentiate between Acumatica and NetSuite. I already knew the following differences:
- Priced per user (NetSuite) vs. Unlimited users (Acumatica)
- SaaS option only (NetSuite) vs. SaaS and on-premise options (Acumatica)
- Forced upgrades (NetSuite) vs. Upgrade on your own schedule (Acumatica)
But, now I can add a new item to the list:
- Oracle Stack (NetSuite) vs. Microsoft Stack (Acumatica)
I definitely prefer the Microsoft stack. Microsoft knows business applications: SharePoint, SQL Server, Exchange, Lync, Word, Excel (with Power BI), PowerPoint, Project, Visio, Access, etc. They may not be the most powerful products in the market, but they are the easiest to use. You will always find an army of Microsoft professionals available because Microsoft understands the people side of software. It’s not just about the software, it’s about people using the software. Oracle has a good database product, but nothing beats Microsoft when it comes to the stack.
Props to Acumatica for going with the Microsoft stack.
Well, yesterday I failed the examination for the first Financial module course. I then failed the second examination. Before failing the third examination, I decided to stop, not wanting to waste the instructor’s time.
Basically, the examination consists of an Acumatica instructor checking my Acumatica version to see if I was able to perform all the exercises that were in the course material. They spot check different things to make sure that I followed the exercises to the letter. I thought I had completed the exercises correctly because they seemed easy enough, but the examination proved otherwise. If there is anything that is not exactly what they are looking for, you fail the exam. As the instructor put it, “this isn’t horseshoes,” being close doesn’t count.
Now, I get to re-perform the exercises. This time I am going to do the examination after each course instead of attempting them all at the end. There are six courses:
- F100 Hands-On Financials (V4.0)
- F110 Advanced General Ledger (V3.0)
- F120 Advanced Accounts Receivable (V3.0)
- F130 Advanced Accounts Payable (V3.0)
- F140 Advanced Cash Management (V4.0)
- F300 Inter-Company Accounting (V3.0)
It has been almost three months since I started the first financial module course. I honestly don’t remember that much from it. So, although I am definitely discouraged that I didn’t pass, it might be a good thing that I have to redo everything. Going through things a second time will help to reinforce what I learned.
This time around I am going to make a few changes:
- Take the examination for each course immediately after performing the exercises rather than wait until the end (it’s hard to remember what you did three months ago).
- Create a separate company for each course and name each company with the ID of the course (F100, F110, F120, F130, F140, F300). That way I can be sure that the only transactions to take place are due to the certification exercises.
- Apply the Pomodoro technique instead of staying up really late trying to finish a course.
This somewhat humiliating experience of failing the exam reminds me of my Aunt Carol. She failed Statistics twice in college before finally passing the course. She then went on to get her PhD and wound up teaching the course at the university. The point is that many times the harder it is to learn something, the better you understand it in the end. Especially with ERP, the tortoise wins the race. Failing the examination also helps remind me that my goal is to be APAL, not ANEXPERT. Even once I eventually pass the examination, my goal is to maintain a learner’s attitude towards the financial module. There are always new things to learn.
Let the Financial module course redo begin…
Today I am scheduled to review my Financial module exams with an Acumatica University Instructor. I did do a quick review of things yesterday to refresh my memory on everything that I learned. I have no idea what to expect. We’ll see how it goes…
Last week someone posted on the LinkedIn Acumatica User Group asking about how to create a rolling monthly financial report.
Now, it’s always dangerous to assume that you know what someone is asking for just by reading a couple of sentences, but I thought I would live dangerously and try to answer the question.
Disclaimer: This is my first time trying to write a financial report in Acumatica so MY METHOD MIGHT BE COMPLETELY WRONG. But, it does seem to work, and sometimes that is good enough. I have gone through part of the course on Acumatica reporting, but nothing so far that relates to the financial report writer. My method is based purely on dusting off my FRx knowledge and trying to make it work in Acumatica.
So, here it goes. In order to demonstrate the concept, I made a less than 8 minute long video on YouTube.
I realized that my Custom Vendors Report was not correct. This was due to the Business Account concept in Acumatica. I’m still not sure of the reason for it, but there is an entity called a Business Account (BIZACCT) that is the parent entity for both Customers and Vendors. If you go to the Segmented Keys (CS202000) screen, you can see that both Customers and Vendors share the same parent entity, BIZACCT.
What this means is that both customers and vendors store some information in the BIZACCT entity. When I designed my Custom Vendors Report, I didn’t realized that I actually made a report which displays Customers and Vendors. I only realized this when I dropped an extra field on the report: Vendor.CreatedByID. Once I dropped this field on the report, the report looked like this:
I immediately noticed that the create user (admin) only appeared next to some of the records. The reason is that only the vendors displayed the create user. The customers did not display a create user because I dropped a vendor-specific field (Vendor.CreatedByID) on the report. The fix? I just needed to go to File -> Build schema… and change the Join type from Left to Inner under the Relations tab.
Now my report only displays vendors. Consequently, the create user is now displayed for every record.
I barely made it through the first 66 pages of the 112 page training manual on Acumatica Report Designer because it was really dry and boring. To be honest, I didn’t read it carefully, but instead skimmed through.
The concepts are consistent with other report writing programs like SSRS and Crystal Reports and the training material is more like a reference manual than training material. However, I do think that I will be referring back to it in the future since it’s a good resource. For me, the best way to get comfortable with a new report writer is to start writing some reports!
That’s why I was happy to finally see on page 68 how to save and publish a report.
2. Go to File -> Save On Server… and give the report a name (I used Vendors.rpx). I also checked the Save as new version box which I’m hoping will keep a snapshot of this version of the report somewhere in the database (with Crystal Reports I always append the date to my versions which is a really duct tape way to do versioning).
3. Now for the cool part. The training course introduced me to the Site Map (SM200520) screen which is how you can make modifications to the Acumatica menu. In this case, all we need to do is add a new item to the menu for the new report. Since this is a vendor report (a really simple one), I decided to put it under Finance -> Accounts Payable -> Reports -> Balance. All I had to do was browse to the folder and then add a new entry (the highlighted one). You can click the image below to get a clearer picture.
I’m starting to warm up to Acumatica Report Designer after seeing how easy it is to publish a report and how nicely it integrates into the Acumatica application. I’m sure that SSRS reports still have their place, but a user would definitely notice that they are leaving Acumatica when running an SSRS report. Also, reports created with Acumatica Report Designer become just like any other screen as far as security (who has access) is concerned. On the other hand, SSRS security is maintained in an entirely separate area. Finally, when running a report created in Acumatica Report Designer, a user has no idea whether they are using a standard report or a custom one. This is exactly the kind of experience that I want a user to have when running reports.
On the topic of Acumatica reporting, I came across this nice little YouTube overview video from Acumatica.
I have to admit, at first I was disappointed to learn that Acumatica has its own reporting program. I was hoping that Acumatica would leverage SQL Reporting Services (SSRS) rather than create its own standalone report designer. The reason is that there is already so much functionality built into SSRS. I didn’t see why Acumatica would reinvent the wheel.
I don’t know the technology behind SSRS that well so I don’t know much about what would have been required to integrate it into the Acumatica framework. I just know that it’s a good report writing program with lots of cool features, it’s web-based, and it has a better future than Crystal Reports (in my opinion). Acumatica Report Designer reminds me a lot of SSRS and Crystal Reports.
There is a Data Access Class (DAC) layer in Acumatica that I don’t understand yet. The picture above is what I picture in my head. Now, as far as I can tell, the main advantage (from a reporting perspective) of leveraging the DAC layer is that the report might be able to control the content that gets presented to a user. For example, the report might show sales per branch with sales on the left and branches going across the screen as columns. If a person doesn’t have access to a specific branch, the report wouldn’t show them data for that branch. I’m not sure if this is how it works, but this is how I hope it works. It would be cool to have the same report display different data depending on who is running the report.
I think there is still a place for SSRS and Power Pivot reports using the traditional reporting method pictured above, but it would be really, really cool (assuming that the previous paragraph is true) if I could leverage the DAC layer even with SSRS and Power Pivot reports. Hopefully the S130 Reports Dashboards and Inquiries course will shed some light on the subject.
Acumatica gives every screen a unique ID. This might not seem like a big deal, but I think it is.
No matter how you determine the screen ID, the important point is that there is a screen ID. This might not seem that important, but it sure helps a lot when you need to refer to a specific screen. For example, the Accounts Payable and Accounts Receivable modules both have a screen with the name Recurring Transactions.
When you are referring to one of the screens in an email, it might be helpful to include the correct screen ID (the one for Accounts Payable or Accounts Receivable). Or, better yet, you could just include a link to the correct screen (replace localhost and AcumaticaERP with your paths of course):
http://localhost/AcumaticaERP/(W(1))/?ScreenId=AP203500. When a user finds the screen by entering the screen ID, they can also see the path to the screen because the navigation page adjusts to show which module and which area of the module the screen is in.
Again, this might not seem like a big deal, but I think that it helps a lot when writing documentation or communicating with colleagues about Acumatica. I often hear people get lazy when talking about screens in an ERP system and the name they use is not the actual screen name, but some creative variant. Having screen IDs helps to set the record straight and prevent misunderstanding, especially among new employees who might spend hours looking for a screen name that doesn’t exist before asking someone for help.
SAP employs the same technique for giving screens an ID (eg. FD01 or XD03). I’m glad that Acumatica does too.
Today I listed to the DN003 – Developer Network – Tech Tuesday course on my iPhone while vacuuming. I kept an eye on our one-and-a-half year-old daughter while my wife was at the gym, got some chores done, and learned some more about Acumatica. Now that’s some good APAL multi-tasking.
Tech Tuesdays are a conference call and screen-sharing session given every two weeks for developers. Most of the info is over my head, but I am able to still pickup some useful tips and tricks. The recorded sessions are available on the http://www.acumaticauniversity.com website. So far, they run about an hour.
It’s nice that the http://www.acumaticauniversity.com videos play on my iPhone. I’m able to go to the site on my phone’s browser, login, and see the full list of courses:
I can choose a course and play the audio and video right on my phone. The only downside is that it plays in the phone’s browser so I can’t lock the screen if I want it to keep playing. This causes the battery to drain a little faster than it normally would and I have to make sure not to accidentally touch the screen, but it’s not too big of a problem.
The convenience of having Acumatica videos available on my phone really helps to put the “perpetual” in Perpetual Acumatica Learner. I can even learn while vacuuming!
I thought I should make a post with short explanation of the term ERP.
ERP stands for Enterprise Resource Planning. It is a term that was coined by Lee Wylie at Gartner in 1990 as an extension of the term MRP (Material Requirements Planning). While MRP focusses on inventory ordering and supply chain management, ERP encompases the entire organization.
I use the term ERP to refer to any system that includes the accounting data. So, I even consider Quickbooks to be an ERP system. However, SAP or Acumatica are better examples of ERP. A true ERP system covers a lot more than accounting, such as:
- Customer Relationship Management (CRM)
- General Ledger
- Cash Management
- Accounts Payable
- Accounts Receivable
- Internal Accounting
- Overhead Cost Allocation
- Fixed Assets
- Sales Orders
- Purchase Orders
- Bar Coding
- Bills of Material
- Work Orders
- Shop Capacity Scheduling
- Quality Control
- Product Life-Cycle Management
- Human Resources
- Project Management / Contract Management
- Resource Planning
- EDI (Electronic Data Interchange) interfaces with customers and vendors for automatic ordering
- Customer and Vendor self-service portals
The Acumatica Report Designer is a stand-alone program that allows you to create reports in Acumatica.
Here was my first experience using the program.
3. Enter the path to your Acumatica installation, then enter your login info. Note that, if you have multiple companies as I do, you have to put the company name after the username (separated by @), otherwise you will get an error that says:
Error during login: PX.Data.PXUndefinedCompanyException: Unable determine proper company id for the request.
at PX.Data.PXDatabaseProviderBase.getCompanyID(String tableName, companySetting& setting)
at PX.Data.PXDatabaseProviderBase.getRestriction(String table, String alias, Boolean mainRestriction, Boolean isRightJoin, Nullable` 1 effectiveCid)
at PX.Data.PXDatabaseProviderBase.appendWhereClauseToSelect(String tableName, PXDataField pars, StringBuilder bld)
at PX.Data.PXDatabaseProviderBase.SelectSingle(Type table, IEnumerable` 1 joins, PXDataField pars)
at PX.Data.PXDatabase.SelectSingle[Table](PXDataField pars)
at PX.Data.PXDatabaseMembershipProvider.ValidateUserPassword(String username, String password, Boolean onlyAllowed)
at PX.Data.PXActiveDirectorySyncMembershipProvider.ValidateUser(String username, String password, String& providerLogin)
at PX.Data.PXActiveDirectorySyncMembershipProvider.ValidateUser(String username, String password)
at PX.Api.WebServiceBase.Login(String name, String password)
6. Note in the screenshot above that I selected the gl633500.rpx report. I knew that was the report that I wanted because I wanted to edit the Transactions for Account report in the Financial module. Since every screen in Acumatica has a unique ID, the report is actually just the ID for the screen in this case. You can determine the ID for a screen by putting your mouse over the screen name in the Navigation Pane or by clicking on the screen name, then looking at the address that appears on the top of your browser. As you can see in the screenshots below, both methods yield the result of GL633500.
7. Now I have a scary looking report that I can break if I’m not careful. Hopefully, after going through the 112 page S130 Reports Dashboards and Inquiries course, I will know what all the stuff below means.