Back to F350: Analytical Reports
Lesson 7: Using Formulas in a Report
Formulas: General Information
You can use formulas to calculate the values to be displayed in the rows and columns of each report. Formulas give you the ability to use advanced calculations and data transformation functions if some values in the report rows and columns are calculated or depend on the data from other sources (such as rows, columns or individual cells included in the report). The formulas used in the analytical reports are much like the formulas used in Excel. You can define parameters and construct a formula by using operators and functions. You can select the parameters used in the formula from the list of predefined parameters or enter them into the formula.
Learning Objectives
In this lesson, you will learn how to do the following in analytical reports:
- Use formulas in a report
- Use formula functions
- Use formula operators
- Use formula parameters
Applicable Scenarios
You may want to use formulas in the following circumstances:
- You are responsible for developing and modifying reports to give users the information they need to do their jobs.
- You need to perform calculations on data or transformation of data before presenting it.
Using Formulas to Assign Values
To define formulas in analytical reports, you use the Formula Editor dialog box. You invoke this dialog box by clicking the magnifier button in the appropriate Value cell on the Row Sets (CS206010), Column Sets (CS206020), or Unit Sets (CS206030) form. Lesson 7: Using Formulas in a Report | 78
Figure: Formula Editor dialog box
The Formula Editor dialog box, which is shown in the screenshot above, includes the following panes:
- Component Type pane: Displays the types of parameters, operators, and functions that can be used as formula components. Click any of the types to display the corresponding list of available components in the Component Selection pane.
- Component Selection pane: For the component type selected in the Component Types pane, displays the list of available components. Click a component to add it to the formula to the Formula Text pane.
- Formula Text pane: Contains the text of the formula, which you can edit manually. The formula may include
the selected components, arguments of manually inserted components, and other elements, all arranged in
accordance with the syntax of the formula.
You can check if the syntax of the formula is correct by clicking Validate.
Formulas: Functions
You use functions to perform specific tasks that facilitate data processing for reports. Many functions available in the Analytical Report Manager process data selected from the data source and return the values to be used in the report. To use functions in a formula, you can enter them directly in the formula editing area or select them from the list of functions provided in the Formula Editor dialog box. The following table lists some of the functions that are frequently used in analytical reports of the GL type. Lesson 7: Using Formulas in a Report | 79
Table: Frequently Used Functions
Function Description and Examples
IIf(expression, truePart, falseP- Returns one of two values, depending on the evaluation of the expression: art) If the expression evaluates to True, the function returns the truePart value; otherwise, it returns the falsePart value. Example: =IIf((A10-B10)<>0), CStr(A12), 'No data available') (where A10, A12, and B10 are the links used as a function ar- guments)
Sort(from, to, column) Returns the values in the specified range of rows in the specified column sorted in ascending order. Example: Sort('0100','0145','B') (the values in rows from 0100 to 0145 in the B column will be sorted in ascending order)
SortD(from, to, column) Returns the values in the specified range of rows in the specified column sorted in descending order. Example: SortD('0100','0145','B') (the values in rows from 0100 to 0145 in the B column will be sorted in descending order)
Sum(from, to) Returns the sum of the values in the specified interval. Example: =Sum('A11','A100') (where A11 and A100 are the links used in a function argument)
Switch(expression_1, value_1, Returns the value (value_n) that corresponds to the first expression (expres- expression_2, value_2, ...) sion_n) that evaluates to True. For example, expression_1 and expression_2 are Boolean expressions. Example: =Switch(((A10-B10)<>0), A12, ((A10-B10)>0), B35)
GetDefUI(object field) Gets the default value of the object field parameter in the UI format. Examples:
=Report.GetDefUI('RowAccessInfo.DisplayName')
=Report.GetDefUI('RowCompanyBAccount.AcctName')
Le(string, length) Returns a string containing a specified number of characters from the le side of a string. If string contains the null value, the null value is returned. Example: =Left(CStr(A12), 3) (where A12 is the link used in a func- tion argument)
Right(string, length) Returns a string containing a specified number of characters from the right side of a string. If string contains a null value, the null value is returned. Example: =Right(CStr(A12), 3) (where A12 is the link used in a func- tion argument)
FormatYear(object period) Retrieves the year part of the object period and converts it into the UI format. This function is used to get the period defined by the report's @StartPeriod and @EndPeriod dates. Lesson 7: Using Formulas in a Report | 80
For more information about functions in analytical reports, see Formula Functions.
Formulas: Parameters
A formula may include parameters that reference the data you want to use in calculating the values in a report. There are two types of parameters: predefined parameters; and links to specific rows, columns, or individual report cells.
Predefined Parameters
The predefined parameters are application-specific and refer to the report parameters that you specify on other ARM forms, such as the Report Definitions (CS206000) form or the Row Sets (CS206010) form. For example, the @StartPeriod parameter refers to the value specified in the Start Period box on the Report Definitions form (or on the report form), and the @BaseRowCode parameter refers to the value specified in the Base Row box on the Row Sets form. All available predefined parameters are listed in the Data Source Editor dialog box.
The table below contains some of the predefined parameters that are frequently used in reports of the GL type.
Parameter Description
@AccountCode The code of the current account if the Account option is selected in the Ex-
pand box in the Data Source Editor dialog box.
This is the code of the current subaccount if the Sub option is selected in
the Expand box.
@AccountDescr The description of the current account if the Account option is selected in
the Expand box in the Data Source Editor dialog box.
This is the description of the current subaccount if the Sub option is select-
ed in the Expand box.
@BaseRowCode The row code specified for the selected row in the Base Row column on the
Row Sets form. This row code is referred to by this parameter, which you
use in the formula in the Value box on the Column Sets (CS206020) form to
retrieve the value specified in this row for computing another value in the
report.
@BookCode The code of the ledger whose data is used in the report. This parameter
refers to the value in the Ledger box in the Default Data Source Settings
section.
@BranchName The company branch identifier in the current tenant. For more information
on company branches, see Branches (CS102000) form.
@ColumnCode The code of the current column in the report.
@ColumnIndex The index of the current column in the report.
@ColumnSetCode The code of the current column set in the report.
@ColumnText The description of the current column in the report.
Lesson 7: Using Formulas in a Report | 81
Parameter Description
@EndAccount The last account in the range of accounts used in the report. This parameter
refers to the value in the End Account box on the report form or in the De-
fault Data Source Settings section of the Report Definitions form.
@EndBranch The last branch in the range of branches used in the report. This parameter
refers to the value in the End Branch box on the report form or in the De-
fault Data Source Settings section of the Report Definitions form.
@EndPeriod The end period of the report. This parameter refers to the value in the End
Period box on the report form or in the Default Data Source Settings sec-
tion of the Report Definitions form.
@EndSub The last subaccount in the range of subaccounts used in the report. This pa-
rameter refers to the value in the End Sub box on the report form or in the
Default Data Source Settings section of the Report Definitions form.
@StartAccount The first account in the range of accounts used in the report. This parameter
refers to the value in the Start Account box on the report form or in the De-
fault Data Source Settings section of the Report Definitions form.
@StartBranch The first branch in the range of branches used in the report. This parameter
refers to the value in the Start Branch box on the report form or in the De-
fault Data Source Settings section of the Report Definitions form.
@StartPeriod The start period of the report. This parameter refers to the value in the Start
Period box on the report form or in the Default Data Source Settings sec-
tion of the Report Definitions form.
@StartSub The first subaccount in the range of subaccounts used in the report. This pa-
rameter refers to the value in the Start Sub box on the report form or in the
Default Data Source Settings section of the Report Definitions form.
@ReportDescr The report description that the system prints in the column set header.
@RowCode The code of the current row in the report.
@RowIndex The index of the current row in the report.
@RowSetCode The code of the current row set in the report.
@RowText The description of the current row in the report.
@UnitCode The code of the current unit in the report.
@UnitSetCode The code of the current unit set in the report.
@UnitText The description of the current unit in the report.
Links to Rows
You can use the links to the rows in the formulas to define the parameters. The links to the rows are used when a formula is defined for the row to calculate its values, and the referenced rows are the parameters used in Lesson 7: Using Formulas in a Report | 82
these calculations. For example, when a row is a summed value of the other two rows, the formula includes the
references to the rows to be summed.
The following is an example of a formula using links to the rows (where 110 and 120 are the row codes).
=@110+@120
Links to Columns
You can use the links to the columns in the formulas to define the parameters. You use the links to the columns
when a formula is defined for the column to calculate its values, and the referenced columns are the parameters
used in these calculations. For example, when a column is a summed value of the other two columns, the formula
includes the references to the columns to be summed.
To reference a column, replace its code in the formula with a parameter.
The following is an example of a formula with links to the columns (where A, B, and D are the column codes).
=A+B+D
Links to Cells
You can use the links to the report cells in the formulas to define the parameters. You use these links when a
formula is defined for the column or row to calculate its values, and the referenced cells are the parameters used in
these calculations.
To reference a cell, add its code to the formula as a parameter.
The following is an example of a formula using links to the cells.
=A60+B30+D20
In this example, A, B, and D are the column codes, while 20, 30, and 60 are the row codes. The cells located in the
specified rows of the columns indicated by the first letters are used as the parameters in this formula.
Formulas: Operators
Comparison Operators
Comparison operators, which are described below, compare two expressions and return a Boolean value that
represents the result of the comparison.
Operator Description and Examples
= The equality operator.
Example:=(A10=B10)
In this example, A10 and B10 are cell references. If the value in the A10 cell is equal to the value
in B10, then the expression evaluates to True; otherwise, the expression evaluates to False.
Lesson 7: Using Formulas in a Report | 83
Operator Description and Examples
<> The inequality operator.
Example: =(C10<>A10)
(where A10 and C10 are the links used as formula parameters)
In this example, A10 and C10 are cell references. If the value in the C10 cell is not equal to the
value in A10, then the expression evaluates to True; otherwise, the expression evaluates to
False.
< The less than operator.
Example: =(@10<@12) (where @10 and @12 are the links to cells in the current column)
In this example, @10 and @12 are the links to cells in the current column. If the value in the
@10 cell is less than the value in @12, then the expression evaluates to True; otherwise, the
expression evaluates to False.
> The greater than operator.
Example: =(C10>A10) (where A10 and C10 are the links used as formula parameters)
In this example, A10 and C10 are cell references. If the value in the C10 cell is greater than to
the value in A10, then the expression evaluates to True; otherwise, the expression evaluates to
False.
<= The less than or equal to operator.
Example: =(C10<=A10) (where A10 and C10 are the links used as formula parameters)
In this example, A10 and C10 are cell references. If the value in the C10 cell is less than or equal
to the value in A10, then the expression evaluates to True; otherwise, the expression evaluates
to False.
>= The greater than or equal to operator.
Example: =(A10>=C10) (where A10 and C10 are the links used as formula parameters)
In this example, A10 and C10 are cell references. If the value in the A10 cell is greater than or
equal to the value in C10, then the expression evaluates to True; otherwise, the expression
evaluates to False.
Other Operators
The table below describes the operators of the Other type.
Operator Description and Examples
In (a binary opera- This operator is used in an elementary logical expression that evaluates the search re-
tor) sults and returns True when the parameter matches one of the elements from the set of
values.
Example: =(A20 In (10, 50, 57)) (where A20 is the link used as a formula para-
meter)
True (a binary con- This operator is used as a parameter in logical expressions.
stant)
Example: =((D30<A30)=True) (where D30 is the link used as a formula parameter)
Lesson 7: Using Formulas in a Report | 84
Operator Description and Examples
False (a binary con- This operator is used as a parameter in logical expressions.
stant)
Example: =((C10=E10)=False) (where C10 and E10 are the links to the data)
Null (a value) This operator is used as a parameter in logical expressions.
Example: =(@20=Null) (where @20 is the cell in the current column and 10th row)
Formulas: To Display a Column Header Based on the Selected Period
The following activity will walk you through the process of modifying a column set so that its header is based on the selected financial period.
Story
Suppose that the SweetLife Fruits & Jams company reports profits and losses each quarter. The company's financial manager has requested that you modify the Profit & Loss report to display the quarter for which the report displays data as the column header. Acting as a technical specialist who is responsible for building and supporting analytical reports in SweetLife, you need to modify the column set of the Profit & Loss report to change the column header based on the selected financial period.
Configuration Overview
In the training dataset, the following tasks have been performed for the purposes of this activity:
- On the Column Sets (CS206020) form, the F350CS16 column set has been created.
- On the Report Definitions (CS206000) form, the F350RD16 report definition has been created with the F350CS16 column set specified.
Process Overview
In this activity, you will do the following:
- On the Report Definitions (CS206000) form, review the Profit & Loss report.
- On the Column Sets (CS206020) form, modify the column set to display the column header based on the selected range of periods.
- On the Report Definitions form, update the report parameters and then review the updated Profit & Loss report.
Step 1: Reviewing the Existing Report To review the existing version of the Profit & Loss report, do the following:
- On the Report Definitions (CS206000) form, open the report definition with the F350RD16 code.
- On the form toolbar, click Preview. The report form opens in a pop-up window.
- On the Report Parameters tab of the report form, specify the following report parameters:
- Company: SWEETLIFE Lesson 7: Using Formulas in a Report | 85
- Ledger: ACTUAL
- Financial Period: 12-2023
- On the report form toolbar, click Run Report.
The Profit & Loss report is opened in a pop-up window. Notice that the report displays one column with the
YTD amounts.
Figure: The Profit & Loss report before changes
Step 2: Updating the Column Header To update the column header in the report, do the following:
- On the Column Sets (CS206020) form, open the column set with the F350CS16 code.
- In the upper table, double-click the cell in the fourth row of column B, and then click the magnifier button to open the Formula Editor.
- In the lower pane of the Formula Editor, replace the existing value with the following formula:
=Report.FormatYear(@EndPeriod) + ' ' + 'Q' + switch(Left(@EndPeriod,2)<=3,
'1', Left(@EndPeriod,2)>=4 and Left(@EndPeriod,2)<=6,
'2', Left(@EndPeriod,2)>=7 and Left(@EndPeriod,2)<=9, '3',
Left(@EndPeriod,2)>=10 and Left(@EndPeriod,2)<=12, '4')
This expression displays the year of the selected financial period followed by a space, then Q, and the
number of the quarter.
Lesson 7: Using Formulas in a Report | 86
The Report.FormatYear(period) function retrieves the year of the financial period. Instead of specifying a particular period, you have entered @EndPeriod, which corresponds to the end period specified in the report parameters. The Left(@EndPeriod,2) function returns the first two characters of the end period. The switch function returns the value that corresponds to the first expression that evaluates to True. The expression that you have entered as the first argument of the switch function compares the first two characters of the financial period with 3 and returns 1 if the expression is true. - Click Validate.
- Click OK to close the Formula Editor.
- On the form toolbar, click Save.
Step 3: Updating the Amount Type To display only turnover amounts for the selected quarter in the report, while you are still on the Column Sets (CS206020) form with the F350CS16 code selected, do the following:
- In the lower table, double-click the cell in the row with the Data Source attribute and column B, and click the magnifier button to open the Data Source dialog box.
- In the dialog box, set Amount Type to Turnover.
- Click OK to close the dialog box.
- On the form toolbar, click Save.
Step 4: Updating the Report Parameters To modify the default report parameters, do the following:
- On the Report Definitions (CS206000) form, open the report definition with the F350RD16 code.
- In the Default Data Source Settings section, in the drop-down list to the right of the End Period box, select Request.
- In the Start Period box, clear the predefined value.
- On the form toolbar, click Save.
Step 5: Reviewing the Updated Report To review the updated version of the Profit & Loss report, while you are still viewing the F350RD16 report definition on the Report Definitions (CS206000) form, do the following:
- On the form toolbar, click Preview. The report form opens in a pop-up window.
- On the Report Parameters tab of the report form, specify the following report parameters:
- Company: SWEETLIFE
- Ledger: ACTUAL
- Start Period: 01-2023
- End Period: 03-2023
- On the report form toolbar, click Run Report.
The Profit & Loss report is opened in a pop-up window. Notice that the column header is 2023 Q1.
Lesson 7: Using Formulas in a Report | 87
Figure: The updated Profit & Loss report displaying data for the first quarter of 2023 - On the report toolbar, click Parameters.
- On the report form, change the parameters as follows:
- Start Period: 04-2023
- End Period: 06-2023
- On the report form toolbar, click Run Report. The Profit & Loss report is opened in a pop-up window. Notice that the column header is 2023 Q2. Lesson 7: Using Formulas in a Report | 88
Figure: The updated Profit & Loss report displaying data for the second quarter of 2023 Lesson 8: Formatting a Report | 89