Advanced period formulas

In Report Designer, you have the possibility to use predefined period functions during the report building. In cases where these features do not meet your needs, you can use advanced period formulas to determine the year, period(s) or day(s) in the report. This guide explains how to use such formulas in Report Designer.

Advanced period formulas

When you work on advanced period formulas, you often have to relate to two variables in each formula.

Example:

{PeriodCalc.GetPeriod(Parameter).AddYears(X).WholeYear}

Parameter is the period parameter created in the report, and X is the variable that determines the number of years, months/periods and days that the formula should add or subtract.

Figure 1 shows where to find and replace the parameter for the period.

1. In the Layout Editor, click the group to enter the expansion.
2. Click the Lookup button to do a lookup. In the Lookup dialog, you can see the name of the parameter (marked in blue).
3. Copy the text within {}.
4. Paste this in to replace the text Parameter in the formulas described in this document.

Figure 1 Finding and replacing the parameter for the period

Period formulas based on a monthly ID (e.g. Period=201801 for January 2018)

The table below shows various period formulas where you can replace (Parameter) with (PostingPeriods)^1^ and replace the variable X with the number of years/months you want to add or subtract. The formula element Extend determines how many months to add to an interval. More examples of built-in period functions will follow in the section Overview of the built-in period functions in Report Designer below.

^1^The term used varies between ERP systems. Look for the period parameter, such as Periods, Posting Periods, Accounting Periods, etc. in the Layout Editor. (See Figure 1 above for where to look.)

Example of formula for rolling interval from X to Y

If you want to show 2 years of rolling history, then you must first subtract 1 month (from the period parameter you select when you run the report), and then extend (Extend) with 23 months.

Here, X gets the value -1 and Y gets the value -23.

The formula would like this:

{PeriodCalc.GetPeriod(PostingPeriods).Add(-1).Extend(-23)}

Period Formulas based on date ID (format dd.mm.yyyy – e.g. 01.01.2019)

The table below shows various period formulas where you can replace (DateParameter) with a date parameter^2^ and replace the variable X with years/months you want to add or subtract. You find the parameter in the same way as in the example above. The only difference is that you have a period dimension based on day level, for example, posted date.

NOTE: When the period formula is based on a date, as opposed to a period (month), you must include ’ before and after the formula: '{DateParameter.AddYears(X).SqlDate}'

^2^The term used varies between ERP systems. Look for date parameters such as Due Date, Transaction Date, etc.

Example of formula for day Y in year X

In this example, you want to find yesterday last year. You can do this by replacing -1 Y with -1. The formula will then subtract 1 year minus 1 day for the date you set when you run the report.

'{DateParameter.AddYears(-1).AddDays(-1).SqlDate}'

‘{DateParameter.AddYears(-1).AddDays(-1).SqlDate}’
This is a period formula based on date. It calculates date based on current date/today’s date

Example of formula with Due Date

In this example, you want to show invoices that are due in the next 30 days, based on a date parameter, @DueDate. You can do this by using this formula:

'{@DueDate.AddDays(30).SqlDate}'

Overview of the built-in period functions in Report Designer

Additional Period Syntax

The following syntax can be applied within the period filter. DateParameter = name of period parameter

The functions may be combined:

'{DateParameter.YearEnd.AddDays(1).SqlDate}':'2020-12-31