ADOMD.NET: Beyond Rows and Columns – The Multidimensional Evolution of ADO.NET

ADOMD.NET: Beyond Rows and Columns – The Multidimensional Evolution of ADO.NET

When I first encountered the challenge of migrating hundreds of Visual Basic 6 reports to .NET, I never imagined it would lead me down a path of discovering specialized data analytics tools. Today, I want to share my experience with ADOMD.NET and how it could have transformed our reporting challenges, even though we couldn’t implement it due to our database constraints.

The Challenge: The Sales Gap Report

The story begins with a seemingly simple report called “Sales Gap.” Its purpose was critical: identify periods when regular customers stopped purchasing specific items. For instance, if a customer typically bought 10 units monthly from January to May, then suddenly stopped in June and July, sales representatives needed to understand why.

This report required complex queries across multiple transactional tables:

  • Invoicing
  • Sales
  • Returns
  • Debits
  • Credits

Initially, the report took about a minute to run. As our data grew, so did the execution time—eventually reaching an unbearable 15 minutes. We were stuck with a requirement to use real-time transactional data, making traditional optimization techniques like data warehousing off-limits.

Enter ADOMD.NET: A Specialized Solution

ADOMD.NET (ActiveX Data Objects Multidimensional .NET) emerged as a potential solution. Here’s why it caught my attention:

Key Features:

  1. Multidimensional Analysis

    Unlike traditional SQL queries, ADOMD.NET uses MDX (Multidimensional Expressions), specifically designed for analytical queries. Here’s a basic example:

    string mdxQuery = @"
        SELECT 
            {[Measures].[Sales Amount]} ON COLUMNS,
            {[Date].[Calendar Year].MEMBERS} ON ROWS
        FROM [Sales Cube]
        WHERE [Product].[Category].[Electronics]";
  2. Performance Optimization

    ADOMD.NET is built for analytical workloads, offering better performance for complex calculations and aggregations. It achieves this through:

    • Specialized data structures for multidimensional analysis
    • Efficient handling of hierarchical data
    • Built-in support for complex calculations
  3. Advanced Analytics Capabilities

    The tool supports sophisticated analysis patterns like:

    string mdxQuery = @"
        WITH MEMBER [Measures].[GrowthVsPreviousYear] AS
            ([Measures].[Sales Amount] - 
            ([Measures].[Sales Amount], [Date].[Calendar Year].PREVMEMBER)
            )/([Measures].[Sales Amount], [Date].[Calendar Year].PREVMEMBER)
        SELECT 
            {[Measures].[Sales Amount], [Measures].[GrowthVsPreviousYear]} 
        ON COLUMNS...";

Lessons Learned

While we couldn’t implement ADOMD.NET due to our use of Pervasive Database instead of SQL Server, the investigation taught me valuable lessons about report optimization:

  1. The importance of choosing the right tools for analytical workloads
  2. The limitations of running complex analytics on transactional databases
  3. The value of specialized query languages for different types of data analysis

Modern Applications

Today, ADOMD.NET continues to be relevant for organizations using:

  • SQL Server Analysis Services (SSAS)
  • Azure Analysis Services
  • Power BI Premium datasets

If I were facing the same challenge today with SQL Server, ADOMD.NET would be my go-to solution for:

  • Complex sales analysis
  • Customer behavior tracking
  • Performance-intensive analytical reports

Conclusion

While our specific situation with Pervasive Database prevented us from using ADOMD.NET, it remains a powerful tool for organizations using Microsoft’s analytics stack. The experience taught me that sometimes the solution isn’t about optimizing existing queries, but about choosing the right specialized tools for analytical workloads.

Remember: Just because you can run analytics on your transactional database doesn’t mean you should. Tools like ADOMD.NET exist for a reason, and understanding when to use them can save countless hours of optimization work and provide better results for your users.

 

Understanding XtraReports: A Windows Forms Developer’s Guide

Understanding XtraReports: A Windows Forms Developer’s Guide

Introduction ?

If you’re familiar with Windows Forms development, transitioning to XtraReports will feel remarkably natural. This guide explores how XtraReports leverages familiar Windows Forms concepts while extending them for robust reporting capabilities.

? Quick Tip: Think of XtraReports as Windows Forms optimized for paper output instead of screen output!

A Personal Journey ✨

Microsoft released .NET Framework in late 2002. At the time, I was a VB6 developer, relying on Crystal Reports 7 for reporting. By 2003, my team was debating whether to transition to this new thing called .NET. We were concerned about VB6’s longevity—thinking it had just a couple more years left. How wrong we were! Even today, VB6 applications are still running in some places (it’s January 2, 2025, as I write this).

Back in the VB6 era, we used the Crystal Reports COM object to integrate reports. When we finally moved to .NET Framework, we performed some “black magic” to continue using our existing 700 reports across nine countries. The decision to fully embrace .NET was repeatedly delayed due to the sheer volume of reports we had to manage. Our ultimate goal was to unify our reporting and parameter forms within a single development environment.

This led us to explore other technologies. While considering Delphi, we discovered DevExpress. My boss procured our first DevExpress .NET license for Windows Forms, marking the start of my adventure with DevExpress and XtraReports. Initially, transitioning from the standalone Crystal Report Designer to the IDE-based XtraReports Designer was challenging. To better understand how XtraReports worked, I decided to write reports programmatically instead of using the visual designer.

Architectural Similarities ?️

XtraReports mirrors many fundamental Windows Forms concepts:

Source Destination
XtraReport Class Report Designer Surface
XtraReport Class Control Container
XtraReport Class Event System
XtraReport Class Properties Window
Control Container Labels & Text
Control Container Tables & Grids
Control Container Images & Charts
Report Designer Surface Control Toolbox
Report Designer Surface Design Surface
Report Designer Surface Preview Window

Like how Windows Forms applications start with a Form class, XtraReports begin with an XtraReport base class. Both serve as containers that can:

  • Host other controls
  • Manage layout
  • Handle events
  • Support data binding

Visual Designer Experience ?

The design experience remains consistent with Windows Forms:

Windows Forms XtraReports
Form Designer Report Designer
Toolbox Report Controls
Properties Window Properties Grid
Component Tray Component Tool

Control Ecosystem ?

XtraReports provides analogous controls to Windows Forms:


// Windows Forms
public partial class CustomerForm : Form
{
    private Label customerNameLabel;
    private DataGridView orderDetailsGrid;
}

// XtraReports
public partial class CustomerReport : XtraReport
{
    private XRLabel customerNameLabel;
    private XRTable orderDetailsTable;
}
    

Common control mappings:

  • Label ➡️ XRLabel
  • Panel ➡️ XRPanel
  • PictureBox ➡️ XRPictureBox
  • DataGridView ➡️ XRTable
  • GroupBox ➡️ Band
  • UserControl ➡️ Subreport

Data Binding Patterns ?

The data binding syntax maintains familiarity:


// Windows Forms data binding
customerNameLabel.DataBindings.Add("Text", customerDataSet, "Customers.Name");

// XtraReports data binding
customerNameLabel.ExpressionBindings.Add(
    new ExpressionBinding("Text", "[Name]"));
    

Code Architecture ?️

The code-behind model remains consistent:


public partial class CustomerReport : DevExpress.XtraReports.UI.XtraReport
{
    public CustomerReport()
    {
        InitializeComponent(); // Familiar Windows Forms pattern
    }

    private void CustomerReport_BeforePrint(object sender, PrintEventArgs e)
    {
        // Event handling similar to Windows Forms
        // Instead of Form_Load, we have Report_BeforePrint
    }
}
    

Key Differences ⚡

While similarities abound, important differences exist:

  1. Output Focus ?️
    • Windows Forms: Screen-based interaction
    • XtraReports: Print/export optimization
  2. Layout Model ?
    • Windows Forms: Flexible screen layouts
    • XtraReports: Page-based layouts with bands
  3. Control Behavior ?
    • Windows Forms: Interactive controls
    • XtraReports: Display-oriented controls
  4. Data Processing ?️
    • Windows Forms: Real-time data interaction
    • XtraReports: Batch data processing

Some Advices ?

  1. Design Philosophy
    
    // Think in terms of paper output
    public class InvoiceReport : XtraReport
    {
        protected override void OnBeforePrint(PrintEventArgs e)
        {
            // Calculate page breaks
            // Optimize for printing
        }
    }
                
  2. Layout Strategy
    • Use bands for logical grouping
    • Consider paper size constraints
    • Plan for different export formats
  3. Data Handling
    • Pre-process data when possible
    • Use calculated fields for complex logic
    • Consider subreports for complex layouts