Formula Reference

OLAP Formulae

XL3Lookup Returns the value for a given cross section of the Cube v6.0
XL3DataSeries Returns a set of cube cell values v6.0
XL3DataSeriesLookup Returns a set of cube cell values v6.0
XL3DoWriteback Optionally writes a value to a specific cube cross-section v6.1
XL3HierarchyLookup Returns the Dimension name v6.0
XL3KpiLookup Returns the value of a KPI for a given cross section of the cube v6.0
XL3KpiPropertyLookup Returns a property for a given KPI v6.0
XL3LookupRW Returns the value for a given cross section of the Cube, allowing a writeback to be performed on the cell v6.0
XL3MdxLookup Returns the value from a specified MDX statement v6.0
XL3MdxMemberLookup Returns a member from a specified MDX statement v6.0
XL3MdxDataSeries Returns a set of cube cell values from an MDX statement v6.0
XL3Member Returns the caption(s) for a member or members v6.0
XL3MemberAlias Allows you to alias a member lookup to return a more readable name. v6.5
XL3MemberChildLookup Returns the specified child for a given member v6.0
XL3MemberLevelLookup Returns the specified member for a given level v6.0
XL3MemberLookup Returns the caption for a member key or unique name (superseded by XL3Member) v6.0
XL3MemberNavigate Returns a member from its relationship to another (e.g. parents, children, siblings etc) v6.0
XL3PropertyLookup Returns the Member Property text for a specified dimension member v6.0
XL3RankLookup Returns the member at the specified position of a Ranking v6.0
XL3SetProperty Allows the updating of object types properties v6.5
XL3ValueRankLookup Returns the Measure value at the specified position of a Ranking v6.0
XL3LastCubeUpdate Returns the time at which the cube was last updated v6.0
XL3UserName Returns user id of currently logged in user v6.5
XL3GridMember Returns the member uniquename for the specified grid cell v7.2
XL3GridArrayLookup Returns an array containing the result of a query. v9.1

Tabular Formulae

XL3LookupTabular Returns the value of a measure for a given filters v7.0

In-Cell Chart Formulae

XL3SparkLine Creates a line, point or area chart that is rendered in an Excel cell v6.0
XL3SparkLineM Creates a monochrome line or point chart that is rendered in the Excel cell v6.0
XL3SparkColumns Creates a column chart that is rendered in an Excel cell v6.0
XL3SparkColumnsM Creates a monochrome column chart that is rendered in the Excel cell v6.0
XL3SparkWinLose Creates a win/loss chart that is rendered in an Excel cell v6.0
XL3SparkWinLoseM Creates a monochrome win/loss chart that is rendered in the Excel cell v6.0
XL3SparkBar Creates a bar chart that is rendered in an Excel cell v6.0
XL3SparkBarM Creates a monochrome bar chart that is rendered in the Excel cell v6.0
XL3SparkBullet Creates a bullet chart that is rendered in an Excel cell v6.0
XL3SparkHorizon Creates a horizon chart that is rendered in an Excel cell v6.0
XL3SparkIcon Creates an indicator icon that is rendered in an Excel cell v6.0
XL3SparkHeatMap Creates a set of coloured icons suitable for use as a heatmap v6.0
XL3SparkPie Creates a pie chart that is rendered in an Excel cell v6.0
XL3SparkBox Creates a box plot chart that is rendered in an Excel cell 6.1
XL3SparkDotPlot Creates a dot plot chart that is rendered in an Excel cell 6.1
XL3SparkLolliplot Creates a lollipop chart that is rendered in an Excel cell 7.0
XL3SparkHeatMap Creates a heatmap section that is rendered in an Excel cell 8.0

Relational Formulae

XL3QueryTableGetConnectionString Gets the connection string of a Query Table v6.0
XL3QueryTableSetConnectionString Sets the connection string of a Query Table v6.0
XL3QueryTableSetParam Sets the query parameter value of a Query Table v6.0
XL3QueryTableSetSQL Sets the SQL statement of a Query Table v6.0
XL3RunSQL Runs specified SQL v6.5
XL3RunSQLProc Runs specified Stored Procedure v7.1
XL3SqlArea Creates and updates table range based on the results of a SQL Query v8.0
XL3TableArrayLookup Returns an array containing the result of a query. v9.1

Report Management Formulae

XL3Link Creates a hyperlink that opens a sheet and sets parameters v6.0
XL3RowVisible Shows or hides a row v6.0
XL3ColumnVisible Shows or hides a column v6.0
XL3DynamicPicture Updates a Picture based on a URL (web address) v6.5
XL3RefreshObjects Refreshes objects in the report based on type v7.1
XL3RefreshSheetObjects Refreshes objects in the report based on type v9.0
XL3RefreshObjectsNamed Refreshes objects in the report based on name v7.1
XL3RefreshConnections Refreshes objects connected to a connection v9.0
XL3WebReportLink Creates a hyperlink that opens an XLCubed Web report and optionally sets web parameters v6.5
XL3IsWeb Returns True if report is running in XLCubedWeb and False if the report is running in Excel v7.5
XL3ResourceLookup Returns the text for the specified resource for the current culture (web only) v8.1

Comment Formulae

XL3CubeComment Get the comments for a particular cube cell v8.1
XL3GridComment Get the comments for a Grid v8.1

Formula Wizards

The XLCubed Excel Add-in provides a menu-driven, graphical user interface to facilitate easy building of reports. The reports are built by combining XLCubed functions on a spreadsheet page. The following functions are supported by the GUI.

Ribbon/Menu option Function Version
Insert Members XL3Member v6.0
Insert Value XL3Lookup/XL3LookupRW v6.0
Insert Ranking XL3RankLookup v6.0
Insert Member Property XL3PropertyLookup v6.0
Insert Data Series XL3DataSeriesLookup v6.0
Insert Link XL3Link v6.0

Excel Formulae

See Supported Excel Formulae for a full list. The list of supported Excel functions can also be downloaded: