Difference between revisions of "Formula Reference"

(→‎Report Management 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
 +
| width="100" | v6.0
 
|-
 
|-
 
| [[XL3DataSeries]]
 
| [[XL3DataSeries]]
 
| Returns a set of cube cell values
 
| Returns a set of cube cell values
 +
| v6.0
 
|-
 
|-
 
| [[XL3DataSeriesLookup]]
 
| [[XL3DataSeriesLookup]]
 
| Returns a set of cube cell values
 
| Returns a set of cube cell values
 +
| v6.0
 
|-
 
|-
 
| [[XL3DoWriteback]]
 
| [[XL3DoWriteback]]
 
| Optionally writes a value to a specific cube cross-section
 
| Optionally writes a value to a specific cube cross-section
 +
| v6.1
 
|-
 
|-
 
| [[XL3HierarchyLookup]]
 
| [[XL3HierarchyLookup]]
 
| Returns the Dimension name
 
| Returns the Dimension name
 +
| v6.0
 
|-
 
|-
 
| [[XL3KpiLookup]]
 
| [[XL3KpiLookup]]
 
| Returns the value of a KPI for a given cross section of the cube
 
| Returns the value of a KPI for a given cross section of the cube
 +
| v6.0
 
|-
 
|-
 
| [[XL3KpiPropertyLookup]]
 
| [[XL3KpiPropertyLookup]]
 
| Returns a property for a given KPI
 
| Returns a property for a given KPI
 +
| v6.0
 
|-
 
|-
 
| [[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
 +
| v6.0
 
|-
 
|-
 
| [[XL3MdxLookup]]
 
| [[XL3MdxLookup]]
 
| Returns the value from a specified MDX statement
 
| Returns the value from a specified MDX statement
 +
| v6.0
 
|-
 
|-
 
| [[XL3MdxMemberLookup]]
 
| [[XL3MdxMemberLookup]]
 
| Returns a member from a specified MDX statement
 
| Returns a member from a specified MDX statement
 +
| v6.0
 
|-
 
|-
 
| [[XL3MdxDataSeries]]
 
| [[XL3MdxDataSeries]]
 
| Returns a set of cube cell values from an MDX statement
 
| Returns a set of cube cell values from an MDX statement
−
|-
+
| v6.0
 
|-
 
|-
 
| [[XL3Member]]
 
| [[XL3Member]]
 
| Returns the caption(s) for a member or members
 
| Returns the caption(s) for a member or members
 +
| v6.0
 
|-
 
|-
 
| [[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.
 +
| v6.5
 
|-
 
|-
 
| [[XL3MemberChildLookup]]
 
| [[XL3MemberChildLookup]]
 
| Returns the specified child for a given member
 
| Returns the specified child for a given member
 +
| v6.0
 
|-
 
|-
 
| [[XL3MemberLevelLookup]]
 
| [[XL3MemberLevelLookup]]
 
| Returns the specified member for a given level
 
| Returns the specified member for a given level
 +
| v6.0
 
|-
 
|-
 
| [[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)
 +
| v6.0
 
|-
 
|-
 
| [[XL3MemberNavigate]]
 
| [[XL3MemberNavigate]]
 
| Returns a member from its relationship to another (e.g. parents, children, siblings etc)
 
| Returns a member from its relationship to another (e.g. parents, children, siblings etc)
 +
| v6.0
 
|-
 
|-
 
| [[XL3PropertyLookup]]
 
| [[XL3PropertyLookup]]
 
| Returns the Member Property text for a specified dimension member
 
| Returns the Member Property text for a specified dimension member
 +
| v6.0
 
|-
 
|-
 
| [[XL3RankLookup]]
 
| [[XL3RankLookup]]
 
| Returns the member at the specified position of a Ranking
 
| Returns the member at the specified position of a Ranking
 +
| v6.0
 
|-
 
|-
 
| [[XL3SetProperty]]
 
| [[XL3SetProperty]]
 
| Allows the updating of object types properties
 
| Allows the updating of object types properties
 +
| v6.5
 
|-
 
|-
 
| [[XL3ValueRankLookup]]
 
| [[XL3ValueRankLookup]]
 
| Returns the Measure value at the specified position of a Ranking
 
| Returns the Measure value at the specified position of a Ranking
 +
| v6.0
 
|-
 
|-
 
| [[XL3LastCubeUpdate]]
 
| [[XL3LastCubeUpdate]]
 
| Returns the time at which the cube was last updated
 
| Returns the time at which the cube was last updated
 +
| v6.0
 
|-
 
|-
 
| [[XL3UserName]]
 
| [[XL3UserName]]
 
| Returns user id of currently logged in user
 
| Returns user id of currently logged in user
 +
| v6.5
 
|-
 
|-
 
| [[XL3GridMember]]
 
| [[XL3GridMember]]
 
| Returns the member uniquename for the specified grid cell
 
| Returns the member uniquename for the specified grid cell
 +
| v7.2
 
|}
 
|}
  
Line 79: Line 102:
 
| width="200" | [[XL3LookupTabular]]
 
| width="200" | [[XL3LookupTabular]]
 
| width="400" | Returns the value of a measure for a given filters
 
| width="400" | Returns the value of a measure for a given filters
 +
| width="100" | v7.0
 
|}
 
|}
  
Line 85: Line 109:
 
| width="200" | [[XL3SparkLine]]
 
| width="200" | [[XL3SparkLine]]
 
| width="400" | Creates a line, point or area chart that is rendered in an Excel cell
 
| width="400" | Creates a line, point or area chart that is rendered in an Excel cell
 +
| width="100" | v6.0
 
|-
 
|-
 
| [[XL3SparkLineM]]
 
| [[XL3SparkLineM]]
 
| Creates a monochrome line or point chart that is rendered in the Excel cell
 
| Creates a monochrome line or point chart that is rendered in the Excel cell
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkColumns]]
 
| [[XL3SparkColumns]]
 
| Creates a column chart that is rendered in an Excel cell
 
| Creates a column chart that is rendered in an Excel cell
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkColumnsM]]
 
| [[XL3SparkColumnsM]]
 
| Creates a monochrome column chart that is rendered in the Excel cell
 
| Creates a monochrome column chart that is rendered in the Excel cell
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkWinLose]]
 
| [[XL3SparkWinLose]]
 
| Creates a win/loss chart that is rendered in an Excel cell
 
| Creates a win/loss chart that is rendered in an Excel cell
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkWinLoseM]]
 
| [[XL3SparkWinLoseM]]
 
| Creates a monochrome win/loss chart that is rendered in the Excel cell
 
| Creates a monochrome win/loss chart that is rendered in the Excel cell
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkBar]]
 
| [[XL3SparkBar]]
 
| Creates a bar chart that is rendered in an Excel cell
 
| Creates a bar chart that is rendered in an Excel cell
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkBarM]]
 
| [[XL3SparkBarM]]
 
| Creates a monochrome bar chart that is rendered in the Excel cell
 
| Creates a monochrome bar chart that is rendered in the Excel cell
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkBullet]]
 
| [[XL3SparkBullet]]
 
| Creates a bullet chart that is rendered in an Excel cell
 
| Creates a bullet chart that is rendered in an Excel cell
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkHorizon]]
 
| [[XL3SparkHorizon]]
 
| Creates a horizon chart that is rendered in an Excel cell
 
| Creates a horizon chart that is rendered in an Excel cell
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkIcon]]
 
| [[XL3SparkIcon]]
 
| Creates an indicator icon that is rendered in an Excel cell
 
| Creates an indicator icon that is rendered in an Excel cell
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkHeatMap]]
 
| [[XL3SparkHeatMap]]
 
| Creates a set of coloured icons suitable for use as a heatmap
 
| Creates a set of coloured icons suitable for use as a heatmap
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkPie]]
 
| [[XL3SparkPie]]
 
| Creates a pie chart that is rendered in an Excel cell
 
| Creates a pie chart that is rendered in an Excel cell
 +
| v6.0
 
|-
 
|-
 
| [[XL3SparkBox]]
 
| [[XL3SparkBox]]
 
| Creates a box plot chart that is rendered in an Excel cell
 
| Creates a box plot chart that is rendered in an Excel cell
 +
| 6.1
 +
|-
 +
| [[XL3SparkDot]]
 +
| 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
 
|}
 
|}
  
Line 130: Line 180:
 
| width="200" | [[XL3QueryTableGetConnectionString]]
 
| width="200" | [[XL3QueryTableGetConnectionString]]
 
| width="400" | Gets the connection string of a Query Table
 
| width="400" | Gets the connection string of a Query Table
 +
| width="100" | v6.0
 
|-
 
|-
 
| [[XL3QueryTableSetConnectionString]]
 
| [[XL3QueryTableSetConnectionString]]
 
| Sets the connection string of a Query Table
 
| Sets the connection string of a Query Table
 +
| v6.0
 
|-
 
|-
 
| [[XL3QueryTableSetParam]]
 
| [[XL3QueryTableSetParam]]
 
| Sets the query parameter value of a Query Table
 
| Sets the query parameter value of a Query Table
 +
| v6.0
 
|-
 
|-
 
| [[XL3QueryTableSetSQL]]
 
| [[XL3QueryTableSetSQL]]
 
| Sets the SQL statement of a Query Table
 
| Sets the SQL statement of a Query Table
 +
| v6.0
 
|-
 
|-
 
| [[XL3RunSQL]]
 
| [[XL3RunSQL]]
 
| Runs specified SQL
 
| Runs specified SQL
 +
| v6.5
 
|-
 
|-
 
| [[XL3RunSQLProc]]
 
| [[XL3RunSQLProc]]
 
| Runs specified Stored Procedure
 
| Runs specified Stored Procedure
 +
| v7.1
 
|-
 
|-
 
| [[XL3SqlArea]]
 
| [[XL3SqlArea]]
 
| Creates and updates table range based on the results of a SQL Query
 
| Creates and updates table range based on the results of a SQL Query
 +
| v8.0
 
|}
 
|}
  
Line 154: Line 211:
 
| width="200" | [[XL3Link]]
 
| width="200" | [[XL3Link]]
 
| width="400" | Creates a hyperlink that opens a sheet and sets parameters
 
| width="400" | Creates a hyperlink that opens a sheet and sets parameters
 +
| width="100" | v6.0
 
|-
 
|-
 
| [[XL3RowVisible]]
 
| [[XL3RowVisible]]
 
| Shows or hides a row
 
| Shows or hides a row
 +
| v6.0
 
|-
 
|-
 
| [[XL3ColumnVisible]]
 
| [[XL3ColumnVisible]]
 
| Shows or hides a column
 
| Shows or hides a column
 +
| v6.0
 
|-
 
|-
 
| [[XL3DynamicPicture]]
 
| [[XL3DynamicPicture]]
 
| Updates a Picture based on a URL (web address)
 
| Updates a Picture based on a URL (web address)
 +
| v6.5
 
|-
 
|-
 
| [[XL3RefreshObjects]]
 
| [[XL3RefreshObjects]]
 
| Refreshes objects in the report based on type
 
| Refreshes objects in the report based on type
 +
| v7.1
 
|-
 
|-
 
| [[XL3RefreshSheetObjects]]
 
| [[XL3RefreshSheetObjects]]
 
| Refreshes objects in the report based on type
 
| Refreshes objects in the report based on type
 +
| v9.0
 
|-
 
|-
 
| [[XL3RefreshObjectsNamed]]
 
| [[XL3RefreshObjectsNamed]]
 
| Refreshes objects in the report based on name
 
| Refreshes objects in the report based on name
 +
| v7.1
 
|-
 
|-
 
| [[XL3RefreshConnections]]
 
| [[XL3RefreshConnections]]
 
| Refreshes objects connected to a connection
 
| Refreshes objects connected to a connection
 +
| v9.0
 
|-
 
|-
 
| [[XL3WebReportLink]]
 
| [[XL3WebReportLink]]
 
| Creates a hyperlink that opens an XLCubed Web report and optionally sets web parameters
 
| Creates a hyperlink that opens an XLCubed Web report and optionally sets web parameters
 +
| v6.5
 
|-
 
|-
 
| [[XL3IsWeb]]
 
| [[XL3IsWeb]]
 
| Returns True if report is running in XLCubedWeb and False if the report is running in Excel
 
| Returns True if report is running in XLCubedWeb and False if the report is running in Excel
 +
| v7.5
 
|-
 
|-
 
| [[XL3ResourceLookup]]
 
| [[XL3ResourceLookup]]
 
| Returns the text for the specified resource for the current culture (web only)
 
| Returns the text for the specified resource for the current culture (web only)
 +
| v8.1
 
|}
 
|}
  
Line 190: Line 258:
 
| width="200" | [[XL3CubeComment]]
 
| width="200" | [[XL3CubeComment]]
 
| width="400" | Get the comments for a particular cube cell
 
| width="400" | Get the comments for a particular cube cell
 +
| width="100" | v8.1
 
|-
 
|-
 
| [[XL3GridComment]]
 
| [[XL3GridComment]]
 
| Get the comments for a [[Grid]]
 
| Get the comments for a [[Grid]]
 +
| v8.1
 
|}
 
|}
  
Line 200: Line 270:
 
! scope="col" width="200" | Ribbon/Menu option
 
! scope="col" width="200" | Ribbon/Menu option
 
! scope="col" width="200" | Function
 
! scope="col" width="200" | Function
 +
| v6.0
 
|-
 
|-
 
| [[Insert Members]]
 
| [[Insert Members]]
 
|XL3Member
 
|XL3Member
 +
| v6.0
 
|-
 
|-
 
| [[Insert Value]]
 
| [[Insert Value]]
 
|XL3Lookup/XL3LookupRW
 
|XL3Lookup/XL3LookupRW
 +
| v6.0
 
|-
 
|-
 
| [[Insert Ranking]]
 
| [[Insert Ranking]]
 
|XL3RankLookup
 
|XL3RankLookup
 +
| v6.0
 
|-
 
|-
 
| [[Insert Member Property]]
 
| [[Insert Member Property]]
 
|XL3PropertyLookup
 
|XL3PropertyLookup
 +
| v6.0
 
|-
 
|-
 
| [[Insert Data Series]]
 
| [[Insert Data Series]]
 
|XL3DataSeriesLookup
 
|XL3DataSeriesLookup
 +
| v6.0
 
|-
 
|-
 
| [[Insert Link]]
 
| [[Insert Link]]
 
|XL3Link
 
|XL3Link
 +
| v6.0
 
|}
 
|}
  

Revision as of 15:29, 12 December 2017

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

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

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 v6.0
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

The list of supported Excel functions can be downloaded:

Retrieved from ‘https://help.fluencexl.com/index.php?title=Formula_Reference&oldid=8348’