Difference between revisions of "Formula Reference"

(→‎OLAP Formulae)
m (→‎OLAP Formulae)
 
(31 intermediate revisions by 8 users not shown)
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
 +
|-
 +
| [[XL3DoWriteback2]]
 +
| Optionally writes a value to a specific Anaplan module cross-section
 +
| v2409
 
|-
 
|-
 
| [[XL3HierarchyLookup]]
 
| [[XL3HierarchyLookup]]
 
| Returns the Dimension name
 
| Returns the Dimension name
 +
| v6.0
 +
|-
 +
| [[XL3HierarchyRoleLookup]]
 +
| Returns the Hierarchy name for a given Role
 +
| v2308.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
 +
|-
 +
| [[XL3MemberFromLookup]]
 +
| Returns a member using a text cell value
 +
| v9.2
 
|-
 
|-
 
| [[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
 
|-
 
|-
−
| [[XL3RunSQL]]
+
| [[XL3SetProperty]]
−
| Runs specified SQL
+
| 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]]
 +
| Returns the member uniquename for the specified grid cell
 +
| v7.2
 +
|-
 +
|[[XL3GridArrayLookup]]
 +
| Returns an array containing the result of a query.
 +
| v9.1
 +
|}
 +
 +
==Tabular Formulae==
 +
{| class="wikitable"
 +
| width="200" | [[XL3LookupTabular]]
 +
| width="400" | Returns the value of a measure for a given filters
 +
| width="100" | v7.0
 
|}
 
|}
  
Line 76: Line 125:
 
| 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]]
 +
| 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
 +
| v6.1
 +
|-
 +
| [[XL3SparkDotPlot]]
 +
| Creates a dot plot chart that is rendered in an Excel cell
 +
| v6.1
 +
|-
 +
| [[XL3SparkLolliplot]]
 +
| Creates a lollipop chart that is rendered in an Excel cell
 +
| v7.0
 +
|-
 +
| [[XL3SparkHeatMap]]
 +
| Creates a heatmap section that is rendered in an Excel cell
 +
| v8.0
 +
|-
 +
| [[XL3SparkCompare]]
 +
| Creates a comparison chart that is rendered in an Excel cell
 +
| v10.0
 
|}
 
|}
  
−
==Query Table Formulae==
+
==Relational Formulae==
 
{| class="wikitable"
 
{| class="wikitable"
 
| 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]]
 +
| 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
 +
|-
 +
|[[XL3TableArrayLookup]]
 +
| Returns an array containing the result of a query.
 +
| v9.1
 
|}
 
|}
  
Line 133: Line 235:
 
| 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
 +
|-
 +
| [[XL3SheetVisible]]
 +
| Shows or hides a sheet
 +
|
 
|-
 
|-
 
| [[XL3DynamicPicture]]
 
| [[XL3DynamicPicture]]
 
| Updates a Picture based on a URL (web address)
 
| 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 FluenceXL Web report and optionally sets web parameters
 +
| v6.5
 +
|-
 +
| [[XL3IsWeb]]
 +
| Returns True if report is running in FluenceXLWeb 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
 +
|-
 +
| [[XL3ReportInfo]]
 +
| Returns information about the published report (ReportName, PublishedBy etc.) (web only)
 +
| v9.1
 +
|}
 +
 +
==Comment Formulae==
 +
{| class="wikitable"
 +
| width="200" | [[XL3CubeComment]]
 +
| width="400" | Get the comments for a particular cube cell
 +
| width="100" | v8.1
 +
|-
 +
| [[XL3GridComment]]
 +
| Get the comments for a [[Grid]]
 +
| v8.1
 
|}
 
|}
  
 
==Formula Wizards==
 
==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.
+
The FluenceXL Excel Add-in provides a menu-driven, graphical user interface to facilitate easy building of reports. The reports are built by combining FluenceXL functions on a spreadsheet page. The following functions are supported by the GUI.
 
{| class="wikitable"
 
{| class="wikitable"
 
! scope="col" width="200" | Ribbon/Menu option
 
! scope="col" width="200" | Ribbon/Menu option
 
! scope="col" width="200" | Function
 
! scope="col" width="200" | Function
 +
! scope="col" width="100" | Version
 
|-
 
|-
 
| [[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
 
|}
 
|}
 +
 +
==Excel Formulae==
 +
See [[Supported Excel Formulae]] for a full list. The list of supported Excel functions can also be downloaded:
 +
* [http://www.xlcubed.com/downloads/FluenceXLV65Web-ExcelFunctionSupport.pdf Version 7]
 +
* [http://www.xlcubed.com/downloads/FluenceXLV8Web-ExcelFunctionSupport.pdf Version 8]
  
 
[[Category:Formula Reporting]]
 
[[Category:Formula Reporting]]
 
[[Category:Formulae]]
 
[[Category:Formulae]]

Latest revision as of 15:57, 30 October 2024

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
XL3DoWriteback2 Optionally writes a value to a specific Anaplan module cross-section v2409
XL3HierarchyLookup Returns the Dimension name v6.0
XL3HierarchyRoleLookup Returns the Hierarchy name for a given Role v2308.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
XL3MemberFromLookup Returns a member using a text cell value v9.2
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
XL3GridArrayLookup Returns an array containing the result of a query. v9.1

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 v6.1
XL3SparkDotPlot Creates a dot plot chart that is rendered in an Excel cell v6.1
XL3SparkLolliplot Creates a lollipop chart that is rendered in an Excel cell v7.0
XL3SparkHeatMap Creates a heatmap section that is rendered in an Excel cell v8.0
XL3SparkCompare Creates a comparison chart that is rendered in an Excel cell v10.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
XL3TableArrayLookup Returns an array containing the result of a query. v9.1

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
XL3SheetVisible Shows or hides a sheet
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 FluenceXL Web report and optionally sets web parameters v6.5
XL3IsWeb Returns True if report is running in FluenceXLWeb 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
XL3ReportInfo Returns information about the published report (ReportName, PublishedBy etc.) (web only) v9.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 FluenceXL Excel Add-in provides a menu-driven, graphical user interface to facilitate easy building of reports. The reports are built by combining FluenceXL functions on a spreadsheet page. The following functions are supported by the GUI.

Ribbon/Menu option Function Version
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

See Supported Excel Formulae for a full list. The list of supported Excel functions can also be downloaded:

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