MDX.EXECUTE

MDX.EXECUTE executes MDX statements dynamically and returns and formats data.

When you convert an ad-hoc report, one conversion option is Query Based (MDX.EXECUTE). We recommend that you use MDX.EXECUTE reports if you do not need to write back data. They are faster because only one query is sent to the database, and they can be dynamic. You can use Microsoft Excel functionality to change the MDX statement and create interactive reports. MDX is to multidimensional databases what SQL is to relational databases.

The MDX.EXECUTE formula has this syntax:

=MDX.EXECUTE (Database Alias, Full MDX Query, Columns, Rows, Data Area, 
Resize Rows Automatically, Merge Labels, Indentations, Column Width, 
Format Extension, Show Borders, Merge Properties, Caption Style)

The most important feature of the formula is its second argument. That is, 'Full MDX Query'. The MDX.EXECUTE formula always remains the same. It is the MDX query which it executes that defines the report.

An example of creating a simple report with MDX.EXECUTE is given here: Creating a formula report with MDX.EXECUTE.

The Office Plus tutorial has examples of how to create both a simple report and a complex, dynamic, report with MDX.EXECUTE.

Arguments of MDX.EXECUTE

This table shows the arguments of the function:


Argument	Values	Description	Default Value
Alias		Name of the database alias.
FullMDXQuery		MDX statement.
Columns		Location of the first dimension cell of the column on the worksheet.	Name of the sheet!cell
Rows		Location of the first dimension cell of the row on the worksheet.	Name of the sheet!cell
Dataset		Location of the first data area cell on the worksheet.	Name of the sheet!cell
Resize row captions automatically	True/False	The Representation options of an Ad-hoc report are passed to this argument of the MDX.Execute formula when the report is converted. See Options If the Resize row captions automatically check box is selected, this argument is set to TRUE and row captions are automatically resized. If the check box is cleared, the argument is set to FALSE and the row captions are not resized. You can use 1 and 0 in place of True and False when creating, or manually editing an MDX.Execute formula.
Merge Labels	True/False	The Representation options of an Ad-hoc report are passed to this argument of the MDX.Execute formula when the report is converted. See Options If the Merge labels check box is selected, this argument is set to TRUE and labels are merged. If the check box is cleared, the argument is set to FALSE and labels are not merged. You can use 1 and 0 in place of True and False when creating, or manually editing an MDX.Execute formula.
Indent	Numerical (0-9)	Specifies how far to the right list elements are indented under their column heading. Zero specifies no indent. Nine specifies maximum indent.
Column Width	Numerical (0, -1 or a number greater than 0)	The column width options of an Ad-hoc report are passed to this argument of the MDX.Execute formula when the report is converted. See Options
Format Extension	0	Values and formatting	0
	1	Formatting only
	2	Values only
Show borders	True/False	The Representation options of an Ad-hoc report are passed to this argument of the MDX.Execute formula when the report is converted. See Options If the Show borders check box is selected, this argument is set to TRUE and borders are displayed. If the check box is cleared, the argument is set to FALSE and borders are not displayed. You can use 1 and 0 in place of True and False when creating, or manually editing an MDX.Execute formula.
	1	Show borders
Merge Properties	0	This parameter is now obsolete	1
	1	Merge attributes
Caption Style	1	Caption and drill-down indicator	1
	2	Caption
	3	Unique name and drill-down indicator
	4	Unique name
	5	Parent caption/caption and drill-down indicator
	6	Caption of the parent/caption

Cell properties

In the MDX statement you define the content and the format of the data using the cell properties. Add the keyword CELL PROPERTIES to the MDX statement and specify the cell properties:

CELL PROPERTIES property1 ,property2,...

If the CELL PROPERTIES are not specified, these cell properties apply:


Property	Description
Value	Values
Formatted_Value	Displays the value with the format specified on the server.
Cell_ordinal	Ordinal number of the values.

When you use the keyword CELL PROPERTIES, only the specified cell properties are used.

If the format definitions in the MDX statement differ from those of the Excel styles, the definitions of the MDX statement apply.

You can enable these additional cell properties:


Property	Description
BACK_COLOR	Background color
FORE_COLOR	Font color
FONT_NAME	Font family
FONT_SIZE	Font size
FONT_FLAGS	Font flags 1 bold 2 italic 4 underlined 8 strikethrough (5 means bold and underlined)
FORMAT_STRING	Number format of the values, defined on the client
CELL_EVALUATION_LIST	The semicolon-delimited list of evaluated formulas applicable to the cell, from the lowest to the highest solve order.
NON_EMPTY_BEHAVIOR	Behavior of the aggregated elements with empty data cells.
SOLVE_ORDER	Solve Order.