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.
It is “best practice” to use single 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 “This 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
FastClose ships with several pre-defined Single Member Variables already configured that you can make use of out of the box.
These include This Year, Last Year & Next Year
As well as This Month.
These are held within the FastClose library and many standard templates and reports that include “Fiscal Year” or “Fiscal Period” dimensions will already be setup to use them.
To make use, go into the Member Selector for Year or Period, and just choose them, the controls for them will then appear in the filter area of the report.
If you come across a report that has the Fiscal Year and Fiscal Period dimensions, but doesn’t have these variables, you can subscribe to the items in the library using the “Library” button on the "Insert" toolbar as below
or the button in the "Variables" dialog, also accessed from the "Insert" menu.
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.
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, columns header can be set automatically by switching to the “Layout” tab and clicking the “Hide Repeated Labels” button
giving column headers as shown below, just one of the possible ways that variables can be displayed.
Report with Column Headers that reference variables
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.
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.
Where these predefined variables come from the Library, when the value is changed in the filter control, that value is only changed for the current user session. If the report is saved and re-opened the value with revert to the value stored in the library, this is because the report goes to the library when it is opened to get the current global value stored there.
If you want change the value stored in the library, for example, to roll the current period of the year forward for all future reporting, change the value of the variable within the Variables dialog itself, and that will then update the global values stored in the library.
UPDATE EXERCISES
Your feedback helps us do better, to fill in a short survey on this tutorial, click here.