Build a 500,000 Row Report in Seconds Using Our Powerful .NET Data Engine

July 25 2022

When working with ComponentOne FlexReport and large datasets, there can be a need to retrieve specific data based on certain groupings. For example, if you are working on some sales data, you might need to create a report to show the number of sales per country, or you might need to create a report to display the data based on unique combinations of region and country.

Suppose you need to render about one million records in the report. In that case, you will need to build the groups in your FlexReport layout via the Groups option in the Home tab of FlexReportDesinger that are based on your requirements, and rendering this kind of report will take a significant amount of time which is around a minute.

But what if you can achieve the same functionality in a fraction of a second. You may be wondering: how is it even possible? The answer is yes; you can accomplish this using ComponentOne DataEngine, the standalone .NET Standard library that provides high-performance analytics for large datasets. C1DataEngine is faster than any other column-oriented storage approach and processes millions of records in less than a second.

So, to accomplish this, you will first need to design a flat report according to your requirements and then obtain the aggregated data from your massive dataset in a flattened form using C1DataEngine API to be assigned as the datasource of your FlexReport.

In this blog, we will demonstrate how you can create a report to display the sales data per country using C1DataEngine in a very few seconds. This will be divided into the steps below:

Design a Flat Report
Get the Required Data
Render the Report in FlexViewer

Want to Create a Large Report? Download ComponentOne today and give it a try yourself.

Design a Flat Report

As previously stated, you must create a flat report displaying sales data by country. First, add TextFields to the detail section for your database values, then design the layout that suits your needs. We have formatted the report in such a way that each record group will be shown in the form of a card, as presented in the following screenshot:

Flat Report Design

After this, create a Windows Forms Application, add the FlexReport component to load your report, and FlexViewer control into your form to render the report.

Design

To load the report via code, use the following:

c1FlexReport1.Load(@"..\..\SalesReport.flxr", "SalesReport");

Get the Required Data

To get the required data, you first need to add the following required packages for using the C1DataEngine API:

C1.DataCollection.BindingList
C1.DataEngine
C1.DataEngine.Collection

This blog will deal with the one million records via a CSV file containing the sales data. First, initialize the Workspace and IDataCollection objects and create a class containing properties that can hold the data corresponding to the fields in the CSV file.

private Workspace _workspace;
private IDataCollection<object> _dataCollection;
class SalesData
        {
            public string Region { get; set; }
            public string Country { get; set; }
            public string ItemType { get; set; }
            public string SalesChannel { get; set; }
            public string OrderPriority { get; set; }
            public DateTime OrderDate { get; set; }
            public string OrderID { get; set; }
            public DateTime ShipDate { get; set; }
            public int UnitsSold { get; set; }
            public double UnitPrice { get; set; }
            public double UnitCost { get; set; }
            public double TotalRevenue { get; set; }
            public double TotalCost { get; set; }
            public double TotalProfit { get; set; }
}

To get the complete one million records in your Workspace object use the CsvReader as given in the code snippet below:

//Initialize a new workspace folder relative to the project root directory
workspace = new Workspace();
workspace.Init("workspace");
 
//Read CSV data and convert it into a collection of custom .NET objects
var reader = new StreamReader(@"..\..\Sales Records.csv");
var csv = new CsvReader(reader, CultureInfo.InvariantCulture);
var salesDataList = csv.GetRecords<SalesData>().AsEnumerable();
 
//Import CSV data from custom collection to a DataEngine table
ObjectConnector<SalesData> connector = new ObjectConnector<SalesData>(workspace, salesDataList);
connector.GetData("SalesData");
workspace.Save();

The specific data we will be extracting from the database is the TotalCost, TotalRevenue, and TotalProfit for each unique country, and this can be done via query using the Op class of the C1.DataEngine assembly is invoked while defining the query to perform a simple operation.

The results of DataEngine queries are cached, making subsequent executions even more quickly. This query remains cached until the underlying data is refreshed. You can refresh data periodically, for example, hourly or daily, by checking a saved time stamp.

dynamic salesTable = workspace.table("SalesData");
dynamic query = workspace.query("SalesByCountry", new
{
Country = salesTable.Country,
TotalCost = Op.Sum(salesTable.TotalCost),
TotalProfit = Op.Sum(salesTable.TotalProfit),
TotalRevenue = Op.Sum(salesTable.TotalRevenue),
});

Use the C1DataEngineCollection class to get the data from the workspace based on your specific query and C1DataCollectionBindingList class to be assigned as the data source of your FlexReport based on your C1DataEngineCollection object.

C1DataEngineCollection _dataCollection = new C1DataEngineCollection(workspace, query);
c1FlexReport1.DataSource.Recordset = new C1DataCollectionBindingList(_dataCollection);

Render the Report in FlexViewer

For the report to appear in the FlexViewer at runtime, you must bind the Flexreport to the FlexReport's DocumentSource property.

c1FlexViewer1.DocumentSource = c1FlexReport1;

The report will get rendered in a short amount of time after following all the above processes, and look as in the GIF below:

Demo

If you are going to directly use FlexReport to work with this massive amount of data, you will need to create a grouped report like the one seen below:

Sales Report

The GIF attached shows how the report would render after assigning the one million records to FlexReport directly using the following code:

DataTable dt = new DataTable();
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\Sales Records.mdb;";
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from SalesRecords", conn);
adapter.Fill(dt);
c1FlexReport1.Load(@"..\..\SalesReportGrouped.flxr", "SalesReport");
c1FlexReport1.DataSource.Recordset = dt;
c1FlexViewer1.DocumentSource = c1FlexReport1;

Demo