CREFLEX

The CREFLEX formula is used to build dynamically sizing reports, or “Reflex reports”, based on an MDX query, whilst simultaneously applying dynamically changing formats to cells based upon format rules.

The CREFLEX formula is an extension on the CGETMDX formula.

Through the use of CCONCAT, the MDX statement may be laid out on additional worksheets, which may assist readability of the function.

Additionally, linkages to other Excel cells may extend the flexibility of this function, allowing dynamic interaction with the user.

Syntax

CREFLEX (Datasource, Catalog, MDX, DataRange, FormatRange, LeftOffset, TopOffset, RightOffset, BottomOffset, MaxRows, MaxColumns, NullsAs)

Arguments

Datasource

(Required)

Specify the Analysis Services server that you want to connect to

Catalog

(Required)

Specify the catalog of cubes on the datasource that you want to perform your query on

MDX

(Required)

Specify the MDX statement to query on

Data Range

(Required)

Specify the data range of the report

Format Range

(Required)

Specify the format range of the report

Left Offset

(Required)

Specify the left offset of the report. This extends the calculation range to the number of columns to the left whenever the CREFLEX function is calculated

Default value: 0

Top Offset

(Required)

Specify the top offset of the report. This extends the calculation range to the number of rows up whenever the CREFLEX function is calculated

Default value: 0

Right Offset

(Required)

Specify the right offset of the report. This extends the calculation range to the number of columns to the right whenever the CREFLEX function is calculated

Default value: 0

Bottom Offset

(Required)

Specify the Bottom Offset of the report. This extends the calculation range to the number of rows down whenever the CREFLEX function is calculated

Default value: 0

Max Rows

(Required)

Specify the max row number of the report.-1 implies no limit

Default value: -1

Max Columns

(Required)

Specify the max column number of the report. .-1 implies no limit

Default value: -1

Nulls As

(Required)

Specify the value which can replace null

Default value: 0

Usage Scenarios

To build dynamically sizing reports based on a piece of MDX. All Arguments are compulsory.

Examples

This example has many components, so to simplify we will break them down.

MDX Statement

The MDX statement is held on a separate sheet called MDX (for ease of update, readability and comprehension). It is not the purpose of this example to explain the MDX statement in detail. The MDX statement is:

Cell B13 is linked to the report sheet , with the formula: =Report!H17 ( which has the value 5 ). Cell B11 is linked to the reports sheet, with the formula: =Report!B4 ( and has the value [Account].[Acounts].[Net Profit/(Loss)] ).  These linkages mean that as the front sheet has adjustments made, the MDX statement will update, and in turn, provide dynamic results back.

On the Report sheet, this statement is concatenated (through the use of CALUMO function CCONCAT) in cell B5, with the formula:

=CCONCAT(MDX!$B$2:$C$25)

Cell H17 is a simple input cell (user just types the number in) and cell B4 is the result of the CMEMBER formula in cell D17. The user will double click to bring up the member explorer to make adjustments.

CREFLEX Breakdown

Cell B16 holds the CREFLEX formula, which is:

=CREFLEX($B$1,$B$2,$B$5,$B$20:$P$54,$B$7:$Q$15,0,0,1,0,-1,-1,"-")

The DataSource, Catalog and MDX statement are held in cells B1, B2 and B5 respectively. Since we are not limiting the number of rows or columns being returned, the argument -1 is used for both MaxRow and MaxColumns.

Should this not be the case, simply enter the maximum number of rows or columns to be displayed. This will not constrain the records the MDX statement will return, just what is displayed on screen.

Format Range

The Data Format to be applied to the report is specified in cells B7 to Q15.

The data range matches against the format range according to relative positions. For ease of use, it is much simpler to include the columns being compared or formatted above each other. In this example, column C is the column being used. Whenever a cell in column C of the data range matches the format range, the format row will be applied. Should an entry be made into an additional column, both columns conditions must resolve to true for the format to apply. If any of the conditions are not met, the formatting will not apply. Accordingly, should there be 2 columns of conditions, across 4 rows, to ensure all possible combinations are met 8 rows (4 x 2) should be listed in the format range with appropriate formatting.

Data Range

The data to be output will be provided in cells B20 to P54. This is dynamic (depending on the MDX statement, and could be specified simply as B20 – the report will automatically adjust).

As the report is dynamic, each time it is run the data area is cleared. Accordingly, any Excel formulas which reference this area will no longer work, and will have the #Ref! Error.

Offset

To avoid this situation, rather than link to the cell directly, the offset cell method should be used. This is illustrated in cell Q19, which has the formula:

="% of "&OFFSET(Q19,1,-1)&" "&OFFSET(Q19,2,-15)

From the anchor cell (Q19), it will come down 1 row, and to the left for 1 column (which will return the value in cell P20, or FY 2012/13). From the same anchor cell, it references the cell 2 rows down and 15 columns to the left (cell B21), which has the value Net Profit / (Loss).

A secondary offset formula is used (in cell Q20), which calculates the % proportion of the account being reported on (in this instance Net Profit/ (Loss) ).  The formula used is must be on the first line of the data area (row 20), and to the left or right of the range. In this instance, it will be immediately after the data range (in column Q).

In order for this formula to update when report is running, we need to include these cells in the CREFLEX formula. Column Q is 1 column to the right of the data range, hence RightOffset is set to 1. If we needed to include 3 columns to the right, since we had formulas or other calculations in 3 columns instead of 1, the right offset would be 3. Likewise, should these columns be to the left of the data range, we would need to include  the appropriate number of columns in LeftOffset. 

Sample sheet

Download CREFLEX worksheet