Binding Several Data Sources to One Reporting Tool

Reporting is a basic feature for almost any kind of application. Often, several data sources have to be combined to merge data and visualize everything in an informative way. This article sheds light on why it is useful to combine different data sources in a report. It also shows how this can be done with combit's List & Label.

Reasons for Binding Reports to Different Data Sources

Combining data from multiple sources into a single report is often required within complex projects. Information is typically stored decentralized in many different formats, on different servers, available from different technologies. By bringing all this together in one place, it is much easier to see the big picture and understand how different facettes of data are interacting with each other.

For example, imagine you're working on a project that involves:

• Tracking customer data in a SQL database, …
• … sales data in an Excel sheet, …
• … and financial data coming from business objects in your code.

Without a reporting tool, capable to combine several data sources, you'd have to manually pull information from each of these sources and try to make sense of it on your own. This is time-consuming and error-prone. Also, it would be difficult to see the overall trends and patterns in the data.

How Can a Reporting Tool Help?

Combining the data

Sophisticated reporting tools allow you and your end users to combine data from a wide range of data sources into a single report. They provide adapters for different types of data sources and allow to mix them. Typically, for the end user there's no visible difference between the different sources. Every kind of data can be used and reported in the same way, without knowing where it comes from.

Consolidating and visualizing the data

Besides just offering data from different sources, a reporting tool can also help with data consolidation. You can add relationships between different data sources, normalize or aggregate data and format values according to the business requirements. Many reporting tools allow you to create custom, even interactive charts and graphs that can help you visualize your data in an intuitive way. This makes it easier to identify trends and patterns that might not be obvious from looking at raw data.

Another advantage of reporting tools is that they help you automate many of the tedious and time-consuming tasks in data analysis. For example, you can use them to schedule regular reports to be generated and sent to stakeholders, or to trigger alerts when certain conditions are met. This helps you and your end users to stay on top of projects and ensure that you are always working with the most up-to-date information.

Build or buy?

When adding functionality, such as reporting, to an app, you always need to make a crucial build-or-buy decision. Are you going to write the functionality yourself or are you going to license a commercial 3rd party component?

Using components often leads to more consistent and reliable results. The component has been tested and has proven to work in a variety of scenarios. It also dramatically reduces the need for in-house development resources. Developers can focus on building custom functionality rather than recreating common features.

Components can also make it easier to scale and maintain projects, as they can be easily updated or replaced as needed. Therefore, a sophisticated reporting tool like combit's List & Label easily saves several years of development effort.

If you are interested in List & Label, take a look at the Online Demo or check out the following steps and everything else in the free and fully functional trial.

Setting Up the Combined Data Source in List & Label

List & Label comes with a wide range of data providers, covering all popular data bases (ex. Microsoft SQL Server, PostgreSQL, MySql, SQLite...), file formats (ex. XML, JSON, XLS) and web sources (ex. REST). There's even a special provider, allowing to mix data from these sources: DataProviderCollection. Usage is straight forward.

Drawing data from SQL Server

To access SQL Server data, you can use the SqlConnectionDataProvider class, which takes a connection as constructor parameter:

// build the connection srtring
var builder = new SqlConnectionStringBuilder();
builder.DataSource = "dbserver";
builder.InitialCatalog = "CustomerData";
// initialize the connection to the database
var connection = new SqlConnection(builder.ConnectionString);
// establish a matching List & Label data provider
var sqlProvider = new SqlConnectionDataProvider(connection);

Connecting to an Excel sheet

Excel data can be added via the XlsDataProvider class:

// access the Excel sheet data
XlsDataProvider xlsProvider = new XlsDataProvider(<PathToXLS>, true /*firstRowContainsColumnNames*/);

Combining the data

To mix the two sources, you can use the DataProviderCollection class as follows:

// construct a provider collection object
DataProviderCollection collection = new DataProviderCollection();
// add the two data sources
collection.Add(sqlProvider);
collection.Add(xlsProvider);

That's all. Now, List & Label can be bound to the combined data source, offering the full range of visualizations and analyses it offers. This works the same for desktop (WinForms/WPF) and cloud applications. A desktop app would call

using (ListLabel LL = new ListLabel())
{
    LL.DataSource = collection;
    LL.Design();
}

to get the Designer started with the combined data source. For a cloud app, there's just one controller that needs to be implemented in order to open the Web Report Designer. The code here would read

public override void OnProvideListLabel(ProvideListLabelContext provideListLabelContext)
{
    ListLabel LL = new ListLabel();
    LL.DataSource = collection;
    provideListLabelContext.NewInstance = LL;
}

Mixing Data On-the-Fly in Memory

An alternative way to connect to different data sources is to load them into memory. This allows for a number of more advanced features, like lightning-fast filtering and sorting data, coming from otherwise non-sortable datasources. In the above example, all that's needed is to wrap the Excel data in an in-memory data source:

InMemoryDataProvider inMemoryProvider = new InMemoryDataProvider();
inMemoryProvider.AddTable(xlsProvider, "Orders");

and then use this InMemoryDataProvider instance instead of the original Excel provider. List & Label can even establish a relation between the SQL and the wrapped XLS data, using a CrossProviderRelation:

collection.AddCrossProviderRelation(relationName: "Customers2Orders", parentProvider: sqlProvider, parentTableName: "Customers", parentColumnName: "CustomerID", childProvider: "inMemoryProvider", childTableName: "Orders", childColumnName: "CustomerID");

This offers end users a convenient way to work with data in the Designer, blending both data sources into one.

Combining Different Data Sources Interactively

This is another alternative supported by List & Label. The Enterprise edition comes with a ready-to-use, browser-based cloud reporting solution, the combit Report Server. It allows interactive processing and supports most of List & Label's datasources directly.

Drawing data from SQL Server

Configuring the data source is easy. To connect to SQL Server data, the following property dialog is available:

Connecting to an Excel sheet

Next, an Excel sheet can be added to the mix. By selecting the "In-Memory mode", advanced features like sorting and filtering become available:

Combining data

Now, these two datasources can be assigned to a report template:

By clicking on "Add Relation", the required relation between Excel and SQL data is configured:

That's all that is required to interactively reproduce the code steps from the first part. In the Designer, the two tables can be used in both cases (and identically) to offer interactive, relational reporting to a huge number of different formats.

Further Reading

More information about data binding options for List & Label can be found in combit's reporting blog or in the combit knowledgebase.