Monday, June 18, 2012

Adding View to Report.

Hi Everyone,

Adding tailor made views to Crystal report seemed to be an easy task. Same for Acumatica. Native Report Designer will only "see" your view if it was properly published.

For example let's modify a Batch Register report from General Ledger. It has summary information, but I want to see the number of transaction lines per each batch. Well I am sure it is possible to do it just inside a report without adding any view, but just for training purpose lets do it our way.

Here are the steps to add tailor made view to a standard report:

1. Create a view in an SQL, here is nothing special. Let's do it using TSQL or Management Studio. What I added is:
create view GLCount as
select CompanyID, BatchNbr, COUNT(*) as NbrTrans
from GLTran
group by CompanyID, BatchNbr


2. Next step is to create a Data Access Class in Acumatica App that will represent that View for our report. System Management->Customization Management->Code Editor, select any project you with to add that DAC. If you have not started customizing your Acumatica yet, just create a new customization project and save it to database before this step. I selected MAIN project, then click New File:
Next choose IBqlTable type and in Class field type the name for our view - GLCount, check "Generate Members from Database" checkbox:
Then click OK.
It creates a DAC for us but we have to do 2 amendments to it before it can be placed inside a report.

A. We have to assign a namespace to the DAC. Since I work with GL, let me add the General ledger name space to my DAC.
B. We should indicate that BatchNbr is a Key field. This is simply because report should know the primary keys for the View. Since Company ID is obviousely a key, the BatchNbr remains the only one we should indicate manually.

After amendments the file should look like this:


Once all saved, have to publish the customization. It can be done via Project List screen.



3. Now we can amend our report. Before changes Batch Register report looks like below:








I get back to parameters and pressed Edit Version to open the report in designer:


 Once you open Report Designer, go to File->Build Schema, login to the database you are working with and here is our newly added View:



Here we go. The rest is just the same we do for standard Tables/DACs.
Set Relations, then add a field to report from our view. Here is what I got at the end:


And just to make sure that it does what it is, lets navigate to that batch with 12 lines:


Cheers,

Sergey.

11 comments:

  1. How about adding a view using Acumatica 4.1? What are the steps? Thanks.

    ReplyDelete
    Replies
    1. Hi, exact same steps. System->Customization->Custom Code screen.

      Delete
  2. Hi Sergey,

    I created a view. And inserted in Report Designer. The report displays erronous results. It display only the first item of the Sales Order. We are using Acumatica 4.1, RapidByte sample data. Below is the script for the view.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CREATE VIEW ViewSO]'))
    drop view [dbo].[CREATE VIEW ViewSO]
    GO

    CREATE VIEW ViewSO AS

    SELECT SO.OrderNbr, SO.OrderDate, SL.InventoryID, Product = I.Descr, SL.OrderQty, SL.UnitPrice
    FROM SOOrder SO WITH(NOLOCK)
    INNER JOIN SOLine SL WITH(NOLOCK) ON SO.OrderType = SL.OrderType AND SO.OrderNbr = SL.OrderNbr
    AND SO.CompanyID = SL.CompanyID
    INNER JOIN InventoryItem I WITH(NOLOCK) ON SL.InventoryID = I.InventoryID
    AND SL.CompanyID = I.CompanyID
    WHERE SO.OrderType = 'SO' AND SO.[Status] = 'N'

    GO

    ReplyDelete
    Replies
    1. Probably you forgot to make view fields having keys. Need to have unique key for report.

      In general for support please use portal.

      Delete
  3. This is how it look like in Acumatica: It repeats the first record per Order No.

    OrderNbr OrderDate InventoryID Product OrderQty UnitPrice

    000139 2011-01-01 00:00:00 270 SO item #151-1 1.000000 20.000000
    000139 2011-01-01 00:00:00 270 SO item #151-1 1.000000 20.000000
    000139 2011-01-01 00:00:00 270 SO item #151-1 1.000000 20.000000
    000139 2011-01-01 00:00:00 270 SO item #151-1 1.000000 20.000000
    000139 2011-01-01 00:00:00 270 SO item #151-1 1.000000 20.000000
    000139 2011-01-01 00:00:00 270 SO item #151-1 1.000000 20.000000
    000139 2011-01-01 00:00:00 270 SO item #151-1 1.000000 20.000000
    000139 2011-01-01 00:00:00 270 SO item #151-1 1.000000 20.000000
    000139 2011-01-01 00:00:00 270 SO item #151-1 1.000000 20.000000



    This is how it looks like if i run the trace in SQL Mgmt Studio:

    OrderNbr OrderDate InventoryID Product OrderQty UnitPrice

    000139 2011-01-01 00:00:00 270 SO item #151-1 1.000000 20.000000
    000139 2011-01-01 00:00:00 271 SO item #151-2 1.000000 21.000000
    000139 2011-01-01 00:00:00 270 SO item #151-1 3.000000 20.000000
    000139 2011-01-01 00:00:00 271 SO item #151-2 3.000000 21.000000
    000139 2011-01-01 00:00:00 270 SO item #151-1 6.000000 20.000000
    000139 2011-01-01 00:00:00 271 SO item #151-2 6.000000 21.000000
    000139 2011-01-01 00:00:00 270 SO item #151-1 12.000000 20.000000
    000139 2011-01-01 00:00:00 271 SO item #151-2 12.000000 21.000000
    000139 2011-01-01 00:00:00 272 SO item #151-3 12.000000 22.000000

    ReplyDelete
  4. P.S.

    The view is working if i used it Generic Inquiry. Thanks.

    ReplyDelete
    Replies
    1. It can also be report design. Please use support portal, team will guide you.

      Delete
  5. Doing these steps exactly in Acumatica 4.0 has a Could not load type 'GLCount' error when attempting create a Generic Inquiry or a Report in the Report Designer. Any ideas? (I realize this is an old topic; sorry).

    ReplyDelete
    Replies
    1. Could you please verify you are using published version. Is it 4.0 4.1 or 4.2? What build? GL Count is a added DAC, you need to add DAC to the system then restart the IIS. Then it will appear in the list of DACs. If not let me know.

      Delete
    2. Version 4.00.1573. I have followed the steps exactly in your posting and the DAC appears in the list in both the Report Designer and the Generic Inquiry. The "Could not load type 'GLCount'" error comes up when I either try to move the DAC in the Tables tab in the Schema Builder of the Report Designer, or when I attempt to add the table in the Table Name column under the Tables tab in Generic Inquiry. Thanks!

      Delete
    3. Most probably its a typo in your class declaration, suggest if you send your DB and code to our support for validation.

      Delete