How to: Filter Dashboard Item's Data in OLAP Mode

• Aug 01, 2019
• 2 minutes to read

The following example demonstrates how to apply filtering to a dashboard item’s data in OLAP mode.

In this example, constant values and dashboard parameters are passed to a filter string to filter a dashboard item’s data.

In OLAP mode, a limited number of criteria operators are supported. This example shows how to use the supported criteria operators.

View Example

Form1.vb

Imports DevExpress.XtraBars.Ribbon
Imports DevExpress.DashboardCommon
Imports DevExpress.Data.Filtering

Namespace Dashboard_OlapFiltering
    Partial Public Class Form1
        Inherits RibbonForm

        Public Sub New()
            InitializeComponent()
            dashboardDesigner1.CreateRibbon()
            Dim dashboard As New Dashboard()
            dashboard.LoadFromXml("..\..\Data\Dashboard.xml")
            Dim pivot1 As PivotDashboardItem = CType(dashboard.Items(0), PivotDashboardItem)
            Dim olapDataSource As DashboardOlapDataSource =
                CType(dashboard.DataSources(0), DashboardOlapDataSource)

            Dim fieldYearName As String = "[Date].[Calendar].[Calendar Year]"
            Dim year2001 As String = "[Date].[Calendar].[Calendar Year].&[2001]"
            Dim year2002 As String = "[Date].[Calendar].[Calendar Year].&[2002]"

            Dim fieldCountryName As String = "[Customer].[Country].[Country]"
            Dim countryCanada As String = "[Customer].[Country].&[Canada]"

            Dim settings As New DynamicListLookUpSettings()
            settings.DataSource = olapDataSource
            settings.ValueMember = fieldCountryName
            Dim parameter1 As New DashboardParameter("Parameter1", _
                                  GetType(String), countryCanada, "Category", True, settings)
            dashboard.Parameters.Add(parameter1)

            Dim filterCriteria As CriteriaOperator = GroupOperator.And( _
                New BinaryOperator(New OperandProperty(fieldCountryName), _
                                   New OperandParameter(parameter1.Name), _
                                   BinaryOperatorType.Equal), _
                New NotOperator(New InOperator(New OperandProperty(fieldYearName), _
                                               New ConstantValue() {New ConstantValue(year2001), _
                                                                    New ConstantValue(year2002)})))

            pivot1.FilterString = filterCriteria.ToString()
            dashboardDesigner1.Dashboard = dashboard
        End Sub
    End Class
End Namespace

Form1.cs

using DevExpress.XtraBars.Ribbon;
using DevExpress.DashboardCommon;
using DevExpress.Data.Filtering;

namespace Dashboard_OlapFiltering {
    public partial class Form1 : RibbonForm {
        public Form1() {
            InitializeComponent();
            dashboardDesigner1.CreateRibbon();
            Dashboard dashboard = new Dashboard();
            dashboard.LoadFromXml(@"..\..\Data\Dashboard.xml");
            PivotDashboardItem pivot1 = (PivotDashboardItem)dashboard.Items[0];
            DashboardOlapDataSource olapDataSource = (DashboardOlapDataSource)dashboard.DataSources[0];

            string fieldYearName = "[Date].[Calendar].[Calendar Year]";
            string year2001 = "[Date].[Calendar].[Calendar Year].&[2001]";
            string year2002 = "[Date].[Calendar].[Calendar Year].&[2002]";

            string fieldCountryName = "[Customer].[Country].[Country]";
            string countryCanada = "[Customer].[Country].&[Canada]";

            DynamicListLookUpSettings settings = new DynamicListLookUpSettings();
            settings.DataSource = olapDataSource;
            settings.ValueMember = fieldCountryName;
            DashboardParameter parameter1 = new DashboardParameter("Parameter1", 
                typeof(string), countryCanada, "Category", true, settings);
            dashboard.Parameters.Add(parameter1);

            CriteriaOperator filterCriteria = GroupOperator.And(
                new BinaryOperator(new OperandProperty(fieldCountryName), 
                                   new OperandParameter(parameter1.Name), 
                                   BinaryOperatorType.Equal),
                new NotOperator(new InOperator(new OperandProperty(fieldYearName), 
                                               new ConstantValue[] { new ConstantValue(year2001),
                                                                     new ConstantValue(year2002) 
                                                                   })));

            pivot1.FilterString = filterCriteria.ToString();
            dashboardDesigner1.Dashboard = dashboard;
        }
    }
}