As you may know, by default Acumatica does not support stored procedures.
But to make it adjustable we have several options how we can extend this approach:
- Use SQL server Views. – this is normal way if you need to make some calculations on server side. Sometimes filtering, grouping and calculation operation on server side much faster than on Acumatica.
- Wrap up table-valued functions into the View. In the table-valued functions you can do as many programming things as you need (including cursors, loops, variables and so on).
- You are able to use functions and variables in Acumatica Report Designer, so you report will be little more intellectual.
- You are able to add custom C# functions in the Acumatica Report Designer. You can read about this ability on our KB portal.
- You are able to use Acumatica Extensibility Framework to make some adjustments and calculation on level of DAC classes and attributes.
CREATE FUNCTION dbo.MyFunction() RETURNS TABLE AS RETURN ( SELECT 1 AS id UNION ALL SELECT 2 ) GO
CREATE VIEW MyView AS SELECT * FROM MyFunction() as res GO
SELECT * FROM MyView
- Create a customization project in Acumatica.
- In the Sql Server we need to prepare function and wrap it into the view.
- From Acumatica we should create new Extension Library and open it with VS.
- From Acumatica Web Forms Designer we can generate DAC based on our view. Do not forget to compile project.
- Now we need to publish everything before creating a report.
- After publishing you can open Acumatica Report Designer and create new report.
- Now you can use your new DAC from the report designer. You can provide some filtering conditions, based on some parameters, if you need.
- Save your report to server and add it to sitemap.
- Pack everything into the customization project (DLL, SQL script, report, site map entry).
- Now you can test it.