AX1754

Configuring a web report to dynamically change years of data

You can set up a web report so that the data in the report dynamically changes based on a user's selected year. To do this:

• Set up the report using year-based tables, such as GL2018, BGT2019, and so on.
• Create a Year refresh variable and configure it as needed, so that users can select years as appropriate for the report setup and available data.

When users view the report, they can select a year for the Year refresh variable in order to adjust the "base year" of the report. The report is then refreshed with data relative to the selected year.

For example, imagine that a report shows 2018 actuals, with a comparison to the prior year (2017). If the user wants to see the same comparison for the previous year, they can use the year variable to change the base year to 2017. Now the report shows 2017 actuals with a comparison to the prior year (2016).

Setting up the report for dynamic years

The basic report setup is as normal, except that data components must use year-based tables if you want the data to change based on the selected year. A year-based table is simply a table with the year in the name, such as GL2018.

For example, you could set up a Data Grid component as shown in the following screenshot. This report shows current year actuals and budget (GL2018.YTD and BGT2018.YTD) as compared to last year's actuals and budget (GL2017.YTD and BGT2017.YTD). The report could also have calculated columns to show the difference and percent difference.

In this example, 2018 is the base year for the report, because the report is built for 2018 and uses columns from 2018 tables (and adjacent year tables). When the Year refresh variable is used to change the year, all table and column references are adjusted based on the difference between the base year and the selected year.

For example, if the user selects 2017 as the new value for the refresh variable, that selected year is one year less than the original base year of 2018. All references to year-based tables in the report are adjusted to be one year less. The reference to GL2018.YTD is adjusted to GL2017.YTD, and the reference to GL2017.YTD is adjusted to GL2016.YTD.

References to year-based tables are adjusted this way throughout the report. This includes the primary table for the report, table columns used in data grids, calculated columns used in data grids, and so on.

IMPORTANT: Table column references that use file group table variables cannot be used with the Year variable. The report cannot dynamically adjust for both the table variables and the Year refresh variable. This means that you should not associate the report with a file group (using File Group Context) until you have already configured all of your table column references. In this case, the only purpose of using the file group context is to dynamically set the default year for the Year variable to the file group year.

Creating the Year refresh variable

In order to change the year dynamically, create a refresh variable for the report and use the Year variable type.

Example Year variable type

When creating this variable, you define the following:

• Base Report Year: The year the report is built for, based on the tables and columns included in the report (as discussed in the previous section). This setting is required in order to set the baseline to adjust the year-based references in the report.

• Default Value: The year that you want the report to use by default when it is opened. When the report is first created, the default value and the base report year will likely be the same. In our example, we want the report to use 2018 by default.

  However, over time, the default value can change while the base report year remains the same. When 2019 starts, we can change the default value of the report to 2019 so that the report is refreshed based on 2019 data (adjusting references 1 year up from the base year of 2018). However, the base year of 2018 must remain the same, because the tables and columns referenced in the report have not changed.

  If the report uses a file group context, you can leave the default value blank to use the file group year by default. Otherwise, the default value is required.

  If the web report is opened from an Axiom form, you can pass a default year from the Axiom form, using the Navigate to Report command.

• Years allowed prior to / after default: These two settings determine the years available for the user to select. If the prior-allowed years is set to 2 and the default year is 2018, then users can select 2018, 2017, and 2016. If the after-allowed years is set to 1, then users can also select 2019. These settings are optional and are interpreted as 0 if left blank.

  You should set these fields as appropriate depending on the purpose of the report and the available data. If you allow users to select a year which results in invalid tables, an error results. For example, if the report displays actuals data and you allow users to select years in the future, your system may not have actuals tables for those future years yet (and even if it does, those tables likely do not have any data).

When the user views the web report, they can now use the Year refresh variable to change the base year and refresh the file with applicable data.

Example Year variable in Filter panel

Passing a year to a web report

You can open a web report from an Axiom form, and pass a default value for the Year refresh variable, so that the report is refreshed for the specified year.

The easiest way to do this is to use the Navigate to Report command with the reserved document variable name ReportYear. In the following example, the Navigate to Report command is configured to open a web report and pass a specified value for ReportYear. The value for ReportYear uses a bracketed cell reference to read the value from the form source file, so that the value can dynamically change based on inputs made to the form.

Example Navigate to Report command that passes a value for ReportYear

When this command is executed, the specified web report is opened, and the value for the Year refresh variable is set to the value passed from the form. It does not matter what the name of the Year refresh variable is—since each web report can only have one Year refresh variable, the ReportYear document variable is automatically associated with it.

Alternatively, you can manually append the ReportYear parameter to a generated URL for a web report, using the syntax &ReportYear=year. You can use the Axiom function GetWebReportDocumentURL to generate the URL, and then use a formula to append the parameter. For example:

This example formula generates a URL such as: