Difference between revisions of "Formula Reference"

(→‎Report Management Formulae)
(→‎OLAP Formulae)
Line 3: Line 3:
 
| width="200" | [[XL3Lookup]]
 
| width="200" | [[XL3Lookup]]
 
| width="400" | Returns the value for a given cross section of the Cube
 
| width="400" | 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]]
 
| [[XL3LookupRW]]
 
| Returns the value for a given cross section of the Cube, allowing a writeback to be performed on the cell
 
| 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]]
 
| [[XL3Member]]
 
| Returns the caption(s) for a member or members
 
| 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)
 
 
|-
 
|-
 
| [[XL3MemberAlias]]
 
| [[XL3MemberAlias]]
 
| Allows you to alias a member lookup to return a more readable name.
 
| Allows you to alias a member lookup to return a more readable name.
−
|-
 
−
| [[XL3HierarchyLookup]]
 
−
| Returns the Dimension name
 
 
|-
 
|-
 
| [[XL3MemberChildLookup]]
 
| [[XL3MemberChildLookup]]
Line 27: Line 46:
 
| [[XL3MemberLevelLookup]]
 
| [[XL3MemberLevelLookup]]
 
| Returns the specified member for a given level
 
| 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]]
 
| [[XL3PropertyLookup]]
Line 33: Line 58:
 
| [[XL3RankLookup]]
 
| [[XL3RankLookup]]
 
| Returns the member at the specified position of a Ranking
 
| Returns the member at the specified position of a Ranking
 +
|-
 +
| [[XL3RunSQL]]
 +
| Runs specified SQL
 
|-
 
|-
 
| [[XL3ValueRankLookup]]
 
| [[XL3ValueRankLookup]]
 
| Returns the Measure value at the specified position of a Ranking
 
| 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]]
 
| [[XL3LastCubeUpdate]]
 
| Returns the time at which the cube was last updated
 
| Returns the time at which the cube was last updated
 
|-
 
|-
−
| [[XL3DoWriteback]]
+
| [[XL3UserName]]
−
| Optionally writes a value to a specific cube cross-section
+
| Returns user id of currently logged in user
 
|}
 
|}
  

Revision as of 09:32, 4 October 2011

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
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

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
Retrieved from ‘https://help.fluencexl.com/index.php?title=Formula_Reference&oldid=3021’