Numeric Functions
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
ORDINAL
Description: Returns the zero-based ordinal (position) of a level within its hierarchy.
Syntax: Level_Expression.Ordinal
Input: A level expression
Output: Integer ordinal (0 = root/All level)
Business Use Case: Determine the depth of a hierarchy level for conditional logic.
WITH MEMBER [Measures].[Level Depth] AS
[Product].[Product Categories].CurrentMember.Level.Ordinal
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Level Depth]} ON COLUMNS,
[Product].[Product Categories].[Subcategory].Members ON ROWS FROM [Adventure Works]Int
Description: Truncates a numeric expression to an integer (floor for positive numbers).
Syntax: Int(Numeric_Expression)
Input: A numeric expression
Output: Integer value (decimal portion dropped)
Business Use Case: Convert fractional unit quantities to whole numbers.
WITH MEMBER [Measures].[Integer Sales] AS Int([Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Integer Sales]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]CInt
Description: Converts an expression to an Integer, rounding to the nearest integer (unlike Int which truncates).
Syntax: CInt(Numeric_Expression)
Input: Any numeric or string-numeric expression
Output: Rounded integer value
WITH MEMBER [Measures].[Rounded Units] AS CInt([Measures].[Internet Sales Amount] / 1000)
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Rounded Units]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]CStr
Description: Converts a numeric or date value to a string.
Syntax: CStr(Expression)
Input: Any numeric or date expression
Output: String representation
Business Use Case: Concatenate member names with numeric values for display labels.
WITH MEMBER [Measures].[Sales Label] AS [Product].[Product Categories].CurrentMember.Name
+ ": $"
+ CStr(Int([Measures].[Internet Sales Amount])) SELECT
[Measures].[Sales Label] ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]LEVEL_NUMBER
Description: Returns the zero-based ordinal number of the level that a member belongs to.
Syntax: Member_Expression, Level, Ordinal (same as ORDINAL on the member's level)
Business Use Case: Apply conditional formatting based on whether a row is a category vs subcategory.
WITH MEMBER [Measures].[Level Number] AS
[Product].[Product Categories].CurrentMember.Level.Ordinal SELECT
{[Measures].[Internet Sales Amount], [Measures].[Level Number]} ON COLUMNS,
Hierarchize(
{[Product].[Product Categories].[Category].Members, [Product].[Product Categories].[Subcategory].Members}) ON ROWS
FROM [Adventure Works]