Flex Reporting

Introduction

Flex reporting is a new style of Formula Reporting introduced in Version 7.6. It allows a report designer to define a custom drill path for a report, so the user of the report could drill, for example, from Products => Customers => Orders. Each Drill is performed so that only rows with data are return, this means drilling into a particular product would only return customers that have bought that product (this is dependent on the Measure applying over both hierarchies)

Getting Started

The starting place for any Flex Report is to insert a Grid and define the starting structure, so which dimensions will be on filters, rows and columns. As with traditional formula reports the structure of the report is static once converted to formula so it's important to start with a grid that matches the final structure.

Once the grid is ready, right-click and select "Convert to flex report", this will show the Flex Report form.

Flex Reporting Form

A individual drill level is called a "Flex Set" and a collection of these is a "Flex Path". A individual member can be assigned a "Flex Path" and as the user drills they will navigate the "Flex Sets" in the path.

Flex Paths

A workbook can contain several Flex Paths, it's also possible to export and import the definitions so they can be reused. Each flex path has a defined name, and it's this name that links a formula to a particular flex path.

When converting a grid to a flex report, select or define the default path for the flex report and click OK (you can always edit the formula to change the flex path later)

Name

The name given to the path is referenced and in XL3FlexMember formulae that are set to use the path

Filter Members

If set to the filter members in the report then any drilled flex members will automatically update to show the members with data for the new selection. The formula report will dynamically grow and shrink in a similar way as grids.

After changing the filter member:

Disable 'Drill To' menu

By default any XL3FlexMember will include a "Dill To" menu option, this allows the report's user to drill to any valid attribute/hierarchy level. You can use this option to disable that functionality for the given path.

Flex Sets

A flex set is the level to drill to you can control the order the flex sets appear in by using the toolbar to move them:

Each set can be given a name, this will default to the level name (the name will be shown on the right-click menu) and can be changed to anything suitable.

Name

The name given to the Flex Set will be displayed on the "Expand To" menu

Level

You can select a level of a hierarchy or attribute for the set to use.

Excel Range

An excel range can be used for the source of the flex set - enter any valid Set MDX in the cell

MDX

You can enter the MDX used to return the set here - enter any valid Set MDX

Auto Expand on Drill

When 'Auto Expand' is enabled then all members returned in this set will be automatically expanded to the next flex set.