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 improve the readability of the function.
Additionally, linkages to other Excel cells may extend the flexibility of this function, allowing dynamic interaction with the user.
CReflex members can be made drillable (from v2020.1.3) by setting the argument CReflexAxis.
Syntax
CREFLEX(Datasource, Catalog, MDX, DataRange, FormatRange LeftOffset, TopOffset, RightOffset, BottomOffset, MaxRows, MaxColumns, NullsAs, CReflexAxis)
Arguments¶
Datasource : string (required)¶
Specify the Analysis Services server that you want to connect to
Catalog : string (required)¶
Specify the catalog of cubes on the datasource that you want to perform your query on
MDX : string (required)¶
Specify the MDX statement to query on
Data Range : string (required)¶
Specify the data range of the report
Format Range : string (required)¶
Specify the format range of the report
Left Offset : number (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 : number (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 : number (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 : number (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 : number (required)¶
Specify the max row number of the report.-1 implies no limit
Default value: -1
Max Columns : number (required)¶
Specify the max column number of the report. .-1 implies no limit
Default value: -1
Nulls As : string (required)¶
Specify the value which can replace null
Default value: 0
CReflex Axis : string¶
Specify one of the following axis drill behaviours to affect the CREFLEX formula members when drill is performed.
Available drills are:
"NoneDrill", "RowDrill", "ColumnDrill","RowColumnDrill"
Default value: NoneDrill
NoneDrill¶
The members are not drillable.
RowDrill¶
Inserts the children below the selected member in the row axis.
ColumnDrill¶
Inserts the children below the selected member in the column axis.
RowColumnDrill¶
Inserts the children below the selected member in the row or column axis.
Usage Scenarios¶
To build dynamically sizing reports based on an MDX query. All arguments except CReflexAxis are required.
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:
Links between Report Sheet and MDX Statement¶
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,"-","NoneDrill")
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 MaxRows 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. You should use the Offset formula to reference cells within the data range to ensure this doesn’t happen, as per below.
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 percentage proportion of the account being reported on (in this instance Net Profit/ (Loss) ). The formula used 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, then RightOffset would be 3. Likewise, should these columns be to the left of DataRange, we would need to include the appropriate number of columns in LeftOffset.