Difference between revisions of "Formula Reference"
(→Report Management Formulae) |
|||
| Line 172: | Line 172: | ||
| [[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. | ||
| + | |} | ||
| + | |||
| + | ==Comment Formulae== | ||
| + | {| class="wikitable" | ||
| + | | width="200" | [[XL3CubeComment]] | ||
| + | | width="400" | Get the comments for a particular cube cell | ||
| + | |- | ||
| + | | [[XL3GridComment]] | ||
| + | | Get the comments for a [[Grid]] | ||
|} | |} | ||
Revision as of 14:46, 9 May 2016
Contents
[hide]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 |
| XL3SetProperty | Allows the updating of object types properties |
| 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 |
| XL3GridMember | Returns the member uniquename for the specified grid cell |
Tabular Formulae
| XL3LookupTabular | Returns the value of a measure for a given filters |
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 |
| XL3SparkHeatMap | Creates a set of coloured icons suitable for use as a heatmap |
| 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 |
Relational 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 |
| XL3RunSQL | Runs specified SQL |
| XL3RunSQLProc | Runs specified Stored Procedure |
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) |
| XL3RefreshObjects | Refreshes objects in the report based on type |
| XL3RefreshSheetObjects | Refreshes objects in the report based on type |
| XL3RefreshObjectsNamed | Refreshes objects in the report based on name |
| XL3WebReportLink | Creates a hyperlink that opens an XLCubed Web report and optionally sets web parameters. |
Comment Formulae
| XL3CubeComment | Get the comments for a particular cube cell |
| XL3GridComment | Get the comments for a Grid |
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 |
Excel Formulae
The list of supported Excel functions can be downloaded:
