Why Reporting is Better with SQL in Acumatica

Thank you to everyone who attended the Acumatica Summer Workshop and thank you to Amiee Keenan for organizing it.

My Why Reporting is Better with SQL in Acumatica session covered an introduction to SQL, why I think SQL is a tool for Business Users (not just Developers), and practical examples of SQL in action.

When I was asked during the session when to use SQL instead of Generic Inquiries, I fumbled my response. So I’d like to give a better response here.

SQL is an enhancement to Generic Inquiries, not a replacement for Generic Inquiries.

Generic Inquiries are awesome and I love Generic Inquiries. I even built a video training course on Generic Inquiries (click here) and I’ll be teaching the content from that course in person here in Columbus, Ohio this Fall 2024 on Tuesday, September 10 and Wednesday, September 11 (click here).

But sometimes you need to do more than what’s possible with Generic Inquiries. This doesn’t happen as frequently now that Generic Inquiries can be used as Data Sources for other Generic Inquiries in Acumatica 2024 R1, but it does still happen.

Ultimately, a Generic Inquiry generates a SQL query when you run it, it just gives you the ability to construct that SQL query graphically using the Generic Inquiry (SM208000) screen.

Most of the time the Generic Inquiry (SM208000) screen is adequate on its own, but sometimes you run into situations where you need to do more. That’s where SQL comes in.

You can put advanced SQL reporting logic into something called a SQL View. The SQL View can then be used in a Generic Inquiry as another Data Source on the TABLES / DATA SOURCES tab.

Click here for details on how to make a SQL View available to a Generic Inquiry

Since a SQL View gets used by a Generic Inquiry, that’s why I consider SQL an enhancement to Generic Inquiries, not a replacement for Generic Inquiries.

You can also use a SQL View in Report Designer, on a Business Event, or on a Dashboard.

There’s also another reason to use SQL. When you’re working with a large amount of data or doing a lot of calculations in Generic Inquiries, Report Designer, or Dashboards, things can get slow.

You can perform those calculations in SQL by combining all of your SQL code into a SQL Stored Procedure.

Then you can schedule that SQL Stored Procedure to run on a schedule (eg. nightly) so all of those calculations get calculated and stored so they don’t have to be calculated every time you run the report.

That means the data isn’t live anymore (it’s as old as the last schedule that ran), but, in my experience, when you get into a situation where a lot of calculations are needed, you don’t need up-to-the-second live data.

To schedule a SQL Stored Procedure to run on a schedule, you do need to use my AugSQL (click here) product, but we priced AugSQL at a super low price point to make it something that every company can afford.

I’ve used a SQL Stored Procedure to build a replenishment solution for a client that runs hourly using AugSQL (click here). It used to take several minutes for their custom replenishment report to run. Now it runs in seconds because the data gets calculated by the SQL Stored Procedure at the top of every hour.

Let’s finish off with the SQL code that I showed during Why Reporting is Better with SQL in Acumatica session. As promised, here are all of the examples that I showed.

Note: Any line that starts with two dashes is treated as a comment in SQL so it doesn’t do anything.

-- Your First SQL Query
SELECT TOP 50 *
FROM SOLine

-- What "Tenants" (called CompanyID) are in your Acumatica Database
SELECT *
FROM Company

-- See Countries that come in the Template Tenant (CompanyID 1)
SELECT *
FROM Country

-- You pretty much always want to filter on CompanyID
SELECT TOP 50 *
FROM SOLine
WHERE CompanyID=2

-- Find Sales Order Lines where the Quantity is greater than 50
SELECT TOP 50 *
FROM SOLine
WHERE CompanyID=2 AND OrderQty>50

-- Find Sales Order Lines from SO004988 or SO003339 where the Quantity is greater than 50
SELECT TOP 50 *
FROM SOLine
WHERE CompanyID=2 AND OrderQty>50 AND
(OrderNbr='SO004988' OR OrderNbr='SO003339')

-- Find Sales Order Lines from a list of Sales Orders
SELECT TOP 50 *
FROM SOLine
WHERE CompanyID=2 AND OrderQty>50 AND
OrderNbr IN ('SO004988', 'SO003339', 'SO006424')

-- Show the Sales Order Lines with the smallest Quantity first
SELECT TOP 50 *
FROM SOLine
WHERE CompanyID=2 AND OrderQty>50 AND
OrderNbr IN ('SO004988', 'SO003339', 'SO006424')
ORDER BY OrderQty

-- Show the Sales Order Lines with the largest Quantity first
SELECT TOP 50 *
FROM SOLine
WHERE CompanyID=2 AND OrderQty>50 AND
OrderNbr IN ('SO004988', 'SO003339', 'SO006424')
ORDER BY OrderQty DESC

-- View all Customers
SELECT *
FROM BAccount
WHERE CompanyID=2 AND Type='CU'

-- View all Customers that start with "a"
SELECT *
FROM BAccount
WHERE CompanyID=2 AND Type='CU' AND AcctName LIKE 'a%'

-- View all Customers that have "LLC" in the name
SELECT *
FROM BAccount
WHERE CompanyID=2 AND Type='CU' AND AcctName LIKE '%LLC%'

-- Back to our original query
SELECT TOP 50 *
FROM SOLine
WHERE CompanyID=2

-- List the specific columns that we want to see
SELECT TOP 50 OrderType,OrderNbr,OrderQty,ExtPrice
FROM SOLine
WHERE CompanyID=2

-- Group By Sales Order to show only one row per Sales Order
SELECT TOP 50 OrderType,OrderNbr
FROM SOLine
WHERE CompanyID=2
GROUP BY OrderType,OrderNbr

-- Show how many lines are on each Sales Order
SELECT TOP 50 OrderType,OrderNbr,COUNT(*) '# of lines'
FROM SOLine
WHERE CompanyID=2
GROUP BY OrderType,OrderNbr

-- Sort by the Sales Orders with the most lines first
SELECT TOP 50 OrderType,OrderNbr,COUNT(*) '# of lines'
FROM SOLine
WHERE CompanyID=2
GROUP BY OrderType,OrderNbr
ORDER BY COUNT(*) DESC

-- Add the total $ of all lines on each Sales Order
SELECT TOP 50 OrderType,OrderNbr,COUNT(*) '# of lines',
SUM(ExtPrice) 'Total Amount'
FROM SOLine
WHERE CompanyID=2
GROUP BY OrderType,OrderNbr
ORDER BY COUNT(*) DESC

-- Add the smallest (MIN) and largest (MAX) $ line from each Sales Order
SELECT TOP 50 OrderType,OrderNbr,COUNT(*) '# of lines',
SUM(ExtPrice) 'Total Amount', MIN(ExtPrice) 'Minimum Amount',
MAX(ExtPrice) 'Maximum Amount'
FROM SOLine
WHERE CompanyID=2
GROUP BY OrderType,OrderNbr
ORDER BY COUNT(*) DESC

-- Turn our SQL Query into a SQL View
CREATE OR ALTER VIEW dbo.MySalesOrderInfo AS
SELECT OrderType,OrderNbr,COUNT(*) '# of lines',
SUM(ExtPrice) 'Total Amount', MIN(ExtPrice) 'Minimum Amount',
MAX(ExtPrice) 'Maximum Amount'
FROM SOLine
WHERE CompanyID=2
GROUP BY OrderType,OrderNbr

You can add the SQL View to a Data Access Class (DAC) in Acumatica so it can be used in a Generic Inquiry, in Report Designer, on a Dashboard, etc. by following the steps outlined in this post (click here).

-- Turn our SQL Query into a SQL Stored Procedure
CREATE OR ALTER PROCEDURE dbo.PopulateMySalesOrderInfo AS
IF EXISTS(SELECT * FROM sys.tables WHERE name='PopulateMySalesOrderInfoTable')
   DROP TABLE dbo.PopulateMySalesOrderInfoTable; 

SELECT OrderType,OrderNbr,COUNT(*) 'NbrOfLines',
SUM(ExtPrice) 'TotalAmount', MIN(ExtPrice) 'MinimumAmount',
MAX(ExtPrice) 'MaximumAmount'
INTO dbo.PopulateMySalesOrderInfoTable
FROM SOLine
WHERE CompanyID=2
GROUP BY OrderType,OrderNbr

-- The SQL Stored Procedure can then be executed like this
EXEC PopulateMySalesOrderInfo

You can execute the Stored Procedure automatically on a schedule using AugSQL (click here).

-- After executing the SQL Stored Procedure, you can see the data that got stored
SELECT *
FROM PopulateMySalesOrderInfoTable

-- Turn the data that got stored into a SQL View like this
CREATE OR ALTER VIEW dbo.MySalesOrderInfo AS
SELECT OrderType, OrderNbr, NbrOfLines, TotalAmount, MinimumAmount, MaximumAmount
FROM PopulateMySalesOrderInfoTable

You can add the SQL View to a Data Access Class (DAC) in Acumatica so it can be used in a Generic Inquiry, in Report Designer, on a Dashboard, etc. by following the steps outlined in this post (click here).