Implementing OLAP Grids

The Syncfusion WPF OLAP Grid is a presentation-quality business control that reads OLAP data sources to create multi-dimensional views for analyzing business data. It provides drill up/down capabilities, multiple layout options, conditional formatting, and comprehensive export features for business intelligence applications.

When to Use This Skill

Use this skill when you need to:

• Display OLAP data from SSAS (SQL Server Analysis Services) or offline cubes in WPF applications
• Create multi-dimensional analysis views with rows, columns, and measure intersections
• Implement drill down/up navigation through hierarchical dimension data
• Build business intelligence dashboards with KPI visualization and analytical capabilities
• Configure different grid layouts (Normal, Excel-like, Top Summary, No Summaries)
• Apply conditional formatting to highlight data based on business rules
• Export OLAP analysis to Excel, Word, PDF, or CSV formats
• Connect to cube data sources and define custom OLAP reports
• Display member properties alongside dimension members
• Implement paging for large OLAP datasets

This skill covers the complete implementation lifecycle from setup and data binding to advanced features like conditional formatting, export options, and interactive drill operations.

Component Overview

Key Capabilities:

• Multi-dimensional data display from OLAP cubes
• Five grid layout options for different presentation needs
• Multi-level drill up/down for rows and columns
• Conditional formatting with custom cell styles
• Export to Excel, Word, PDF, and CSV
• KPI (Key Performance Indicator) support
• Member properties display
• Paging for large datasets
• Design-time binding with query designer
• Cell selection and hyperlink support

Core Components:

• OlapGrid - Main grid control
• OlapDataManager - Manages OLAP data and reports
• OlapReport - Defines cube query (dimensions, measures, filters)
• DimensionElement - Represents dimension hierarchies
• MeasureElements - Represents measures to display

Install the NuGet Package

To use the OLAP Grid in your WPF application, install the following NuGet package:

Install-Package Syncfusion.OlapGrid.WPF

Assembly References

The OLAP Grid requires the following assemblies:

Assembly Reference

• Syncfusion.Grid.WPF
• Syncfusion.GridCommon.WPF
• Syncfusion.Linq.Base
• Syncfusion.Olap.Base
• Syncfusion.OlapGridCommon.WPF
• Syncfusion.OlapShared.WPF
• Syncfusion.Shared.WPF
• Syncfusion.Tools.WPF

NuGet Package

• Syncfusion.OlapGrid.WPF

Important Note

The Syncfusion OlapGrid control is supported only in .NET Framework WPF applications. It is not supported in .NET Core. Please create a Framework-based WPF sample when working with OlapGrid.

Documentation and Navigation Guide

Getting Started

📄 Read: references/getting-started.md

When you need to:

• Install and configure the OLAP Grid control
• Add required assemblies to WPF projects
• Understand licensing requirements
• Create basic OLAP Grid implementation (XAML, Code-behind, or Blend)
• Set up OlapDataManager and connection strings
• Use design-time binding with Query Designer
• Create your first OLAP report with dimensions and measures

Data Source Configuration

📄 Read: references/data-source-configuration.md

When you need to:

• Configure OLAP data source connections (SSAS or offline cubes)
• Build OlapReport with dimensions and measures
• Specify cube names and connection strings
• Add categorical elements (column axis) and series elements (row axis)
• Configure dimension hierarchies and levels
• Set up measure elements
• Understand OlapDataManager workflow

Grid Layouts

📄 Read: references/grid-layouts.md

When you need to:

• Choose between five layout types (Normal, Excel-like, Top Summary, No Summaries)
• Position summary cells at top or bottom of parent members
• Display member properties with Excel-like layout
• Configure layout property in XAML or code
• Understand summary and child member positioning
• Select appropriate layout for business requirements

Drill Operations

📄 Read: references/drill-operations.md

When you need to:

• Implement drill down/up navigation through hierarchies
• Configure drill position for rows and columns
• Enable multi-level drilling
• Support all level type members
• Handle drill events programmatically
• Manage drill state and navigation patterns
• Optimize drill performance

Conditional Formatting

📄 Read: references/conditional-formatting.md

When you need to:

• Apply cell formatting based on data conditions
• Use OlapGridDataConditionalFormat class
• Define filter criteria with condition types (GreaterThan, LessThan, Equals)
• Combine conditions with predicates (And, Or)
• Customize cell styles (background, foreground, fonts, borders)
• Configure formatting in XAML or code-behind
• Apply multiple conditional formats

Exporting

📄 Read: references/exporting.md

When you need to:

• Export OLAP Grid to Excel (ExportToExcel method)
• Export to Word documents (ExportToWord method)
• Export to PDF files (ExportToPdf method)
• Export to CSV format (GridCsvExport class)
• Preserve formatting in exported documents
• Handle file dialogs and export configuration
• Implement export error handling

Cell Selection

📄 Read: references/cell-selection.md

When you need to:

• Configure cell selection modes
• Handle selection events
• Implement programmatic cell selection
• Customize selection behavior
• Support multi-cell selection
• Manage selection state

KPI and Member Properties

📄 Read: references/kpi-and-member-properties.md

When you need to:

• Display Key Performance Indicators (KPI)
• Configure KPI status and trend visualization
• Show member properties alongside dimensions
• Use Excel-like layout with member properties
• Bind member properties from cube definitions
• Configure OlapReport for KPI display

Advanced Features

📄 Read: references/advanced-features.md

When you need to:

• Implement paging for large OLAP datasets
• Add hyperlink functionality to cells
• Freeze row or column headers
• Customize tooltips for cells
• Use grid style dialog for formatting
• Enable RTL (Right-to-Left) support
• Configure accessibility features

XAML Configuration

📄 Read: references/xaml-configuration.md

When you need to:

• Configure OLAP Grid declaratively in XAML
• Set properties through XAML markup
• Define conditional formats in XAML
• Manage XAML namespaces
• Use styles and templates
• Apply resource management patterns
• Follow XAML best practices

Quick Start Example

Here's a minimal working example to create an OLAP Grid with data from an OLAP cube:

XAML Setup

<Window xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:syncfusion="http://schemas.syncfusion.com/wpf"
        x:Class="MyApp.MainWindow">
    <Grid>
        <syncfusion:OlapGrid Name="olapGrid1" />
    </Grid>
</Window>

Code-Behind

using Syncfusion.Windows.Grid.Olap;
using Syncfusion.Olap.Manager;
using Syncfusion.Olap.Reports;

public partial class MainWindow : Window
{
    private OlapDataManager olapDataManager;
    private string connectionString = "YOUR_END_POINT"; // For e.g - "Data Source=http://<your-ssas-server>/olap/msmdpump.dll;Initial Catalog=Adventure Works DW 2008 SE;";
    
    public MainWindow()
    {
        InitializeComponent();
        
        // Initialize OlapDataManager with connection string
        olapDataManager = new OlapDataManager(connectionString);
        
        // Create and set the OLAP report
        olapDataManager.SetCurrentReport(CreateOlapReport());
        
        // Bind to OlapGrid
        olapGrid1.OlapDataManager = olapDataManager;
        olapGrid1.DataBind();
    }
    
    private OlapReport CreateOlapReport()
    {
        OlapReport report = new OlapReport();
        
        // Set cube name
        report.CurrentCubeName = "Adventure Works";
        
        // Configure column dimension
        DimensionElement columnDimension = new DimensionElement();
        columnDimension.Name = "Customer";
        columnDimension.AddLevel("Customer Geography", "Country");
        
        // Configure measure
        MeasureElements measures = new MeasureElements();
        measures.Elements.Add(new MeasureElement { Name = "Internet Sales Amount" });
        
        // Configure row dimension
        DimensionElement rowDimension = new DimensionElement();
        rowDimension.Name = "Date";
        rowDimension.AddLevel("Fiscal", "Fiscal Year");
        
        // Add to report
        report.CategoricalElements.Add(columnDimension);
        report.CategoricalElements.Add(measures);
        report.SeriesElements.Add(rowDimension);
        
        return report;
    }
}

This creates a basic OLAP Grid showing Internet Sales Amount by Customer Geography (Country) and Fiscal Year.

Common Patterns

Pattern 1: Setting Grid Layout

// Excel-like layout with indented child members
olapGrid1.Layout = GridLayout.ExcelLikeLayout;

// Normal layout with bottom summaries (default)
olapGrid1.Layout = GridLayout.Normal;

// Top summary layout
olapGrid1.Layout = GridLayout.NormalTopSummary;

// Layout without summaries
olapGrid1.Layout = GridLayout.NoSummaries;

Pattern 2: Applying Conditional Formatting

// Create conditional format
OlapGridDataConditionalFormat format = new OlapGridDataConditionalFormat();

// Add conditions
format.Conditions.Add(new OlapGridDataCondition() 
{ 
    ConditionType = OlapGridDataConditionType.GreaterThan,
    MeasureElement = "Internet Sales Amount",
    Value = "2000000",
    PredicateType = PredicateType.And
});

// Set cell style
format.CellStyle = new OlapGridCellStyle() 
{ 
    Background = Brushes.Yellow,
    FontFamily = new FontFamily("Calibri"),
    FontSize = 12
};

// Apply to grid
olapGrid1.ConditionalFormats.Add(format);

Pattern 3: Exporting to Different Formats

// Export to Excel
olapGrid1.ExportToExcel("SalesReport.xlsx");

// Export to Word
olapGrid1.ExportToWord("SalesReport.docx");

// Export to PDF
olapGrid1.ExportToPdf("SalesReport.pdf");

// Export to CSV
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "CSV file (.csv)|*.csv";
if (dialog.ShowDialog() == true)
{
    GridCsvExport csvExport = new GridCsvExport(olapGrid1.InternalGrid.Engine);
    csvExport.Export(dialog.FileName);
}

Pattern 4: Multiple Dimensions and Measures

private OlapReport CreateComplexReport()
{
    OlapReport report = new OlapReport();
    report.CurrentCubeName = "Adventure Works";
    
    // Multiple column dimensions
    DimensionElement productDimension = new DimensionElement();
    productDimension.Name = "Product";
    productDimension.AddLevel("Product Categories", "Category");
    
    DimensionElement geographyDimension = new DimensionElement();
    geographyDimension.Name = "Customer";
    geographyDimension.AddLevel("Customer Geography", "Country");
    
    // Multiple measures
    MeasureElements measures = new MeasureElements();
    measures.Elements.Add(new MeasureElement { Name = "Internet Sales Amount" });
    measures.Elements.Add(new MeasureElement { Name = "Reseller Sales Amount" });
    
    // Row dimension
    DimensionElement dateDimension = new DimensionElement();
    dateDimension.Name = "Date";
    dateDimension.AddLevel("Fiscal", "Fiscal Year");
    
    // Build report
    report.CategoricalElements.Add(productDimension);
    report.CategoricalElements.Add(geographyDimension);
    report.CategoricalElements.Add(measures);
    report.SeriesElements.Add(dateDimension);
    
    return report;
}

Key Properties and Methods

OlapGrid Properties

• `OlapDataManager` - Gets or sets the OlapDataManager instance
• `Layout` - Gets or sets the grid layout type (GridLayout enum)
• `ConditionalFormats` - Collection of conditional formatting rules
• `InternalGrid` - Access to underlying grid engine for advanced operations

OlapGrid Methods

• `DataBind()` - Binds the grid to the data source
• `ExportToExcel(string filename)` - Exports grid to Excel
• `ExportToWord(string filename)` - Exports grid to Word
• `ExportToPdf(string filename)` - Exports grid to PDF

OlapDataManager

• `SetCurrentReport(OlapReport report)` - Sets the active OLAP report
• Constructor accepts connection string for OLAP data source

OlapReport Structure

• `CurrentCubeName` - Name of the cube to query
• `CategoricalElements` - Collection for column axis (dimensions and measures)
• `SeriesElements` - Collection for row axis (dimensions)

Common Use Cases

Business Intelligence Dashboards

Create executive dashboards displaying KPIs, sales metrics, and performance indicators with drill-down capabilities for detailed analysis.

Sales Analysis Reports

Analyze sales data across multiple dimensions (time, geography, product) with the ability to drill into specific regions or time periods.

Financial Reporting

Display financial data from OLAP cubes with conditional formatting to highlight variances, trends, and exceptional values.

Multi-Dimensional Data Exploration

Enable users to explore complex datasets by drilling through hierarchies and pivoting dimensions interactively.

Data Export and Distribution

Generate formatted reports in Excel, Word, or PDF for distribution to stakeholders who need offline access to OLAP analysis.

Comparative Analysis

Compare multiple measures (actual vs. budget, current vs. prior year) across different dimensions with Excel-like layout and member properties.