MDX Functions: Conversion

SQL Server Analysis Services - Multidimensional Modeling

Conversion Functions

Conversion functions in MDX are used to convert values from one data type to another. This is essential for performing calculations, comparisons, and manipulating data when different data types are involved.

Overview

MDX supports conversion between various data types, including strings, numbers, dates, and Booleans. These functions are particularly useful when dealing with data from different sources or when you need to format data for display or further processing.

Common Conversion Functions

Function Details and Examples

CStr()

Converts any valid MDX expression to a string data type.

Example:

-- Convert a numeric value to a string
SELECT
    {
        CStr([Measures].[Internet Sales Amount])
    } ON COLUMNS
FROM [Adventure Works DW]

CDbl()

Converts any valid MDX expression to a double-precision floating-point number data type.

Example:

-- Convert a string representation of a number to a double
SELECT
    {
        CDbl("123.45") + 10
    } ON COLUMNS
FROM [Adventure Works DW]

CLng()

Converts any valid MDX expression to a long integer data type.

Example:

-- Convert a decimal value to a long integer
SELECT
    {
        CLng([Measures].[Quantity])
    } ON COLUMNS
FROM [Adventure Works DW]

CDate()

Converts any valid MDX expression to a date/time data type.

Example:

-- Convert a string to a date
SELECT
    {
        CDate("2023-10-27")
    } ON COLUMNS
FROM [Adventure Works DW]

CBool()

Converts any valid MDX expression to a Boolean data type (True or False).

Example:

-- Convert a numeric value to a Boolean
SELECT
    {
        CBool(1), CBool(0)
    } ON COLUMNS
FROM [Adventure Works DW]

Val()

Extracts numeric characters from a string and converts them into a numeric value.

Example:

-- Extract numeric value from a string
SELECT
    {
        Val("Product ID: 12345")
    } ON COLUMNS
FROM [Adventure Works DW]

Format()

Formats a value to a specified format. This is very powerful for presentation.

Example:

-- Format a sales amount with currency and two decimal places
SELECT
    {
        Format([Measures].[Internet Sales Amount], "$#,##0.00")
    } ON COLUMNS
FROM [Adventure Works DW]
-- Format a date
SELECT
    {
        Format(Now(), "yyyy-MM-dd HH:mm:ss")
    } ON COLUMNS
FROM [Adventure Works DW]

Important Considerations