Lesson 7: Using Parameters and Filters

This lesson explains how you can use parameters and filters in reports.

Parameters and Filters: General Information

In the Acumatica Report Designer, you can define parameters for the report you are designing, and users will specify the values for these parameters before they run the resulting report. For the report, you also can specify filters that can be used both in conjunction with parameters to limit the data based on the specified value and separately to always limit the data.

Learning Objectives

In this chapter, you will learn how to do the following in the Report Designer:

Specify parameters in a report
Set up filters in a report

Applicable Scenarios

You may want to use parameters and filters in reports you design in the Acumatica Report Designer in the following circumstances:

You are responsible for the customization of Acumatica ERP in your company, including developing and modifying reports to give users the information they need to do their jobs.
You want to modify an existing report to filter data, or add, modify, or define parameters that users can select to view data that meets their current needs.

Parameters

Report parameters are variables that the user specifies on the report form before running the report. You can use parameters to share values between two or more reports, or in expressions and formulas to calculate values for multiple fields within the same report. Based on the parameter, the report engine creates a variable within the report, which can be referred to as a database field can. When a parameter is referred to in code, it starts with the @ symbol. You can specify parameters for a report on the Parameters tab of the Schema Builder. You add a parameter by clicking the Add button (lower le) and specifying the following settings.

Parameter                 Description

Name (required)           The name that you assign to the parameter, which will be used internally.

Data Type (required)      The data type of the parameter. You can select one of the following types from the
                          drop-down list: Boolean, Date time, Float, Integer, and String.

Prompt                    The title that is displayed to the le of the parameter on the Report Parameters tab of
                          the report form in Acumatica ERP. If this value is not specified, the prompt and the box
                          for the user to specify the parameter will not be displayed, and a user will not be able
                          to enter or select any value; in this case, you can use this hidden parameter internally.

Lesson 7: Using Parameters and Filters | 83

Parameter Description

Default Value The value of the parameter when the user opens the report form; this value is used by default if a user does not specify another value. While designing and debugging a re- port in the Report Designer, you can experiment with specifying different values. You can use expressions and formulas to define parameters' default values. In the final ver- sion of the report, in the Default Value box, you specify the preferred value or leave the setting empty.

Input Mask The input mask to be used for entering the data for the parameter. For example, you can specify an input mask for a telephone number. You can specify a formula in the Ex- pression Editor to define the input mask.

View Name The lookup window that will open to help the user running the report select the para- meter. Usually, in the View Name box, the Report.GetFieldSchema(DAC.field) function is selected. This function returns a detailed description of the lookup window depending upon the specified DAC and field. The lookup window contains all the possible values of the parameter from which the user running the report can select the necessary val- ue.

                           You can also use any field of any existing outside DAC if it has an attribute
                           with appropriate lookup columns for the report parameter being adjust-
                           ed. You can create a special DAC with the needed lookup fields if you have
                           not found the appropriate field or fields in the existing DACs.

Column Span The column span to be used to display the parameter on the report form. The default column span is 1, indicating that the parameter occupies one column.

Allow Null Values A check box that you select to indicate that the parameter can be null. By default, the check box is cleared, which means that the parameter cannot be null.

Visible A check box that you select to indicate that the parameter will be visible. By default, the check box is selected. If you clear the check box, the parameter will not appear on the Report Parameters tab of the report form and will be used only internally.

Required A check box that you use to specify whether the parameter is required to run the re- port. By default, the check box is cleared, which means that a user can run the report without specifying a value for the parameter. If you select the check box, a user cannot run the report without specifying a value for this parameter. If you click the selected check one more time, it will get to its third option, Required by Condition (the check box is filled with square, instead of check mark). You select this option to let the system calculate whether the parameter is required or not when you open the report form. The system does the calculation based on the parameter config- uration, which is defined by the type and attributes of the corresponding DAC field. For example, suppose that for the BranchID field, it is defined that it becomes re- quired if the Multiple Base Currencies feature is enabled in the system. If you use the BranchID field as a parameter of your report and select the Required by Condition op- tion for it, the system will take the current value from the corresponding attribute of the DAC field.

Available Values A table in which you can specify the values that a user can select in the box of the para- meter on the report form. Lesson 7: Using Parameters and Filters | 84

You delete any parameter from the list on the Parameters tab of the Schema Builder by clicking the parameter in
the le pane and then clicking the Remove button.
Acumatica ERP has the following predefined date-relative parameters:

@Today: The business day.
@WeekStart and @WeekEnd: The start and end of the current week. The start and end of the week are determined based on the default system locale or the locale the user has selected when signing in to Acumatica ERP. System locales are defined on the System Locales (SM200550) form.
@MonthStart and @MonthEnd: The start and end of the current month.
@QuarterStart and @QuarterEnd: The start and end of the current quarter.
@PeriodStart and @PeriodEnd: The start and end of the current financial period. The financial periods are defined on the Financial Year (GL101000) form. For more information about financial periods in Acumatica ERP, see Managing Financial Periods.

@YearStart and @YearEnd: The start and end of the current calendar year.

           All the date-relative parameters use the date (in UTC) of the server used to run the Acumatica ERP
           instance as the business date.

Filters

You can use filters to limit the volume of data selected for the reports, specify more specific criteria for selecting
data from data tables, and remove unnecessary data as a result of the table joining. When you define filters, you
can use parameters. Multiple filters are transformed into one SQL WHERE clause.
You can specify filters in the following ways:

On the Filters tab of the Schema Builder.
In the FilterExp Collection Editor (that is, the FilterExp Collection Editor dialog box). You use the Data > Filters property of the report in the Properties pane of the Properties tab to open the FilterExp Collection Editor. Filter expressions use the data field names and parameters to specify the criteria for data processing. You specify filters as one filter clause or multiple filter clauses. For each clause, you specify the following settings:
Data Field: A data field or parameter that you select from the list.
Condition: The logical operation that applies to the value of the selected property. You select a condition from the list of available conditions.
Value 1 (and Value 2, if needed): A value for the logical condition that is used to filter the data. Depending on the selected property and condition, you enter one value or two values. Each value must conform with the data type of the selected property. You can use parameters for value settings. Not all conditions require you to specify values. For example, values are not necessary for the Is Null and IsNotNull conditions. You can use And and Or operators and parentheses to group clauses into logical expressions. Parentheses can be used in logical statements to define the order of operations. The And and Or operators work on a unit in parentheses as if the unit was a single clause. Any defined expression can be deleted. You can delete an expression in the following ways:
On the Filters tab of the Schema Builder, by clicking the row with the filter you want to delete and pressing the Delete key.
In the FilterExp Collection Editor, by clicking the row with the filter you want to delete and then clicking the Remove button (or pressing the Delete key). Lesson 7: Using Parameters and Filters | 85

Optional Data Filtering

Acumatica ERP gives users the ability to add filters to a generated report. Before a user runs the report, the user can add filters on the Additional Sort and Filters tab of the report form, in the Additional Filtering Conditions table. However, the user cannot view, delete, or modify the filters that are defined in the Report Designer. In the Report Designer, you can manage the list of fields that a user can use to define the sorting and filtering conditions of the published report. When a report is selected, in the Behavior > ViewerFieldsMode property, you can select the following settings:

MergeWithUsed (default): With this setting, on the Additional Sort and Filters tab of the report form, the user can select any of the following as the properties of filtering and sorting conditions: any data fields used in the text boxes, any data fields used in the sorting and filtering conditions of the report, and any data fields listed on the Viewer Fields tab of the Schema Builder.

OnlySpecified: With this setting, on the Additional Sort and Filters tab of the report form, the user can select only the data fields specified on the Viewer Fields tab of the Schema Builder.

                On the Viewer Fields tab of the Schema Builder, you can add all the data fields used in the
                report to the list of available properties. To do that, you click the Load Used Fields button.

Parameters and Filters: To Define a Parameter and a Filter

In the following activity, you will learn how to define parameters for a report and set a filter using the parameters.

           This activity is based on the U100 dataset. If you are using another dataset, or if any system settings
           have been changed in U100, these changes can aﬀect the workflow of the activity and the results of
           the processing. To avoid any issues, restore the U100 dataset to its initial state.

Story

Suppose that you are a technical specialist in your company who is working on simple customizations. A sales manager of your company has requested a report that displays data about vendors. You have oﬀered the predefined Vendor Summary (AP655000) report, but the sales manager has asked you to give users the ability to select a particular vendor class to view data about only the vendors of this class. If no vendor class is specified, the report should display the full list of vendors.

Process Overview

In the Report Designer, you will open the AP6550C2.RPX report, which is a copy of the Vendor Summary (AP655000) report. In the Schema Builder, on the Tables tab, you will look for the field that contains the vendor class; you will need this field to specify the parameter. You will add a new parameter to the Parameters tab. Then, on the Filters tab, you will add clauses to limit the data in the report. With this filter, the report will display only documents that relate to the specified vendor class.

System Preparation

Before you begin performing the steps of this activity, launch the Acumatica ERP website with the U100 dataset preloaded, and sign in as a system administrator Kimberly Gibbs by using the gibbs username and the 123 password. Also, to prepare for use the file that is intended for this activity, do the following: Lesson 7: Using Parameters and Filters | 86

Download the AP6550C2.rpx file.
Open the downloaded file in the Report Designer.
On the Report Designer menu bar, select File > Save To Server, which opens the Save Report on Server dialog box.
In the dialog box, specify the connection string and sign-in credentials of your Acumatica ERP instance, type AP6550C2 as the report name, and click OK. The report is saved on the server.

Step 1: Defining the Parameter for the Report To define the parameter for the vendor class in the AP6550C2 report, do the following:

In the Report Designer, make sure that the AP6550C2 report (which you have saved to the server) is open.
On the Report Designer menu bar, click File > Build Schema to open the Schema Builder.
On the Parameters tab, click the Add button. A new parameter is added to the list of parameters.
In the Name box, enter Class as the parameter name.
In the Data Type box, make sure that String is selected.
In the Prompt box, enter Vendor Class as the prompt for the parameter.
In the Default Value box, enter Product as the default value for the parameter.
In the Column Span box, set the value to 2.
Select the Allow Null Values and Visible check boxes. 10.Make sure that the Required check box is cleared. 11.In the View Name box, click the More button. In the Expression Editor, which opens, enter the formula used to retrieve data for the parameter from the Vendor data access class and the VendorClassID data field as follows: =Report.GetFieldSchema('Vendor.VendorClassID'). 12.Click Validate to validate the added formula. 13.Click OK to close the Expression Editor. 14.In the Schema Builder, click the Apply button. The Class parameter on the Parameters tab of the Schema Builder is shown in the following screenshot. Lesson 7: Using Parameters and Filters | 87
```
    Figure: Class parameter on the Parameters tab

 15.Click OK to save the parameters defined for the report and close the Schema Builder.
 16.On the Report Designer window toolbar, click Save.
```

Step 2: Defining the Filter To define the filter for the vendor class, while you are still working with the AP6550C2 report in the Report Designer, do the following:

On the Report Designer menu bar, click File > Build Schema to open the Schema Builder.
On the Filters tab, in the table, add single braces to bracket the existing expression.
Add two filter clauses. The first clause uses the @Class parameter specified in this report (which you can find listed on the Parameters tab). The clause compares the value in the Vendor.VendorClassID field with the value of the vendor class that a user will specify before running the report. Specify the following settings in the first row of the table:

Braces: (
Data Field: Vendor.VendorClassID
Condition: Equal
Value 1: @Class
Operator: Or The second clause covers the case when a user will not specify a vendor class before running the report. Specify the following settings in the second row of the table as follows:
Data Field: @Class
Condition: IsNull
Braces: ) These two clauses are grouped into one expression by the Or operator (see the following screenshot). Lesson 7: Using Parameters and Filters | 88
```
    Figure: Adding the filter
```

Click the Apply button.
Click OK to save the filter defined for the report and close the Schema Builder.
On the Report Designer window toolbar, click Save.

Step 3: Viewing the Report To view the report, do the following:

In Acumatica ERP, open the S150 Vendor Summary (AP6550C2) report form by searching for its identifier. On the Report Parameters tab, notice that the Vendor Class label is displayed to the le of the box with the defined parameter.

              This report, which you have modified in this activity, has been published in the U100 dataset.
              That is, it has been added to the Site Map (SM200520) form, and you can access it in Acumatica
              ERP.

In the Vendor Class box, select any value, for example, Product.
On the report form toolbar, click Run Report.
Make sure that the report contains only vendors of the class you have specified. The following screenshot displays the report generated for the Product vendor class.
Figure: The S150 Vendor Summary (AP6550C2) report for the Product vendor class Lesson 8: Sorting and Grouping Data | 89