Difference between revisions of "Formula Reference"

(In-Cell Chart Formulae)
(OLAP Formulae)
Line 12: Line 12:
 
| [[XL3MemberLookup]]
 
| [[XL3MemberLookup]]
 
| Returns the caption for a member key or unique name (superseded by XL3Member)
 
| 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)
 
|-
 
|-
 
| [[XL3HierarchyLookup]]
 
| [[XL3HierarchyLookup]]

Revision as of 16:26, 17 March 2011

OLAP Formulae

XL3Lookup Returns the value for a given cross section of the Cube
XL3LookupRW Returns the value for a given cross section of the Cube, allowing a writeback to be performed on the cell
XL3Member Returns the caption(s) for a member or members
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)
XL3HierarchyLookup Returns the Dimension name
XL3MemberChildLookup Returns the specified child for a given member
XL3MemberLevelLookup Returns the specified member for a given level
XL3PropertyLookup Returns the Member Property text for a specified dimension member
XL3RankLookup Returns the member at the specified position of a Ranking
XL3ValueRankLookup Returns the Measure value at the specified position of a Ranking
XL3KpiLookup Returns the value of a KPI for a given cross section of the cube
XL3KpiPropertyLookup Returns a property for a given KPI
XL3DataSeries Returns a set of cube cell values
XL3DataSeriesLookup Returns a set of cube cell values
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
XL3LastCubeUpdate Returns the time at which the cube was last updated

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

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

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