Specifying Query Parameters with the CubeDataSource Component

The CubeDataSource component supports parameterized MDX queries by associating the parameters you add to the Parameters collection with placeholders in the query. Parameter values can be evaluated with any expression which conforms to the common expression syntax supported by the reporting engine. This grants you a great deal of flexibility on how you can supply your MDX queries with parameters. For example, you can bind an MDX query parameter directly to a report parameter.

The CubeDataSource Wizard can detect parameters listed in the MDX query, and it will ask you to provide values for them at Configure Data Source Parameters step.

When creating a parameterized query, you identify the parameter name by prefixing the name with the "@" character. For example, "@Year" would be a valid parameter name. MDX supports only parameters for literal or scalar values. To create a parameter that references a member, set, or tuple, you would have to use a function such as StrToMember or StrToSet. To illustrate this, let us define a simple MDX query that selects product sales grouped by category and subcategory, and define a slicing axis by year, where the chosen year is specified as a parameter. Since the "@Year" parameter is passed as a string literal value, we need to use the StrToMember function to convert that value to a valid member of the cube as shown below:

select non empty { [Measures].[Sales Amount] } on columns,
    non empty { [Product].[Category].[Category] *
    [Product].[Subcategory].[Subcategory] } on rows
from [Adventure Works]
where StrToMember(@Year)

The following code sample illustrates how to pass a value to the "@Year" parameter of the query using the Parameters collection of the CubeDataSource component:

Telerik.Reporting.CubeDataSource cubeDataSource = new Telerik.Reporting.CubeDataSource();

cubeDataSource.ConnectionString = "MyAdventureWorksDW";
cubeDataSource.SelectCommand = "select non empty { [Measures].[Sales Amount] } on columns, " +
                               "       non empty { [Product].[Category].[Category] * " +
                               "                   [Product].[Subcategory].[Subcategory] } on rows " +
                               "from [Adventure Works] " +
                               "where StrToMember(@Year)";
cubeDataSource.Parameters.Add("Year", "[CY 2001]");

Dim cubeDataSource As Telerik.Reporting.CubeDataSource = New Telerik.Reporting.CubeDataSource()

cubeDataSource.ConnectionString = "MyAdventureWorksDW"
cubeDataSource.SelectCommand = "select non empty { [Measures].[Sales Amount] } on columns, " & _
                               "       non empty { [Product].[Category].[Category] * " & _
                               "                   [Product].[Subcategory].[Subcategory] } on rows " & _
                               "from [Adventure Works] " & _
                               "where StrToMember(@Year)"
cubeDataSource.Parameters.Add("Year", "[CY 2001]")

When you need to pass multiple values as a single parameter to the query, use the StrToSet function instead. For example, to pass several years for the slicing axis at once, we can modify the previous query as shown below:

select non empty { [Measures].[Sales Amount] } on columns,
    non empty { [Product].[Category].[Category] *
    [Product].[Subcategory].[Subcategory] } on rows
from [Adventure Works]
where StrToSet(@Year)

Now it is possible to assign an array of years to the data source parameter:

Telerik.Reporting.CubeDataSource cubeDataSource = new Telerik.Reporting.CubeDataSource();

cubeDataSource.ConnectionString = "MyAdventureWorksDW";
cubeDataSource.SelectCommand = "select non empty { [Measures].[Sales Amount] } on columns, " +
                               "       non empty { [Product].[Category].[Category] * " +
                               "                   [Product].[Subcategory].[Subcategory] } on rows " +
                               "from [Adventure Works] " +
                               "where StrToSet(@Year)";
cubeDataSource.Parameters.Add("Year", new string[] { "[CY 2001]", "[CY 2002]" });

Dim cubeDataSource As Telerik.Reporting.CubeDataSource = New Telerik.Reporting.CubeDataSource()

cubeDataSource.ConnectionString = "MyAdventureWorksDW"
cubeDataSource.SelectCommand = "select non empty { [Measures].[Sales Amount] } on columns, " & _
                               "       non empty { [Product].[Category].[Category] * " & _
                               "                   [Product].[Subcategory].[Subcategory] } on rows " & _
                               "from [Adventure Works] " & _
                               "where StrToSet(@Year)"
cubeDataSource.Parameters.Add("Year", New String() {"[CY 2001]", "[CY 2002]"})

Instead of specifying the parameter values directly, you can use expressions to evaluate them at runtime. For instance, you can bind the CubeDataSourceParameter from the previous example to a ReportParameter with an expression, so that the default parameter user interface of Report Viewer can be used for choosing the slicing year:

var cubeDataSource = new Telerik.Reporting.CubeDataSource();

cubeDataSource.ConnectionString = "MyAdventureWorksDW";
cubeDataSource.SelectCommand = "select non empty { [Measures].[Sales Amount] } on columns, " +
                               "       non empty { [Product].[Category].[Category] * " +
                               "                   [Product].[Subcategory].[Subcategory] } on rows " +
                               "from [Adventure Works] " +
                               "where StrToMember(@Year)";
cubeDataSource.Parameters.Add("Year", "=Parameters.Year.Value");

var report = new Report1();

report.DataSource = cubeDataSource;
report.ReportParameters.Add("Year", ReportParameterType.String, "[CY 2001]");

Dim cubeDataSource As Telerik.Reporting.CubeDataSource = New Telerik.Reporting.CubeDataSource()

cubeDataSource.ConnectionString = "MyAdventureWorksDW"
cubeDataSource.SelectCommand = "select non empty { [Measures].[Sales Amount] } on columns, " & _
                               "       non empty { [Product].[Category].[Category] * " & _
                               "                   [Product].[Subcategory].[Subcategory] } on rows " & _
                               "from [Adventure Works] " & _
                               "where StrToMember(@Year)"
cubeDataSource.Parameters.Add("Year", "=Parameters.Year.Value")

Dim report As New Report1()

report.DataSource = cubeDataSource
report.ReportParameters.Add("Year", ReportParameterType.String, "[CY 2001]")