10 Custom
Excel Functions
REST API
Connectivity
OAuth 2.0
Per-User Security
PAXCEL® DOCUMENTATION
Custom Functions Reference
PAXcel™ extends Excel with 10 custom functions that connect directly to IBM Planning Analytics (TM1) via REST API. Read data, write plan values, query dimensions, and run MDX — all from native Excel formulas that work in Excel for Web, Windows, and Mac.
- 10 custom functions for read, write, bi-directional, and discovery
- Works in Excel for Web, Windows, and Mac
- REST API connectivity with OAuth 2.0 security
- Native Excel formulas with cell reference support
PAXCEL.HELLOConnection Test
Test function — returns a static string to verify the add-in is loaded. Use this to confirm PAXcel is active in your Excel session before running other queries.
SYNTAX
=PAXCEL.HELLO()Parameters: None
Returns: Static string confirming the add-in is loaded
Example:
=PAXCEL.HELLO()
→ Returns: "PAXcel is loaded"
PAXCEL.SERVERServer Info
Returns the connection alias of the active TM1 server. Use this to verify which server you’re connected to, or to dynamically reference the server name in other formulas.
SYNTAX
=PAXCEL.SERVER()Parameters: None
Returns: Connection alias string of the active TM1 server
Examples:
=PAXCEL.SERVER()
→ Returns: "Planning_Analytics_Prod"
Tip: Use in combination with other functions:
=PAXCEL.CUBES(PAXCEL.SERVER())
PAXCEL.CUBESDiscovery
Lists all cube names from the connected TM1 server. Returns a spilling array of cube names for discovery and validation.
SYNTAX
=PAXCEL.CUBES(serverName)Parameters:
serverName (optional) — Database server name for identification
Returns: Dynamic spilling array of cube names
Example:
=PAXCEL.CUBES("Planning_Analytics_Prod")
→ Returns: ["Finance_GL", "Revenue_Cube", "Workforce", "CapEx", ...]
PAXCEL.DIMENSIONSDiscovery
Lists all dimension names for a TM1 cube. Use this to understand cube structure before building GET or VIEW queries.
SYNTAX
=PAXCEL.DIMENSIONS(serverName, cubeName)Parameters:
serverName (required) — Database server name
cubeName (required) — Name of the TM1 cube
Returns: Dynamic spilling array of dimension names
Example:
=PAXCEL.DIMENSIONS("Planning_Analytics_Prod", "Revenue_Cube")
→ Returns: ["Period", "Version", "Region", "Account", "Measure"]
PAXCEL.GETRead / Write
Get a cell value from TM1. Requests are batched for performance (like DBRW). Supports three calling modes: cache mode with a VIEW reference, paired mode with Dim::Hier notation, and bare element mode. This bi-directional function enables full read/write planning workflows when used with PAXCEL.SEND.
SYNTAX
=PAXCEL.GET(args...)Parameters: (repeating — three calling modes)
Mode 1 — Cache:
ViewRef, Elem1, Elem2, …
Use a cell reference to a PAXCEL.VIEW result for instant cached lookups
Mode 2 — Paired:
Server, Cube, “Dim::Hier”, Elem, …
Explicitly specify dimension and hierarchy for each element
Mode 3 — Bare:
Server, Cube, Elem1, Elem2, …
Elements in cube dimension order (like classic DBRW)
Returns: Single numeric or string cell value
Examples:
=PAXCEL.GET(A1, "North America", "Revenue", "Q4")
→ Cache mode — A1 references a PAXCEL.VIEW cell
=PAXCEL.GET("Prod", "Revenue_Cube", "Region::Region", "North America", "Period::Period", "Q4")
→ Paired mode — Dim::Hier notation
=PAXCEL.GET("Prod", "Revenue_Cube", "2026", "Forecast", "North America", "Revenue")
→ Bare mode — elements in cube dimension order
PAXCEL.VIEWRead Data
Load a TM1 view into cache for instant PAXCEL.GET lookups, or render a full cube view in Excel. Supports named views (saved in TM1) and dynamic views built with axis definitions. Use Dim:Subset for named subsets, just Dim for all members, or Dim.Hier:Subset for alternative hierarchies.
SYNTAX
=PAXCEL.VIEW(serverName, cubeName, viewName, dynamicParams...)Parameters:
serverName (required) — Database server name
cubeName (required) — Name of the TM1 cube
viewName (required) — Name of saved view, or empty string “” for dynamic mode
dynamicParams (repeating, optional) — Axis definitions:
rows=Dim1:Subset1,Dim2 — row axis
cols=Dim3:Subset3 — column axis
where=Dim4:Element — fixed slicer
Returns: Spilling grid with view data (named view) or cache reference string (dynamic view)
Named view:
=PAXCEL.VIEW("Prod", "Revenue_Cube", "Q4_Forecast")
→ Loads saved view “Q4_Forecast”
Dynamic view:
=PAXCEL.VIEW("Prod", "Revenue_Cube", "", "rows=Region,Account", "cols=Period:Q_Periods", "where=Version:Forecast")
→ Dynamic view with Region/Account on rows, Period subset on columns, Forecast filter
PAXCEL.ATTRMetadata
Retrieves an attribute value from a TM1 dimension element. Supports alternative hierarchies for dimension elements with multiple organizational structures.
SYNTAX
=PAXCEL.ATTR(serverName, dimension, hierarchy, element, attribute)Parameters:
serverName (required) — Database server name
dimension (required) — Dimension name
hierarchy (required) — Hierarchy name (use dimension name if default)
element (required) — Element name
attribute (required) — Attribute name
Returns: String or numeric attribute value
Example:
=PAXCEL.ATTR("Prod", "Account", "Account", "4100", "Description")
→ Returns: "Net Revenue - Product Sales"
PAXCEL.MDXAdvanced
Executes an MDX query against TM1 and returns the result as a 2D grid. For power users who need full control over data retrieval — custom filters, calculated members, and complex slicing.
SYNTAX
=PAXCEL.MDX(serverName, mdxQuery)Parameters:
serverName (required) — Database server name
mdxQuery (required) — MDX SELECT statement
Returns: Dynamic spilling 2D grid with query results
Example:
=PAXCEL.MDX("Prod", "SELECT {[Period].[Q1],[Period].[Q2]} ON COLUMNS, {[Region].Members} ON ROWS FROM [Revenue_Cube] WHERE ([Version].[Forecast],[Measure].[Revenue])")
→ Returns Revenue by Region and Period for Forecast version
PAXCEL.SUBSETDiscovery
Returns elements of a TM1 subset, or all members if subset is blank. Supports alternative hierarchies and an optional attribute column for element descriptions or aliases.
SYNTAX
=PAXCEL.SUBSET(serverName, dimension, hierarchy, subsetName, attribute)Parameters:
serverName (required) — Database server name
dimension (required) — Dimension name
hierarchy (required) — Hierarchy name (use dimension name if unsure)
subsetName (optional) — Subset name, or “” for all members
attribute (optional) — Adds a second column with attribute values
Returns: Dynamic spilling array of element names (with optional attribute column)
Examples:
=PAXCEL.SUBSET("Prod", "Region", "Region", "Active_Regions")
→ Returns: ["North America", "EMEA", "APAC", "LATAM"]
=PAXCEL.SUBSET("Prod", "Region", "Region", "", "Caption")
→ All members with Caption attribute in second column
PAXCEL.SENDWrite Data
Write a value to a TM1 cell. Writes are batched for performance (like DBSW). The last argument is always the value to write. Supports paired mode with Dim::Hier notation and bare element mode. Returns the written value on success.
SYNTAX
=PAXCEL.SEND(args..., value)Parameters: (repeating — two modes, value is always last)
Mode 1 — Paired:
Server, Cube, “Dim::Hier”, Elem, …, Value
Explicit dimension and hierarchy for each element
Mode 2 — Bare:
Server, Cube, Elem1, Elem2, …, Value
Elements in cube dimension order, value last
Returns: The written value on success, or error message
⚠ IMPORTANT: PAXCEL.SEND writes data to your Planning Analytics model. The user must have write access to the target cell. All writes are logged in the TM1 audit trail. TM1 security rules apply per-user based on OAuth 2.0 authentication.
Bare mode:
=PAXCEL.SEND("Prod", "Revenue_Cube", "2026", "Forecast", "North America", "Revenue", "Q4", 4890000)
→ Writes 4,890,000 and returns 4890000
Paired mode:
=PAXCEL.SEND("Prod", "Revenue_Cube", "Region::Region", "North America", "Period::Period", "Q4", 4890000)
→ Writes with explicit Dim::Hier mapping
READY TO GET STARTED
See PAXcel™ Functions in Action
See these functions working live with your own IBM Planning Analytics environment. Free trial available on Microsoft AppSource.