Get Data from Stored Procedures In Acumatica

19 September 2015 by Sergey Marenich20 comments

Hi There,

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.
Now lets s 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.
    1. From Acumatica we should create new Extension Library and open it with VS.
    2. From Acumatica Web Forms Designer we can generate DAC based on our view. Do not forget to compile project.
    3. Now we need to publish everything before creating a report.
    4. After publishing you can open Acumatica Report Designer and create new report.
    5. Now you can use your new DAC from the report designer. You can provide some filtering conditions, based on some parameters, if you need.
      Acumatica Report Designer
    6. Save your report to server and add it to sitemap.
    7. Pack everything into the customization project (DLL, SQL script, report, site map entry).
    8. Now you can test it.
Acumatica Report Using Stored Procedure
Profit!

20 Replies to “Get Data from Stored Procedures In Acumatica”

  1. Hi Sergey,

    DisplayPlan is a unbound formula (concatenate) field for PlanCD & RevisionNo in our Plan DAC
    At the time of import ITEM CLASS, we are passing DisplayPlan column as {PlanCD}-{RevisionNo} in the excel sheet
    This is giving me error : Plan ID can not be empty.

    Note: Generally, Import data in other screens, If i am passing this field to form view control, then it imports successfully.
    But if i am passing this field to the grid column, then it is giving the above error.

    Please suggest.

    Thanks.

    Reply

  2. Hi Sergey,

    Can we show multiple columns in the report designer lookup parameter ? (without using custom filter DAC)
    View name in the Report Parameter : Report.GetFieldSchema( field )
    I can see only one column in report for this case.
    Please suggest.

    Thank you.

    Reply

    1. Hi Gaurav,
      Unfortunately no. The lookup windows are defined the same way as selectors in Acumatica. And there is no way to configure it from the UI.
      The best way is to have a DAC with proper selector

      Reply

  3. Hi Sergey,

    Can we hide pencil icon (edit icon) from report ?
    (Which we use edit the report parameters at runtime)

    Thank you..

    Reply

    1. Hi Riyaz, version of report designer is growing together with Acumatica. So every new release of Acumatica we have a new version of designer.

      Reply

  5. Hi Sergey,

    What is best practice to create a report using complex queries (Multiple joins)

    1. By creating Database Views and then DAC as you suggested above
    or
    2. Using PXProjection

    Please Explain..

    Thanks & Regards,
    Gaurav Katiyar

    Reply

    1. Acumatica does not have and use any of the views. The reason is that you cannot validate that view during the compilation time. We use projections for reports.
      However creation of the view is easier, so it is your choice.

      Reply

  6. Hello Sergey, can you please suggest me, how to pass value dynamically to the view. We are trying to use this view in GI and then using OData to import into another application.

    CREATE VIEW StatisticksView
    AS
    SELECT * FROM sp_GetBalanceSheet(‘201301’) AS Stat
    GO

    Reply

    1. Hi Nagaraj,
      I think you can’t really pass parameters to the view, but what you can is add a filtering conditions on top of it.
      CREATE VIEW StatisticksView
      AS
      SELECT * FROM sp_GetBalanceSheet() AS Stat
      GO
      Select * from StatisticksView where PeriodID = ‘201301’
      Hope it helps.

      Reply

  7. Thank you!! This worked perfectly in my dev environment because I am using a local instance of SQL Server. How can I create a user defined function and a view on a SAAS implementation?

    Reply

    1. You can include it as a script into the customization project. Script will be executed on SaaS DB as soon as you publish the customization.

      Reply

  8. 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.

    Reply

  9. 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.

    Reply

  10. 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!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *