Formula Reference

Revision as of 15:07, 16 March 2012 by Colin Overton (talk)

OLAP Formulae

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

Tabular Formulae

XL3LookupTabular Returns the value of a measure for a given filters

In-Cell Chart Formulae

XL3SparkLine Creates a line, point or area chart that is rendered in an Excel cell
XL3SparkLineM Creates a monochrome line or point chart that is rendered in the Excel cell
XL3SparkColumns Creates a column chart that is rendered in an Excel cell
XL3SparkColumnsM Creates a monochrome column chart that is rendered in the Excel cell
XL3SparkWinLose Creates a win/loss chart that is rendered in an Excel cell
XL3SparkWinLoseM Creates a monochrome win/loss chart that is rendered in the Excel cell
XL3SparkBar Creates a bar chart that is rendered in an Excel cell
XL3SparkBarM Creates a monochrome bar chart that is rendered in the Excel cell
XL3SparkBullet Creates a bullet chart that is rendered in an Excel cell
XL3SparkHorizon Creates a horizon chart that is rendered in an Excel cell
XL3SparkIcon Creates an indicator icon that is rendered in an Excel cell
XL3SparkPie Creates a pie chart that is rendered in an Excel cell
XL3SparkBox Creates a box plot chart that is rendered in an Excel cell

Query Table Formulae

XL3QueryTableGetConnectionString Gets the connection string of a Query Table
XL3QueryTableSetConnectionString Sets the connection string of a Query Table
XL3QueryTableSetParam Sets the query parameter value of a Query Table
XL3QueryTableSetSQL Sets the SQL statement of a Query Table

Report Management Formulae

XL3Link Creates a hyperlink that opens a sheet and sets parameters
XL3RowVisible Shows or hides a row
XL3ColumnVisible Shows or hides a column
XL3DynamicPicture Updates a Picture based on a URL (web address)

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
Insert Members XL3Member
Insert Value XL3Lookup/XL3LookupRW
Insert Ranking XL3RankLookup
Insert Member Property XL3PropertyLookup
Insert Data Series XL3DataSeriesLookup
Insert Link XL3Link