Difference between revisions of "Formula Reference"

(OLAP Formulae)
(OLAP Formulae)
Line 75: Line 75:
 
|-
 
|-
 
| [[XL3GridMember]]
 
| [[XL3GridMember]]
| Returns the member uniquename from the specified grid cell
+
| Returns the member uniquename for the specified grid cell
 
|}
 
|}
  

Revision as of 11:53, 12 December 2012

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
XL3GridMember Returns the member uniquename for the specified grid cell

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)
XL3RefreshObjects Refreshes objects in the report based on type
XL3RefreshObjectsNamed Refreshes objects in the report based on name

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