Best Practices for Data Retrieval in Telerik Reporting

Creating a report basically means to obtain a chunk of data, organize it according to a given set of rules and present it in a visual manner. The reports are already an integral part of the current business processes—whether it is a simple invoice or an elaborate sales chart, virtually every collection of facts and figures can be represented in a visually appealing way. But before getting to the visual part of the things, we need to make sure the basis of our reports—the data—is retrieved and retained in the best possible way.

We on theTelerik Reportingteam understand the need for a reliable and easy way to access the data your report needs.That’s why I will explain below more about the different approaches for data retrieval and their variety—from database connections through collections of business objects up to JSON-formatted entities delivered from a web service.

The content below assumes you’re already familiar with Telerik Reporting, but if you’re not—please consider checking ourGetting Started articles first. I will go a bit more technical and provide under-the-hood details and examples because I believe that better understanding of the way the data gets obtained and processed by the reporting engine will help you build your reports faster and use them effortlessly in various applications.

Telerik Reports can connect to data from various sources, but the most commonly used is the database. The component that performs the actual connection between the report and the database is named SqlDataSource. Its wizard provides a simple and intuitive interface that helps to create and test a connection to the selected database in a matter of seconds.

The SqlDataSource is database-agnostic, i.e. it does not have dedicated routines for MSSQL, Oracle or MySQL databases. Instead it relays the commands and parameters through a common interface, provided by the DbProviderFactory class. While configuring the SqlDataSource, you select a provider name, which is used by the DbProviderFactories instance to create the corresponding DbProviderFactory. The available DbProviders are listed in a configuration file—it can be the common machine.config file or just the configuration file of the .NET application. Here’s how the list with the DbProviderFactory entries in the machine.config looks:

<DbProviderFactories>

  <add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client"

    description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

  <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc"

    type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

  <add name="OleDb Data Provider" invariant="System.Data.OleDb"

    description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

  <add name="SqlClient Data Provider" invariant="System.Data.SqlClient"

    description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

  <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient"

    description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=8.0.13.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />

</DbProviderFactories>

Usually every database provider or driver installed on the machine adds its entry here—this way the provider is visible to applications that need to access a database in a more generic approach. So when you pick the MySQL Data Provider from the combo-box in the SqlDataSource wizard, the wizard stores the invariant name of that provider and uses it for creating the specific implementations of DbConnection, DbCommand and the rest of the classes needed to access that database.

Telerik Reporting offers a few other components that allow connecting to a database, but they target a specific database or technology and are not as popular as the SqlDataSource. These components include:

• CubeDataSource—used to connect to an OLAP cube data model through MDX query;

• OpenClientDataSource—specifically designed to be used with OpenEdgeAppServer ABL procedures;

• OpenAccessDataSource—provides data through an OpenAccess data model.

• EntityDataSource—it’s not mandatory to have a real database as a data storage for this component—allows to connect to an existing ObjectContext or DbContext and access its data model. The current implementation works with .NET Framework and cannot be used in projects that employ EntityFrameworkCore.

Although the scenarios where the data is obtained from a database are still the most common ones, the cases where the database is not directly accessible or is even missing are getting more and more traction. In these setups a web service accepts the requests and returns data objects, formatted as JSON, XML or text. This approach has significant benefits in terms of security and configuration, and using a standardized interface to access the data provides for easier adoption. Telerik Reporting has a dedicated component to serve that purpose—theWebServiceDataSource component.

Similarly to the other data sources, it can be configured via its wizard to connect to a web service, authenticate if needed, request the necessary data and parse the response to a set of data objects. The WebServiceDataSourceconfiguration allows you to fine-tune the URL and the request body using tokens that will be substituted at runtime with actual values from report parameters or static strings.

As a real-life example, let’s take a look at the website restcountries.eu that provides statistics for all the countries in the world via RESTful API. The endpoint that returns all the countries is https://restcountries.eu/rest/v2/all , but if our report needs to show a subset of countries by given name, the endpoint we need to use is https://restcountries.eu/rest/v2/name/{name} . In this case the WebServiceDataSource’sServiceUrl would look like this: https://restcountries.eu/rest/v2/name/@name , where the“@” character denotes a token that will be substituted. The actual token substitution is configured in the WebServiceDataSource parameters collection, and in the current case we’ll have an inline parameter that obtains its value from a report parameter:

As you can see, it’s important to pick the right endpoint and request just the necessary info rather than fetch data for all the countries and filter it later on the clientside.

Regardless of how carefully we construct our database query, occasionally we may end up with a SqlDataSource that fetches a result set with 30 columns to a Table item which uses only 5 of them. The general recommendation applicable to all the data sources is “only what’s necessary,” i.e. data sources should retrieve the minimum of the data fields needed for the report, especially when the result set contains lots of records. Let’s illustrate it with an example: our report needs to show the actual price of the items we sold last year. The actual price equals the base price minus the discount. Our query might look like this:

Select base_price, discount, base_price - discount as actual_price from Sales

Assuming that Money field type in MSSQL takes 8 bytes , for one million records in our Sales table we’ll have ~8MB overhead only from a single column in our dataset. Of course, in a real-life scenario we’ll have traffic compression and other optimizations (and also 8MB is not 8 million bytes, right), but still this column is redundant. In this scenario I would use the SqlDataSource’s CalculatedFields property and write a simple expression that will subtract the prices for me.

Every data item or group in the report can set rules for filtering its data before it’s processed. This is useful in a few scenarios, but usually the data can and should be filtered server-side. As explained before, the less data is fetched from the data source, the better the performance. Almost all data source components provide a way to filter their data before it’s sent to the data items. The recommended way to apply filtering to the data source is through report parameters.

The report parameters are expressions, evaluated before processing the report. Depending on their configuration, they might be displayed in the report viewer, providing an UI to select or enter a value. Regardless of their visibility, they must have a valid value prior to report processing. This value might be passed to theWHERE clause in the SqlDataSource’s query, used in theDataSelector’sJSONPath expression in WebServiceDataSource, or sent as aparameter to the ObjectDataSource’s data retrieval method. In any case they should be used to decrease the amount of data fetched from the data source when possible. In the case of WebServiceDataSource component the DataSelector is applied client-side once the data is fetched from the server. Therefore, if the web service provides an endpoint that could return only the necessary data, it’s always better to use it rather than applying a DataSelector afterwards.

By “ordering”I do not mean sorting the data by its values, but customizing the layout of the columns and rows in the result set returned by the data source. In general, it should be as similar to the layout that will be used in the report as possible. But on the other hand, if the same data source will be reused by another data item, the same layout may be impractical. Consider having the following result set that represents the car sales per year: