Intro from Tim: Back in February 2017 at the Acumatica Summit in San Diego, Acumatica let me teach the S130 reporting class. And they also let me sneak in pretty much a full day on Power BI. Kevin was in that class and he had never heard of Power BI before, but he took it and ran with it. He even took it a step further and actually used Power BI to positively change organizational behavior, something that is hard to do. Kevin is one of my heros. And I’m so glad that he was willing to write a guest post about his experience using Power BI. Take it away Kevin!
Intro and Obligatory Back-Patting
Hello AUG Members!
It has been a little over a week now since our team and I returned from the Acumatica 2018 User Summit, and before I dive into the topic of this post, I would like to expand upon my experience at the summit in Nashville this year.
As usual, there was plenty to be excited about with Acumatica, and all the capabilities that are up and coming left us in awe. The classes were educational and taught us many useful skills to improve our business processes.
I myself attended the Data Query and Financial Reporting class in hopes to improve my Generic Inquiry writing. I received what I came for, in this year’s class led by Doug Johnson. He did a great job teaching us about ARM, Generic Inquiries and Report Designer, and I was grateful to learn more about those.
My only complaint was that the Power BI reporting was absent this year. I think they decided to merge the Report Designer class into the Data Query class and, unfortunately, the details on how to feed your generic Inquiries into Power BI was taken out of the class discussion this year.
I say “details” because it was mentioned that you can use Power BI to display your inquiries in Acumatica, but not enough information was provided to the users on how to make that happen. As disappointed as I was about this, I could almost understand someone’s decision to push this info out of the class. I am sure the argument went something like, “This is not a Microsoft Summit, why are we teaching users how to use a Microsoft Application to display their reports when they have Acumatica Pivot Tables, Dashboards, and (worst of all) Report Designer!?”
Come to think of it, reporting with Power BI seemed to be Acumatica’s best kept secret at this year’s summit. The only worthwhile mention of Power BI was a small segment hidden away at a 50 min lightening session for developers led by Power BI Super Hero, Tim Rodman.
It was called Lightning session for a reason. There were two other topics unrelated to PBI that took place before Tim’s Power BI segment. Although those first topics were interesting, that only left Tim 15 min. to talk about the use of Power BI in Acumatica. It was a great overview, but there was just not enough time to really get into the full capabilities. 15 min. is barely enough time for some people to even tie their shoe, but Tim did make the most of the time he had.
During that 15 min. session though, I remember Tim had echoed a question that someone must have asked him at some point. The question was something to the effect of, “Why would we need to use Power BI at all, what can Power BI do that Acumatica’s reporting can’t do?”
Tim said he really did not have a good answer for that, and to be honest, neither did I at the time.
Now that I have had a moment to think about it, I would say the one thing Power BI reporting has that Acumatica reporting does not have is a simple user interface.
If I get my block of data out of Acumatica, and I want to make changes to it, I do not have to be an SQL-query writing genius to figure out how to manipulate my data the way I need it using Power Query in Power BI.
Also, creating visualizations out of data has never been easier. If I wanted ONE report that can display a graph, also have data numbers for the number people, complete with on the fly filtering, and can drill down to the records for easy exporting to an excel spreadsheet, while summarizing dates like a pivot table, and have multiple pages for the report, I could use Power BI and have that report finished within an hour.
Acumatica Reporting Options in a Nutshell
Which brings me to my point, it may be possible to create one report that can do all that in Acumatica, but right now it is just not practical to.
If report designer, dashboards, generic inquiries expression writer, and pivot tables in Acumatica all had one baby, it would be named Power BI.
Bottom line, if you need financial statements, go ARM reporting tool in Acumatica.
If you need an inventory form report to print out for people to submit, try Report Designer.
But if you need a flexible report that keeps your business performance humming, go Power BI!
World’s Best Power BI Water Fountain
Now I would like to provide an example on how displaying Power BI reports improved our business processes.
We are a software company, and like most software company, we rely heavily on time entries. Whether it is a billable time entry made by an employee working on site for a customer in the Philippines, or a time entry made by a developer that we can’t bill but will want to include on a R&D report come tax time.
We want our time entries to make us money and save us money. We wanted a report to hold people accountable.
We ran into situations where employees would go on site, perform weeks working on site, and forget to log some of their time we should have billed all our customers for.
On the flip side, there were cases where we would have developers not logging all their time under our R&D project and we were missing out on writing off a greater portion of those salaries on the R&D tax credit.
I was reading one of Tim’s posts concerning reports being displayed by a water fountain, or the coffee machine that made for easy access for employees to quickly glance at the report and perhaps serve as a reminder or maybe lead to them fixing mistakes automatically.
My boss decided to get a large 60 inch tv to serve as a display and post it right next to the water fountain and the kitchen. This is what we ended up with.
Organizational Behavior Change
We have three Power BI reports the tv monitor cycles through.
One is a % of 40 hour work week that was billable time. This one has conditional formatting that shows green for great billable hours, blue for good, purple for mediocre, and red for problematic.
Another report tells us how many days since last time entry they made in Acumatica, both (billable and non- billable time entry).
And the last report the display cycles through is a report on which project managers need to approve time. It is also worth noting we have the Weather Channel radar cycling along with reports as an attention grabber (it just seemed appropriate).
Now we do have management Time Reports that we bounce back into Acumatica as depicted below that are fully intractable, but these are not available to everyone.
Maybe a week after everyone noticed the reports, we started to see vast improvement of employees remembering to log their time.
And we rarely have to go back and make sure that everyone has entered their time or get onto project managers about approving time so we can bill it.
Displaying these reports have made a great difference in our working environment.
The Technical Stuff
Anyone that is wanting to give this is a try for themselves would need to build the generic inquiries in Acumatica, load their inquiry onto Power BI Desktop, then publish the data to Power BI online.
Make sure to schedule hourly refreshes of the data to keep your reports up to date, build the reports you want displayed on Power BI, then publish the report to web.
Keep in mind if you publish the report to the web, it is out there for the world to see so I would keep all sensitive information in an Power BI report safe inside Acumatica.
Then you will need a tv and a micro pc to mount behind the tv to run google chrome. My boss had us get a Dell Micro PC for a couple hundred bucks, but I think there are cheaper alternatives that could make this work.
It’s also worth noting you need to make sure that your mini pc does not sleep. This can be done in the windows settings. I have not experimented with the cheaper alternative, but I would think it would not go to sleep on you.
Then you will need to add the Revolver extension to Chrome on your mini pc.
Then you open all the reports you posted to the internet with Power BI. Each report needs to be open in a separate tab in chrome on the mini pc that your TV monitor is hooked up to.
Now you need to go into the revolver settings to make sure to check the refresh web page on cycle box, and set the timer you want to display each report, full screen your mini pc, and Whala!
Tim has tons of articles on using Acumatica Data in Power BI on his Acumatica User Group if you want to try this and get stuck on creating the reports you want to display.
If anyone ends up giving the cheaper alternative a try please let me know, I would be interested to know if that worked for you!
Special thanks to Tim for establishing this site and making all of this possible.