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

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: