Using SQL Views with Generic Inquiries and Reports in Acumatica

Generic Inquiries are one of Acumatica's most powerful features. They allow users to create custom queries and reports without extensive programming skills. But what happens when your query becomes too complex for the standard GI builder? Or when performance suffers due to large datasets and multiple joins? That's where SQL Views come in.

The best part? Once you create a SQL View, you can use it in both Generic Inquiries and Acumatica Reports—giving you a single source of truth for complex data across your entire reporting ecosystem.

When to Use SQL Views Instead of Generic Inquiries

Generic Inquiries are great for most scenarios. However, there are specific situations where a SQL View is a better choice:

• Complex Business Logic: When your condition cannot be expressed using the GI's Condition tab. For example, counting how many fields are filled in a record across multiple columns
• SELECT DISTINCT Functionality: Generic Inquiries do not support SELECT DISTINCT. A SQL View can handle this requirement
• Performance Issues: When your GI has many joins and is slowing down the system
• Large Datasets: When you're working with high-volume data and need to push the processing to the database layer for better performance
• Complex Aggregations: When you need to perform calculations that are cumbersome in GIs

Why SQL Views Are a Unified Solution

Acumatica's reporting and Generic Inquiry systems both rely on DACs (Data Access Classes). When you create a SQL View and map it to a DAC, that DAC becomes available in both Generic Inquiries and the Report Designer. This means:

• One View, Multiple Use Cases: Create the complex logic once in a SQL View, then use it across your entire reporting ecosystem
• Consistent Data: Both GIs and reports pull from the same data source, ensuring consistency
• Reduced Maintenance: Update the view in one place, and all dependent GIs and reports are automatically updated
• Better Performance: The complex processing happens at the database level, benefiting both GIs and reports

Step-by-Step Guide: Creating and Using SQL Views

Step 1: Create the SQL View

Start by creating your SQL View. Here's a sample that summarizes sales order data for better reporting performance:

CREATE OR ALTER VIEW dbo.MySalesOrderInfo AS
SELECT 
    OrderType,
    OrderNbr,
    COUNT(*) AS '# of lines',
    SUM(ExtPrice) AS 'Total Amount',
    MIN(ExtPrice) AS 'Minimum Amount',
    MAX(ExtPrice) AS 'Maximum Amount'
FROM SOLine
GROUP BY OrderType, OrderNbr

Here's another example that counts how many fields are filled for each contact—a complex condition that's difficult to achieve in a GI directly:

CREATE VIEW ContactsWithMinFields AS
SELECT 
    ContactID, 
    Field1, Field2, Field3, Field4, Field5,
    Field6, Field7, Field8, Field9,
    (CASE WHEN Field1 IS NOT NULL THEN 1 ELSE 0 END + 
     CASE WHEN Field2 IS NOT NULL THEN 1 ELSE 0 END + 
     CASE WHEN Field3 IS NOT NULL THEN 1 ELSE 0 END + 
     CASE WHEN Field4 IS NOT NULL THEN 1 ELSE 0 END + 
     CASE WHEN Field5 IS NOT NULL THEN 1 ELSE 0 END + 
     CASE WHEN Field6 IS NOT NULL THEN 1 ELSE 0 END + 
     CASE WHEN Field7 IS NOT NULL THEN 1 ELSE 0 END + 
     CASE WHEN Field8 IS NOT NULL THEN 1 ELSE 0 END + 
     CASE WHEN Field9 IS NOT NULL THEN 1 ELSE 0 END) AS FieldsFilled
FROM Contact
WHERE (CASE WHEN Field1 IS NOT NULL THEN 1 ELSE 0 END + 
       CASE WHEN Field2 IS NOT NULL THEN 1 ELSE 0 END + 
       CASE WHEN Field3 IS NOT NULL THEN 1 ELSE 0 END + 
       CASE WHEN Field4 IS NOT NULL THEN 1 ELSE 0 END + 
       CASE WHEN Field5 IS NOT NULL THEN 1 ELSE 0 END + 
       CASE WHEN Field6 IS NOT NULL THEN 1 ELSE 0 END + 
       CASE WHEN Field7 IS NOT NULL THEN 1 ELSE 0 END + 
       CASE WHEN Field8 IS NOT NULL THEN 1 ELSE 0 END + 
       CASE WHEN Field9 IS NOT NULL THEN 1 ELSE 0 END) >= 3

Step 2: Add the SQL View to Your Customization Project

1. Open the Customization Project Editor
2. Add the SQL View script to the Database Scripts section. This ensures the view is created when the customization is published
3. Publish the customization to create the view in the database

Step 3: Create the DAC

After publishing the view, create a Data Access Class (DAC) that maps to your SQL View:

1. In the Customization Project Editor, click the plus (+) button to add a new object
2. Select New DAC as the File Template
3. Enter the name of your View as the class name (must match the view name exactly)
4. Check the box to Generate Members from Database. This will automatically create a code file that maps to all the fields in your view

using System;
using PX.Data;

namespace MyCustomViews
{
    [Serializable]
    [PXCacheName("MySalesOrderInfo")]
    public class MySalesOrderInfo : IBqlTable
    {
        // Fields will be auto-generated by Acumatica
        // when you check "Generate Members from Database"
    }
}

Step 4: Publish and Use in Generic Inquiries

After publishing the customization, your SQL View is now available as a DAC and can be used in Generic Inquiries:

1. Open the Generic Inquiry (SM208000) form
2. Create a new inquiry and select your new DAC as a data source
3. Add the fields you need to the Results Grid
4. Save and view your inquiry

Step 5: Use in Acumatica Reports

The same DAC is now available in the Report Designer:

1. Open the Report Designer (SM208060)
2. Create a new report or edit an existing one
3. In the Data Source section, select your new DAC
4. Add the fields to your report design
5. Save and preview your report

Why This Approach Works

Common Use Cases for SQL Views

1. Count-Based Conditions

When you need to check if a certain number of fields are filled (like "at least 3 of 9 fields must have values"), this logic is difficult in GIs but simple in SQL Views.

2. SELECT DISTINCT

Generic Inquiries don't support SELECT DISTINCT. A SQL View solves this problem.

3. Complex Aggregations

When you need to calculate totals, averages, or other aggregations across multiple tables, SQL Views can handle these efficiently.

4. Nested Generic Inquiries

For very complex queries, you can use Generic Inquiries as Data Sources for other Generic Inquiries, but SQL Views often provide better performance for the most complex scenarios.

Important Considerations

• Direct Database Access: Direct access bypasses security measures and increases data corruption risk. Use PXDatabase class for safe interactions
• Acumatica Recommended Approach: While SQL Views are not Acumatica's suggested approach for all scenarios, they are acceptable for performance optimization
• Security: Ensure your SQL Views respect Acumatica's security model. Role-based access should still be enforced
• Maintenance: SQL Views need to be maintained alongside your customization projects. Document them clearly

Performance Optimization Best Practices

Conclusion

SQL Views are a powerful enhancement to Acumatica's Generic Inquiry and Reporting capabilities. They allow you to:

• Handle complex business logic that's difficult in GIs
• Improve performance for large datasets
• Perform SELECT DISTINCT operations
• Create complex aggregations efficiently
• Use the same view in both Generic Inquiries and Reports

By leveraging SQL Views, you create a unified data source that works across Acumatica's entire reporting ecosystem, ensuring consistency and reducing maintenance overhead.