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 equals 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.

Click on the below links for details:

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 report output. 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:

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.

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 click the Formula Editor == IMAGE== icon on the toolbar.

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

Revenue is the product of all order quantities (B5) and unit prices (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 equals sign (=). To reference a cell, enclose the coordinates in square brackets ([ ]). We'll enter =[B5]*[C5] in cell D5 to calculate the revenue for each product.

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:

Tip: Instead of using the Formula Editor it is possible to aggregate using the AutoSum icon on the toolbar. Select the target cell, enter an equal sign followed by the values 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.)

Concatenation combines 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 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 figure below, the Concatenate function has been entered into the cell manually. Note the ampersands.

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

For example: {Categories.ID} & " " & {Categories.Name}

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