Contents x
    EvaluateSqlDatasourceExpression
    • 28 Mar 2025
    • 1 Minute to read
    • Dark
      Light
    Contents

    EvaluateSqlDatasourceExpression

    • Updated on 28 Mar 2025
    • 1 Minute to read
    • Dark
      Light
    Article summary

    General

    This is available in version 3.5.16 and later.

    The EvaluateSqlDatasourceExpression function evaluates a SQL expression against a SQL Datasource and can return the result in a number of different formats which can be useful, for example, in repeaters or cycle groups.

    Syntax

    The syntax of the EvaluateSqlDatasourceExpression function is:

    EvaluateSqlDatasourceExpression Function Syntax

    EvaluateSqlDatasourceExpression ("datasource_name", "sql_query", responseFormat)

    responseFormatDescription
    0Scalar - Returns either a scalar value or the first cell of a table.
    1ListOfSimpleRows - Returns a list where each entry is an object which can be used to get the rows value by either index or column name (see below).
    2ListFromSingleColumn - You can use this if your SQL returns a table with a single column of data. In this case a list of values will be returned which can be used, for example, in a repeater.

    Example 1

    The following expression returns a collection of rows from a table containing reciepients for a report:

    EvaluateSqlDatasourceExpression( "Sales & Returns Sample ODBC PowerBI", "SELECT * FROM [M&D Demo_Supporting Report].[Model].[Store Managers] ", 1 )

    Lets assume you have used this expression in a cycle action and you have configured the Value Name for this to be 'user'. Inside your cycle group you can use these to access the values in the row for the current iteration:

    These would both return the Name:

    [CycleValue].[user].Value("Name")
    [CycleValue].[user].Value(1)

    This would return a comma separated list of the values in the row:

    [CycleValue].[user].Value.ToCsv()

    Example 2

    The following expression returns a list of the region names in the database :

    EvaluateSqlDatasourceExpression Function Example

    EvaluateSqlDatasourceExpression("Snowflake", "SELECT REGION_NAME FROM REGION", 2)

    Was this article helpful?
    What's Next