Selecting Data from an OLAP Cube with the CubeDataSource Component

You can specify an MDX query for the CubeDataSource component to execute by setting its SelectCommand property.

The following example demonstrates an MDX query that retrieves a result set consisting of product sales grouped by category and subcategory:

select non empty { [Measures].[Sales Amount] } on columns,
    non empty { [Product].[Category].[Category] *
    [Product].[Subcategory].[Subcategory] } on rows
from [Adventure Works]

The next code sample illustrates how to set the ConnectionString and SelectCommand properties of a CubeDataSource component to retrieve the data from the above MDX query:

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]";

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]"

The CubeDataSource component does not impose any restrictions over the format of the MDX query. However, to ensure that the retrieved data is in a proper format for visualization you should follow the next rule: specify the measures you want to aggregate in the column axis and the dimensions to group by in the row axis of the query; also avoid using more than two axes in a single query.

If you need to visualize the data grouped by rows and columns you can use the Crosstab report item. Specify all the measures you want aggregated inside the Crosstab body in the column axis and all the grouping dimensions in the row axis of your MDX query. You can distribute the groups by rows and columns in the Crosstab later, when configuring the Crosstab layout from Crosstab Wizard or in Report Designer.

The default fields returned by an MDX query often are too long and might need to be escaped when used in expressions. To overcome this it is advised to assign shorter and more descriptive aliases for them. Use the Mappings collection of the CubeDataSource component to specify mappings between the fields of the data source and their corresponding aliases for use in expressions as illustrated in the following code sample:

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]";
cubeDataSource.Mappings.Add("[Measures].[Sales Amount]", "Sales");
cubeDataSource.Mappings.Add("[Product].[Category].[Category].[MEMBER_CAPTION]", "Category");
cubeDataSource.Mappings.Add("[Product].[Subcategory].[Subcategory].[MEMBER_CAPTION]", "Subcategory");

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]"
cubeDataSource.Mappings.Add("[Measures].[Sales Amount]", "Sales")
cubeDataSource.Mappings.Add("[Product].[Category].[Category].[MEMBER_CAPTION]", "Category")
cubeDataSource.Mappings.Add("[Product].[Subcategory].[Subcategory].[MEMBER_CAPTION]", "Subcategory")