Creating a Tabular Report in Report Designer

Tabular Reports for Acumatica

From the Acumatica S130 course, Data Retrieval and Analysis, you learn that “a tabular report is a report that displays information from the database in a format consisting of multiple columns and rows.” You can use a tabular report, for example, to see a breakdown of your sales by Customer and Inventory Item Class. In this case, the rows would be Customer ID, the columns would be Inventory Item Classes, and the cell at the intersection of each row and column would be the total sales amount of the Class by Customer.

The advantage of using a Tabular report approach versus the traditional format in Report Designer is that the Tabular report will dynamically add the necessary columns for the Item Class included in the report, so you don’t have to hard code every Item Class possible value.

Here’s how:

A quick summary:

  1. The key settings are TabularFreeze (in pixels) and TabularReport (True)
  2. Tabular reports render dynamic columns for the data field defined as a top-level Report Group
  3. The additional Report Groups are used as Rows in the report.
  4. A select field (e.g. Sales Amount) is used in the Group with a SUM formula.

 

Let’s get technical

Compare the results when run the report for two different date ranges.

A short range includes seven Item Classes:

Acumatica Tabular Reports

A longer date range includes sixteen Item Classes:

Acumatica Tabular Reports

Selecting the Data

In the Report Designer’s Schema Builder, select these five Data Access Classes (DACs):

Acumatica Tabular Reports

The relationships between these DACs is demonstrated in this SQL Query Designer schema:

Acumatica Tabular Reports

The relationships in Report Designer are:

SOInvoice > ARRegister:

Acumatica Tabular Reports

ARRegister to ARTran:

Acumatica Tabular Reports

ARTran to InventoryItem:

Acumatica Tabular Reports

InventoryItem to INItemClass:

Acumatica Tabular Reports

Parameters

We will add two date parameters to filter the results by date range:

Acumatica Tabular Reports

Next, the filter is set to use the ARRegister document date.

Acumatica Tabular Reports

Report Groups

The top-level group is always used for the report-level dynamic columns, and the second (and subsequent) groups are used as rows in the report.

To calculate the total sum of sales of each Item Class per Customer, we will group the report by Item Class and Customer.

Acumatica Tabular Reports

For the Item Class:

Acumatica Tabular Reports

For the Customer Class:

Acumatica Tabular Reports

 

Tabular report property settings:

On the Properties tab, select the report1 Report object from the drop-down list to select the report form, and specify the following settings:

  • TabularReport: True
  • TabularFreeze: 128px

Based on the value of TabularFreeze, a red line appears on the report layout.  The line passes through all the group headers, the group footers, and the detail sections of the tabular report. Anything that is beyond this red line will appear as new columns instead of new rows.

Acumatica Tabular Reports

We will also set the page width:

Acumatica Tabular Reports

Once the report layout is updated based on the TabularFreeze settings, we proceed to add TextBox controls for the Item Class header and the sum of sales, using this expression:

 =SUM(IIF([ARTran.DrCr]=’C’,1,-1)* [ARTran.CuryTranAmt])

 Acumatica Tabular Reports

Save the report, set it in the Site Map and a Workspace, and you can select a date range and run it:

Acumatica Tabular Reports

 

Give these tabular reports a try in Acumatica.  You will save yourself quite a bit of time.  By using parameters, grouping, and report properties, the resulting report is sleek and easy-to-read, and will be available in the Report Designer for future use. If you have further questions, or need assistance setting up your Acumatica reports, contact our team at support@crestwood.com.

 

Leave a Reply

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