Update July 06, 2023 – This is now a real thing:
AugSQL (click here)
This post is focused on using Reporting, Dashboards, Pivot Tables, and Business Intelligence in Acumatica. SQL can do a lot, but my focus here is making it easier to use SQL queries with the Acumatica reporting tools.
This would make reporting in Acumatica even more powerful. Dashboards are cool, but 80% of the work with Dashboards is getting the data, and SQL makes getting the data A LOT easier. With this idea, you could essentially feed SQL into a Generic Inquiry and use that Generic Inquiry in a Dashboard.
If you love SQL (like I do), then this post is for you.
If you hate SQL, then this post probably isn’t for you.
If you don’t know what SQL is, then this post might be for you.
SQL stands for “Structured Query Language” and it’s the standard language used to get information out of the kind of databases that are used by ERP systems like Acumatica.
These kinds of databases are called relational databases. Examples include Microsoft SQL Server, MySQL, and Amazon Aurora.
Since Acumatica runs on a relational database, SQL is an important part of Acumatica.
Why SQL Matters
SQL is similar to HTML and Excel Formulas in terms of importance, long history, and ubiquitousness in the software world.
HTML is the “language of web pages”.
Excel Formulas are the “language of spreadsheets”.
SQL is the “language of databases”.
Also consider this: there are A LOT of people out there who already understand HTML and Excel Formulas.
Similarly, there are A LOT of people who already understand SQL.
In fact, SQL is so popular that people from around the world occasionally give up their Saturdays to attend SQL Saturday Events (click here).
SQL is important to Acumatica because ERP data is highly structured. The highly structured nature of ERP data is why ERP data is best stored in highly “normalized” Relational Database Management Systems (RDMS) like Microsoft SQL Server and MySQL.
So if you want to be able to “talk” to your Acumatica ERP data, there is no better language than SQL. It’s an oldie, but a goodie.
Side Note: BQL in Acumatica is great for developers, but there are MANY more people who know SQL than people who know BQL.
Another Side Note: In recent years there has been a lot of talk about something called NoSQL. Proponents of newer NoSQL technology like to make fun of SQL as being a technology relic. As with anything new, the first assumption is that the new thing is the cure for every problem. But NoSQL comes from the world of data analytics which often operates on unstructured data that gets stored in a “blob” file storage format, a data lake, a NoSQL database, etc. Unstructured data is “flat”, not normalized like ERP data. A SQL database is still the best way to store ERP data, just ask anyone (cough cough “Kenandy” cough cough) who has ever tried to build an ERP product on the Salesforce platform.
The Darkside of SaaS
SaaS (Software as a Service) is very popular these days and for good reason.
You not only “rent” the software, but you also outsource the expertise needed to keep it running and up-to-date. It’s more cost effective, especially in such a fast-changing technology landscape where heavy staff investment in narrow technology areas can be risky.
But there is a darkside to SaaS.
SaaS can become too much of a “black box”.
Inside of the “black box” there are often multiple databases with APIs doing the heavy lifting to move data between the databases.
This is especially true when multiple applications are involved behind-the-scenes to deliver a front-end solution.
I have always liked how “open” Acumatica is. It doesn’t feel like a “black box”. Even though Acumatica has a SaaS offering, you still have the ability to see under the hood. If you want, you can even install Acumatica locally and explore how things are stored in the Acumatica SQL database.
Contrast Acumatica with NetSuite, Intacct, and Microsoft Dynamics 365, and you’ll find that those other products are much more of a “black box” with mysterious layers that somehow work “automagically”.
Also, Acumatica is much more “friendly” when it comes to accessing the data. In fact, access to the data anytime is part of the Acumatica Customer Bill of Rights (click here).
However, even with “open” and “friendly” Acumatica, you don’t get direct access to the SQL database, at least not in an Acumatica SaaS environment.
You can run a SQL query directly against an “on-prem” or “hosted” Acumatica environment, but you can’t run a SQL query directly against an Acumatica SaaS environment.
So the darkside of SaaS (even Acumatica SaaS) is that you can’t directly query the SQL database.
Current Acumatica SQL Query Method
Although you can’t currently run a SQL query against an Acumatica SaaS environment “directly” using a SQL query application like Microsoft SQL Server Management Studio, there is a way to run a query “indirectly”.
Actually, there are a couple of ways to do this.
The first way is to create a Generic Inquiry, which is kind of like constructing a SQL query graphically. You still pick your tables (FROM clause), do your joins (JOIN clause), apply filters (WHERE clause), etc.
But Generic Inquiries aren’t as flexible as SQL, especially when it comes to things like subqueries, filtering, UNION statements, etc.
The second way is to create a SQL View as outlined in this famous Acumatica SQL View post by Doug Johnson (click here). With this method you create a SQL View and connect it to a Data Access Class (DAC). You can even use this technique in an Acumatica SaaS environment using Acumatica Customization Projects.
But this SQL View method has a couple of problems. First, it’s cumbersome to do. Creating the Data Access Class (DAC) is really tedious. Second, technically you can use this method to grab data from another tenant, making Acumatica not truly a multi-tenant solution.
Acumatica Partners and SQL
Before we move to the final section of this post showing an idea about how to make Acumatica more SQL friendly, let’s step back and consider Acumatica Partners and SQL.
I personally have had numerous conversations with new Acumatica VAR partners over the years. These VAR partners knew they needed to pickup a Cloud ERP offering. They would find me through this website and get my opinion about why I’m so enthusiastic about Acumatica.
In talking with these VAR partners, I noticed several common themes that would emerge.
One of those common themes was SQL, especially the ability to run a SQL query “directly” in an Acumatica On-Prem or Hosted environment or “indirectly” using the Doug Johnson post mentioned above.
SQL is a skill set that many VAR partners have built up over the years and knowing SQL is EXTREMELY useful when it comes to creating reports in an ERP system like Acumatica.
Many Acumatica VAR partners like that they can leverage their existing SQL knowledge for reporting in Acumatica and SQL was a factor in why they decided to add Acumatica to their portfolio of ERP product offerings.
An Acumatica SQL Query Idea
Here’s an idea to make Acumatica more SQL friendly and make reporting in Acumatica even more powerful.
This idea is basically a way to make the SQL View method in the Doug Johnson post mentioned earlier easier to accomplish, making it easier to turn SQL into a Data Access Class (DAC) that could be used in Generic Inquiries and Report Designer.
What if we had a screen in Acumatica called “SQL Query“?
The SQL Query screen would have two tabs: SQL and FIELDS.
Here is my rendition of the screen on a piece of paper:
Let’s go through the various fields and tabs on the screen mockup one by one, starting from the top and working our way down.
Buttons (area) :: The buttons area would have all of the standard buttons like you see on the Generic Inquiry (SM208000) screen (Save, Add New Record, Delete, etc.). It would be awesome to also have the Clipboard icon to copy/paste a SQL Query or export it and import it into another tenant. Being able to package SQL Queries into a Customization Project at some point would be even cooler.
Query Title (field) :: Similar to the Generic Inquiry (SM208000) screen, this would become the primary identifier of the SQL Query. No spaces would be allowed. The Query Title would also become part of the Data Access Class (DAC) that gets created. All Data Access Classes that get created by the SQL Query screen would begin with the same thing, maybe “SQLQuery”. Let’s say that the Query Title is “CustomersWithSalesThisYear”. Then the DAC would be something like this:
“SQLQuery.CustomersWithSalesThisYear”.
SQL (tab) :: This tab would have one giant text field where you would paste your SQL Query (a SELECT statement). This SQL Query could include anything that you could do in a SQL SELECT statement, including subqueries, GROUP BY clauses, UNION ALL clauses, etc. You’d still need access to an On-Prem or Hosted Acumatica database for crafting the SELECT statement, but then you’d paste the resulting SQL into this tab. When you do anything to move out of the text field (click SAVE, click on the FIELDS tab, etc.), then some validation would kick in. The validation would be necessary to ensure that the SQL respects Acumatica’s multi-tenancy and doesn’t do anything devious to get data from another Acumatica tenant. The validation is the tricky part and something for the clever developers in Moscow to figure out. One idea I have is for the validation to automatically insert CompanyID=CurrentTenant logic into the SQL code for every table used in the SQL code (this would get tricky with JOINs, but should still be doable). There might even be some extra general formatting rules that your SQL would need to follow to make this validation process easier for Acumatica to accomplish.
FIELDS (tab) :: This tab would list as rows the SQL columns that would get added to the DAC as fields in the DAC. The following columns on this tab would allow you to set properties for each DAC field:
ID (field) :: This column would display the SQL column name from your SQL code. In my screen mockup above, SQL1 and SQL2 would be listed as rows in this column. This would become part of the DAC that gets created by this SQL Query screen. Using the DAC example from above, if your SQL column name was “MyCustomerField” then you would access the DAC field in a Generic Inquiry or Report Designer using
“SQLQuery.CustomersWithSalesThisYear.MyCustomerField”.
Display Name (field) :: Every DAC field in Acumatica has a display name. In this column you would have the opportunity to determine what that Display Name is for each field in the DAC that gets created by your SQL Query. In a Generic Inquiry, you would still have the opportunity to override this display name with the Caption field, just like you can do for any other DAC field.
Display Format (field) :: Already in Acumatica, when you drop a DAC field on a Generic Inquiry, the Generic Inquiry automatically somehow knows how to display the field (text, date, number, checkbox, etc). It also affects the filter options you have when you click on a column heading in a Generic Inquiry. You can use Schema Field on the Generic Inquiry (SM208000) screen to use the display format from another DAC field (click here for an example of Date vs. Date/Time). The idea with this Display Format field is to allow you to set the display format on the resulting DAC field so it behaves the same way on a Generic Inquiry. I’m not sure what the list of values should be here. DAC fields have two things:
First, PXDBString (10), PXDBDate, PXDBShort, etc.
Second, PX.Data.BQL.BqlGuid.Field, PX.Data.BQL.BqlString.Field, PX.Data.BQL.BqlBool.Field, PX.Data.BQL.BqlDateTime.Field, PX.Data.BQL.BqlDecimal.Field, etc.
I’m not sure which is more relevant, but in my opinion the closer the list of values is to real DAC field options the better. Some DAC fields (eg. APInvoice.RefNbr) even know how to display a lookup window when they are used as a Parameter on a Generic Inquiry (we might need a separate field to handle this, not sure).
Width (field) :: I’ve also noticed when you drop a DAC field on a Generic Inquiry that the Generic Inquiry knows the default width for each field. I’d love to be able to define that DAC field property as well.
Cast Your Vote
If you think this is a cool idea, I need your help.
Acumatica looks at votes and comments on feedback.acumatica.com to determine what to add next into the product.
Click here to cast your vote and add a comment on my feedback.acumatica.com idea