Click here to Skip to main content
15,901,373 members
Articles / Database Development / Data Visualization
Article

Binding Several Data Sources to One Reporting Tool

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
1 Feb 2023CPOL6 min read 5.8K   3  
This article sheds light on why it is useful to combine different data sources in a report.

This article is a sponsored article. Articles such as these are intended to provide you with information on products and services that we consider useful and of value to developers

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:

C#
// 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:

C#
// 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:

C#
// 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

C#
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

C#
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:

C#
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:

C#
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:

Image 1

How to pull data from a SQL Server

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:

Image 2

How to add an excel sheet to the data mix

Combining data

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

Image 3

Report template for combining data

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

Image 4

Add relations to combine data

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.

Image 5

Sample of interactive and relational reporting

Further Reading

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
CEO combit
Germany Germany
Leading the development at combit as Managing Director. Microsoft .NET enthusiast driving innovation & agile project management. Used to be a physicist in my first life. I love hiking and vanlife.

Comments and Discussions