by Joche Ojeda | Jan 20, 2025 | ADO, ADO.NET, Database, dotnet
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:
-
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]";
-
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
-
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:
- The importance of choosing the right tools for analytical workloads
- The limitations of running complex analytics on transactional databases
- 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.
by Joche Ojeda | Jan 2, 2025 | XtraReports
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:
- Output Focus ?️
- Windows Forms: Screen-based interaction
- XtraReports: Print/export optimization
- Layout Model ?
- Windows Forms: Flexible screen layouts
- XtraReports: Page-based layouts with bands
- Control Behavior ?
- Windows Forms: Interactive controls
- XtraReports: Display-oriented controls
- Data Processing ?️
- Windows Forms: Real-time data interaction
- XtraReports: Batch data processing
Some Advices ?
- Design Philosophy
// Think in terms of paper output
public class InvoiceReport : XtraReport
{
protected override void OnBeforePrint(PrintEventArgs e)
{
// Calculate page breaks
// Optimize for printing
}
}
- Layout Strategy
- Use bands for logical grouping
- Consider paper size constraints
- Plan for different export formats
- Data Handling
- Pre-process data when possible
- Use calculated fields for complex logic
- Consider subreports for complex layouts