Active forms are a new TM1 feature as of version 9.4. Active forms are the long awaited replacement for the often maligned (and rightly so!) dynamic slices which suffered from poor performance and instability. At Cubewise we love active forms as they offer some powerful features in the Excel and TM1 web environments that were previously only available via an MDX ODBO connection requiring understanding of MDX and extensive VBA coding. Active forms make features such as drill-down/roll-up, zero suppression, filtering and ranking not just accessible but able to be implemented in minutes with no coding. If you have 9.4 and haven't boned up on active forms, then you should. If you are still on 9.0 or 9.1 then you should really consider migrating to 9.4, especially given IBM's end of life statement for older versions.

To really get active forms there are only 4 things you need to understand:
1. The TM1RptView formula
2. The TM1RptRow formula
3. The TM1RptFilter formula
4. The TM1RptFmtRng and TM1RptFmtIDCol ranges

Firstly a preface, this tech tip is only intended as an overview. In the next few weeks we will commence publishing an ongoing feature on active form report construction and tips as part of our Cubewise Blog.

Let's start with the TM1RptView formula. Basically this is a new implementation of the VIEW formula specifically for active forms. It has the following arguments:
TM1RptView(ViewID, ZeroSuppression, TM1RptTitle1 ... TM1RptTitleN, FormatRange, FormatIDColumn)
The parameters with bold font are basically as per the old VIEW formula so we will concentrate on the underlined ones which are specific to active forms.
• Zero Suppression: simple really 1 = suppress zeros on rows, 0 = no zero suppression
• Format Range: the named range where the active form data rows will inherit their formatting from
• Format ID Column: the column where the key for data row formatting is held

The TM1RptRow formula can be a little more complicated (but doesn't have to be) as most of the arguments are optional:
TM1RptRow(ReportView, Dimension, Subset, SubsetElements, Alias, ExpandAbove, MDXStatement, Indentations, ConsolidationDrilling)
Only the first three parameters are mandatory, the remaining italicised parameters are optional and can be left blank.
• Report View: cell reference to the cell containing the TM1RptView formula
• Dimension: name of the row dimension
• Subset: reference to a named public subset or location of a list of elements for the row dimension
If a named public subset is not used for the row dimension(s) when slicing out an active form then a hidden sheet called "{AR}01" is inserted into the workbook. The hidden sheet contains the elements in the "root subset" of the active form. However it is much better to always used a named public subset for the rows and avoid the hidden sheet as this is both more efficient and allows for much more flexible reporting (more on this in the blog series).

The TM1RptFilter formula is straightforward, even with no knowledge of MDX:
TM1RptFilter(ReportView, Tuple, FilterFunction, FilterValue, SortOrder)
• Report View: cell reference to the cell containing the TM1RptView formula
• Tuple: the MDX specification of the column dimension to which the filter will apply (eg. [dimension].[element])
• Filter Function: what type of filter to apply, eg. top count, bottom count, top sum, etc.
• Filter Value: the number of returned rows to apply to the filter function, eg top 10, bottom 10, etc.
• Sort Order: ASC = ascending, DESC = descending

The most important things to understand about the formatting ranges are:
• To apply formatting to additional columns outside of the DBRWs in the active form you must expand TM1RptFmtRng to cover all columns to be formatted
• Additional rows can be inserted into TM1RptFmtRng, similarly superfluous rows can be removed
• The {0, 1, 2, 3, D, N} default row formatting key can be replaced by whatever custom formatting key is required (more on this in the blog series)

This has just been an introduction, remember to check the Cubewise Blog regularly for further instalments in our active form tutorial. We will start with working with the TM1RptView formula then move on to other areas in subsequent instalments.

If working with active forms in advance of reading further posts on this topic remember that like all TM1 formulas the arguments of active form functions can be reduced to simple text strings and Boolean values (which is all TM1 cares about when evaluating the function). It is possible to parameterise almost every aspect of these formulas and combine then with other standard Excel tricks to perform some really useful and powerful reports.