FastClose has three concepts that are loosely bundled under the title of “Variables”

• Related Member Variables

• Sets of Constants

• SQL Parameter Variables

By far the most commonly used of these, is the “Related Member Variable”, which is the focus of this tutorial.

Here is a video, or read below.

Overview - Related Member Variables

It is “best practice” to use related member variables to represent item selections that could change in the future. They are most commonly used with time related items (though they can be used on any field).

For example, when first building a balance sheet, the user would likely be looking at this year’s data, say...

Fiscal Year filter with a Year selected

But in a year’s time it will need to move on to look at data for next year. If the year is selected directly in the Fiscal Year filter (as above), the report will work very nicely until the end of the year, but after year end when we look to roll forward to the new year and so update the selected year, we could find our column formats disappear, that calculations are still working on historic instead of current data and that columns we expect to be hidden, reappear.

There is then significant work to be done by the report designer, to migrate such a report to the new year.

These problems all occur because the report design is directly referencing the year being displayed in the layout of the report; with calculations, formats and so on, all directly referencing that year.

The solution is to use a variable, so that instead of talking about a year, 2011 or 2021 or whatever, we talk about “This Year” or “Current Year”. Then all the calculations, formats and so on can be set to reference the item “Current Year”, which in turn is set to point to 2021.

When that subsequently is changed to point at 2022 the report carries on working seamlessly, displaying data for the new year with calculations and formats all being applied correctly.

Fiscal Year filter with a variable selected, adjacent to a variable selector that specifies the value of that variable

Design Time

Setting up Variables

To edit the definitions of variables switch to the “Insert” tab, and select “Variables”…

…which will display the Variables dialog.

On the left hand side is a list of all the fields in the report that have variables defined for them. There are several different types but in this tutorial we are only interested in the “Related Member Variable” type. When a field is selected (like "Fiscal Year" above) the right hand zone shows information for that field.

For this type of variable then, the right hand zone contains a list of all the individual variables defined for the selected field. In the example above, the “Fiscal Year” field has two variables defined: ThisYear and LastYear and at a glance we can see their name, caption and current value.

Finally the smaller zone on the right hand side, is used to configure the selected variable.

Variable pointing directly at an Item

Where a variable is set to point directly at an item, such as with the ThisYear variable shown above left, this is straight forward.

ThisYear variable set to 2021 and with filter control

The variable is given:

• Name – this will be used to refer to the variable in filters and other selectors.

• Caption – nicer text that will be displayed in the report and as the title for any control that governs it (note the space separating ‘This’ from ‘Year’).

• Related To – Which can be used to select the item being pointed at, here 2021.

And then two control settings:

• Visible in Selections Variables View – when enabled, the filter can be controlled from the Report Variables dialog which can be opened by clicking on the button found here:

  

• Editable in Filters Area - when enabled, if the variable is selected, a control will be placed directly in the main “Filters” area like this:

Variable pointing at another variable

Where a variable is set to point at another variable, such as with the LastYear example shown here

LastYear variable set to ThisYear with an offset of -1 and without a filter control (as it will be driven by ThisYear)

the variable is setup slightly differently:

• Related to – Instead of pointing directly at an item such as 2021, instead point to another variable such as ThisYear

• Shift By – now we can shift the item forward or backward in the item list from the one pointed to. In this example, shifting by -1 moved last year so that instead of pointing at 2021, it will point at 2020 – which can be seen in the result box at the bottom.

In this configuration, where one variable is related to another, report designers would usually leave the Visible in Selections Variable View box and Editable in Filters Area boxes clear, as the variable is designed to be controlled by the other variable, so direct control is not required (indeed it would be confusing to the user).

This ability to daisy chain them to create members such as ThisYear & LastYear, ThisMonth & LastMonth or even rolling 12 month analyses is incredibly useful. By setting LastYear to be ThisYear with an offset of -1, we can control both variables by setting the value of just the ThisYear variable. As the setting of ThisYear changes, so the value of LastYear will track it whilst calculations such as Variance to Last Year = [Year].[ThisYear]-[Year].[LastYear] work seamlessly.

Setting up the Filter Area

Once the variables have been configured, it is worth thinking about how users will interact with them.

In many reports it is the case that the variable control becomes the primary driver of the report rather than the filter.

Because the layout as well as much else is controlled by the filter, it can make sense to hide the main filter control, whilst leaving the variable control visible.

To do this switch to the “Layout” tab, and select “Add / Remove Filters”, and then hide the filter concerned. Eg:

becomes

Doing this, protects the design of the report from changes made by inexperienced users.

Setting up Column Headers

Now that the report has variables the designer will want to be able to control how the current value of the variable is displayed in the grid results. For more on this, see the “Column Headers” tutorial.

However as a glimpse of what is possible, column headers can be set automatically by switching to the “Layout” tab, entering the “Rows/Columns” dialog, going to the 2nd tab and selecting “Pretty Nested Column Captions” as shown in this P&L example below

giving column headers as shown below, just one of the possible ways that variables can be displayed.

Report with Column Headers that reference variables

Setting up a Report Title

Similarly it can be useful to reference a variable in the report’s title so that it can read something like “P&L for 2011” where the 2011 is provided by a variable. For more on this see the “Report Title” tutorial.

But for a glimpse of what is possible, switch to the “Insert” tab and click the “Report Title” button and then select one of the Sample report title designs available in that dialog as below.

Run Time – Using and Changing Variables

Now that the report is complete, it only remains for users to make use of the report, setting variable values as they need. They can do this using the controls exposed on the front report surface or through the variables button on the left hand side, safe in the knowledge that the whole report will continue to work as expected.

Exercises and Tutorial Survey

04.01 Variables - Exercises.pdf

Your feedback helps us do better, to fill in a short survey on this tutorial, click here.