Difference between revisions of "Formula Reference"
(→Report Management Formulae) |
m (→OLAP Formulae) |
||
| (3 intermediate revisions by 3 users not shown) | |||
| Line 16: | Line 16: | ||
| Optionally writes a value to a specific cube cross-section | | Optionally writes a value to a specific cube cross-section | ||
| v6.1 | | 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 | | v6.0 | ||
| + | |- | ||
| + | | [[XL3HierarchyRoleLookup]] | ||
| + | | Returns the Hierarchy name for a given Role | ||
| + | | v2308.0 | ||
|- | |- | ||
| [[XL3KpiLookup]] | | [[XL3KpiLookup]] | ||
| Line 262: | Line 270: | ||
|- | |- | ||
| [[XL3WebReportLink]] | | [[XL3WebReportLink]] | ||
| − | | Creates a hyperlink that opens an | + | | Creates a hyperlink that opens an FluenceXL Web report and optionally sets web parameters |
| v6.5 | | v6.5 | ||
|- | |- | ||
| [[XL3IsWeb]] | | [[XL3IsWeb]] | ||
| − | | Returns True if report is running in | + | | Returns True if report is running in FluenceXLWeb and False if the report is running in Excel |
| v7.5 | | v7.5 | ||
|- | |- | ||
| Line 290: | Line 298: | ||
==Formula Wizards== | ==Formula Wizards== | ||
| − | The | + | 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 | ||
| Line 323: | Line 331: | ||
==Excel Formulae== | ==Excel Formulae== | ||
See [[Supported Excel Formulae]] for a full list. The list of supported Excel functions can also be downloaded: | See [[Supported Excel Formulae]] for a full list. The list of supported Excel functions can also be downloaded: | ||
| − | * [http://www.xlcubed.com/downloads/ | + | * [http://www.xlcubed.com/downloads/FluenceXLV65Web-ExcelFunctionSupport.pdf Version 7] |
| − | * [http://www.xlcubed.com/downloads/ | + | * [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
Contents
[hide]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:
