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