XL3GridArrayLookup

Revision as of 16:05, 12 September 2017 by Colin (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Returns the result of an MDX query as an array. This is a CSE/Array formula so must be entered into a range at once using Ctrl+Shift+Enter.

This is the fastest way to return cube results into Excel, but limits your options for altering the query. Query Generators can help ease of use and allow users to filter the report based on Dimension Slicers and cell values.

Syntax

XL3GridArrayLookup( Connection, Mdx, [MemberHandling] )

Parameters

Parameter Description
Connection Connection number to use
Mdx The query to run. Typically from a cell so that the query can be updated.
MemberHandling Optional, how to output members
0/Missing Members not displayed
1 Member unique names
2 Member captions

Example

Here we have our MDX statement in cell A1. We select the range we want to output the results to, and type

Then press Ctrl+Shift+Enter.

Xl3gridarraylookup.png

Note that any cells outside of the data area return #N/A

See Also