| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "Flex Reporting"
(→Flex Reporting Form) |
(added Category:Flex Reporting) |
||
| (16 intermediate revisions by 4 users not shown) | |||
| Line 1: | Line 1: | ||
==Introduction== | ==Introduction== | ||
| − | Flex reporting is a new style of [[Formula_Reporting_Overview|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 | + | Flex reporting is a new style of [[Formula_Reporting_Overview|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. The user can double click to follow the defined drill path, or can use a right click shortcut to go directly to any defined level in the drill path, which gives huge flexibility in a controlled environment. |
| + | |||
| + | Each Drill is performed so that only rows with data are returned, which 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== | ==Getting Started== | ||
| Line 13: | Line 15: | ||
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. | 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. | ||
| − | [[Image: | + | [[Image:FlexReport.png]] |
====Flex Paths==== | ====Flex Paths==== | ||
| Line 20: | Line 22: | ||
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) | 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) | ||
| + | |||
| + | [[Image:FlexPathOptions.png]] | ||
| + | |||
| + | ''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 to grids. | ||
| + | |||
| + | [[Image:FlexExample1.png]] | ||
| + | |||
| + | After changing the filter member: | ||
| + | |||
| + | [[Image:FlexExample2.png]] | ||
| + | |||
| + | ''Disable 'Drill To' menu'' | ||
| + | |||
| + | By default any XL3FlexMember will include a "Drill 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==== | ====Flex Sets==== | ||
| − | A flex set is the level to drill to | + | A flex set is the level to drill to. You can control the order in which the flex sets appear by using the toolbar to move them: |
[[Image:FlexReportFormOrder.png]] | [[Image:FlexReportFormOrder.png]] | ||
| − | Each set can be given a name | + | 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. |
| + | |||
| + | [[Image:FlexSetOptions.png]] | ||
| + | |||
| + | ''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. | ||
| + | |||
| + | ====Restrictions==== | ||
| + | |||
| + | It is only possible to include a Hierarchy in a report once, so if the hierarchy is on the rows, columns or filter area of the report then it cannot be used in any of the Flex Sets. This also applies within a flex path, so once a hierarchy has been used it cannot be included again at another level. | ||
| + | |||
| + | This restriction does not apply to attribute hierarchies and as these are single level, then it generally makes most sense to use the attribute hierarchy when defining the flex sets. | ||
===XL3FlexMember=== | ===XL3FlexMember=== | ||
| + | |||
| + | For more information about the XL3FlexMember please see the main article: [[XL3FlexMember]] | ||
==Navigating== | ==Navigating== | ||
| + | |||
| + | Once a flex report has been created then drilling will work in the same way as Formula Drilling, but will follow the Flex Path defined rather then the hierarchy levels. | ||
===Expand to=== | ===Expand to=== | ||
| + | |||
| + | Right-clicking on a Flex report member will give an "Expand To" option, this can be used to skip to a particular flex set level. | ||
===Drill to=== | ===Drill to=== | ||
| + | |||
| + | The ''Drill to'' option allows drilling to any valid hierarchy/attribute in the cube, it's not possible to drill to any hierarchies included in the report already so these will be disabled | ||
===Slicers=== | ===Slicers=== | ||
| − | + | To add slicers to a flex report - highlight one or more filter members and select "Add Slicer" - this will add slicers that are automatically cascading (that is they feed into each other top to bottom) and will also enable the "Auto Filter" option on the flex path. | |
| − | |||
[[Category:Formula Reporting]] | [[Category:Formula Reporting]] | ||
| + | [[Category:Flex Reporting]] | ||
Latest revision as of 14:55, 29 April 2016
Contents
[hide]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. The user can double click to follow the defined drill path, or can use a right click shortcut to go directly to any defined level in the drill path, which gives huge flexibility in a controlled environment.
Each Drill is performed so that only rows with data are returned, which 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 to grids.
After changing the filter member:
Disable 'Drill To' menu
By default any XL3FlexMember will include a "Drill 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 in which the flex sets appear 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.
Restrictions
It is only possible to include a Hierarchy in a report once, so if the hierarchy is on the rows, columns or filter area of the report then it cannot be used in any of the Flex Sets. This also applies within a flex path, so once a hierarchy has been used it cannot be included again at another level.
This restriction does not apply to attribute hierarchies and as these are single level, then it generally makes most sense to use the attribute hierarchy when defining the flex sets.
XL3FlexMember
For more information about the XL3FlexMember please see the main article: XL3FlexMember
Once a flex report has been created then drilling will work in the same way as Formula Drilling, but will follow the Flex Path defined rather then the hierarchy levels.
Expand to
Right-clicking on a Flex report member will give an "Expand To" option, this can be used to skip to a particular flex set level.
Drill to
The Drill to option allows drilling to any valid hierarchy/attribute in the cube, it's not possible to drill to any hierarchies included in the report already so these will be disabled
Slicers
To add slicers to a flex report - highlight one or more filter members and select "Add Slicer" - this will add slicers that are automatically cascading (that is they feed into each other top to bottom) and will also enable the "Auto Filter" option on the flex path.
