Report Designer: Formulas not working correctly due to cell referencing

Alerts and notices
Leave feedback

Internal Employees: Submit feedback

Contact information (optional):

Leave this blank:

Please tell us how we can make this information more helpful.


Characters left:

Change to CS Support service hours

Our Support department is closed on Friday, April 28th for a company event. However, limited Accounting CS phone support is available on that date from 9 AM - 5 PM ET due to the approaching deadline for quarterly payroll tax forms.

Links to our most popular payroll tax processing topics are available in the Alerts and notices section on the right side of most pages.

When working in the Report Designer on a report that has formulas, they may not work properly because of the cells that are being referenced.

See also: Working with formulas

It is critical to understand that the Report Designer calculates reports the same way a book reads. This means that the application will begin with cell A1, calculate all of the cells in row 1, and then move on to cell A2 and calculate that row, etc.

Example

Here is an example of a report not calculating percentages correctly in column D. Show me.

Incorrect percent formulas

The report is not calculating the percentages correctly because the denominator in the formula (the total sales) has not been calculated yet. Only the values above and to the left of the percent cell have been calculated.

To allow the percentages to calculate correctly, use the following steps:

  1. First, you will need to move the total (the denominator) to a cell above and to the left of the cell where the percent is being calculated. In this example, take the [ChartofAccountsActivity.Core.Amount] variable, modify the properties of that cell to show the total of all the sales (income) accounts, and place it in row 4. Show me
    Correct percent formulas
  2. Next, hide row 4 by giving it a row height of 0. This will allow the application to calculate the total, but not display it on the report.
  3. Finally, change the percentage formulas to divide by the total hidden in row 4 instead of the total below the cell with the percent.

Share This