Saturday, 19 September 2015

Stored Procedures In Acumatica Reports

As you may know, by default Acumatica does not support stored procedures.

The reason of this is hidden in multi-tenancy.
Acumatica able to host more than one tenant in the same database. To do this, In each table it has a CompanyID column, which is a tenant ID. User from one tenant must not be able to get access to date of other tenant.
So to make sure that Acumatica getting data from the right tenant and this date is secured, Acumatica platform automatically adds the special restriction by CompanyID to every select statement ( ... where [table].CompanyID in { 1, 2 } )

If you are using stored procedures than platform is not able to make sure that the result date is getting from the correct company, so Acumatica does not support stored procedures for select data at all. So to make everything secure, Acumatica uses only normal tables and views to generate SQL statements (including reports). 

But to make it adjustable we have several options how we can extend this approach:

  1. 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.
  2. 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).
  3. You are able to use functions and variables in Acumatica Report Designer, so you report will be little more intellectual.
  4. You are able to add custom C# functions in the Acumatica Report Designer. You can read about this ability on our KB portal.
  5. You are able to use Acumatica Extensibility Framework to make some adjustments and calculation on level of DAC classes and attributes.
Here I want to speak about option #2.

Table-Valued Functions
On the Stack Overflow you can find quite simple example how to call functions form views. You need to use special table-valued functions for this.

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
Using Table-valued function for generating Balance Sheet Report.
To do this we need to follow these steps:
  1. Create a customization project in Acumatica.
  2. In the Sql Server we need to prepare function and wrap it into the view.
  3. From Acumatica we should create new Extension Library and open it with VS.
  4. From Acumatica Web Forms Designer we can generate DAC based on our view. Do not forget to compile project. 
  5. Now we need to publish everything before creating a report.
  6. After publishing you can open Acumatica Report Designer and create new report.
  7. Now you can use your new DAC from the report designer. You can provide some filtering conditions, based on some parameters, if you need.
  8. Save your report to server and add it to sitemap.
  9. Pack everything into the customization project (DLL, SQL script, report, site map entry).
  10. Now you can test it.
Profit!

3 comments:

Woody Gu said...

Hi I have a question:

If I modify the defination in Acumatica code, after publish, I won't see the SQL VIEW /SP from web service defination anymore. So how can I modify SQL View/SP and let DAC pick up the changes?

Thanks!

Woody Gu said...

Right now, all I can do is change the view/sp name and create NEW DAC for that and then modify the report to see the changes.

Sergey Marenich said...

Hi Woody,

Could you advise please, what do you you need to change (rename) and why?
In general you do not need to rename any standard Acumatica classes.