Formula Basics

Formulas allow users to create complex calculations, parse strings, and insert images within the application. Formulas can be comprised of functions, parameters, strings, data fields, and cell references. All formulas begin with an equal sign (=).

In the Report Designer, users can either key their desired formulas directly into a cell or enter them with the assistance of the built-in Formula Editor.

Adding Mathematical Calculations

Totals for each data field are not automatically calculated in an Advanced Report, so we must add calculations to the report in order to display totals. A great way to do this is through the use of footers, which are designed to aggregate the data contained in the section above them. Group Footers allow the totaling or counting of data contained in the group, and Report Footers allow the totaling of the full contents of a report.

In the example below, there is a group footer on Products.ProductName, and the Detail section of the report has been suppressed. Suppressed rows do not display on the executed report. The end goal is to have this report display one row for each confection product, each row containing the product name, total order quantity, unit price, and revenue generated by each product, with a grand revenue total at the end.

Looking more closely at the footer, we can expect the cells to display as follows:

Cell A5 will display the product name for each unique product in the group.
Cell B5 will display just the last order quantity value for each product in the group because there is a one-to-many relationship between products and order quantities.
Cell C5 will display the unit price for each unique product in the group.

If our goal is to calculate the revenue generated by each confection product, the first step would be to calculate the total quantity of orders for each product in cell B5.

Entering a Formula

To enter a formula into B5, we click on the cell and either enter =aggsum({Order Details.Quantity}) or select AggSum and the data field from the Formula Editor. To access the Formula Editor, select the cell and press the formula editor () button in the toolbar.

Now we can add a column to the right-hand edge of the table and calculate the product revenue in D5.

Calculating with Cell References

Revenue is the product of all order quantities (B5) and unit price (C5). Instead of completely rewriting the aggsum formula we just created in B5, we can use a cell reference to quickly and easily allow this inclusion.

Cell references are formulas, so they will have to start with an equal sign (=). To reference a cell, enclose the coordinates in square brackets ([ ]). We'll enter =[B5]*[C5] in D5 to calculate the revenue for each product.

Calculating in the Report Footer

To calculate a grand total of the Revenue column, we will add a new group to the report, this time a Report Footer. The Report Footer aggregates values contained in the whole report, as opposed to a particular group within it. Again, we will use cell references for simplicity.

Upon execution, the above report displays the following:

NOTE. Instead of using the Formula Editor, it is possible to aggregate using the AutoSum button () on the toolbar. Just select the target cell, enter an equal sign followed by the value(s) you wish to aggregate, and click the AutoSum button. (In the above example, the grand total formula would read =[D5] with the AutoSum button depressed.)

Concatenating Strings

Concatenation is the act of combining multiple fields or strings into a single string. For more on concatenation see the full list of formulas and descriptions.

In this report, we can use concatenation to create a label for our revenue grand total and specify what food category it pertains to.

Concatenation can be done by using an ampersand (&) between each string or by using the concatenate function in the Formula Editor.

In the example below, the concatenate function has been entered into the cell manually. Note the ampersands.

Note: To add space between elements in your concatenation, insert spaces at the beginnings and/or ends of your strings.

With a little added formatting, the executed report now looks like this. The "Confections" has replaced Categories.CategoryName in our concatenation formula.