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.