Reporting — Server-side Filtering of Cascading Parameters, EF Core 6 Support, Stored Procedures with Multiple Data Tables, and More (v22.1)

Reporting Team Blog
04 July 2022

DevExpress Reports v22.1 ships with a series of new data source-related features, including the following:

Stored Procedures with Multiple Data Tables

If stored procedures for your SQL database can return multiple data tables (result sets), we’ve got some good news for you. Our recent major release (v22.1), allows you to retrieve all these tables as needed. The following image displays multiple tables from one stored procedure (ProductInfo) within the Field List:

Stored procedure with multiple data tables in the Field List
The tables have the following titles: Result1, Result2, ..., ResultN, where N is the number of tables that the stored procedure returns.

As you would expect, you can display data from each of these data tables within your report. To display data, set your report's Data Member property to the appropriate table (the table that includes the data you wish to display).

Specify a report's Data Member

Once set, you can drop the table fields from the Field List onto report bands.

Drop table fields onto the Detail band

Your report document will then display all records from the table:

Report preview

The same method works for any other data table from for these stored procedures (those with multiple data tables).

If you want to combine multiple stored procedure data tables into one table, simply use our Federation Data Source. If you wish to display data from all the tables successively in one report, follow the approach described in the following help topic: Bind a Report to Multiple Data Tables.

When you select a stored procedure in the Data Source Wizard, you can preview stored procedure execution results.

Preview of a stored procedure execution result

For stored procedures that return multiple data tables, the data preview window displays a drop-down menu with all available tables. Simply select the desired table to preview your data.

Preview of a stored procedure table

Note: If such a stored procedure accepts query parameters, the procedure is executed with values determined using the following rules:

  • if a query parameter is static, the actual static value is used;
  • if a query parameter is expression, the expression value is used;
  • if a query parameter is mapped to a report parameter, the report parameter's default value is used.

Support of the ODBC Driver for the Google BigQuery SQL Datasets

Our SQL Data Source supports multiple data providers and allows you to connect DevExpress .NET Reports with BigQuery datasets. Recently, Google released an official ODBC driver for these datasets, and we now allow you to leverage Google’s driver to connect DevExpress Reports-powered .NET reporting applications with BigQuery SQL datasets.

The release of Google’s ODBC driver means that we will no longer support/maintain our custom BigQueryProvider driver.

Our custom BigQueryProvider  driver only supports legacy SQL syntax, and since we won't fix any bugs, implement new features, and provide any support for this driver, we recommend that you install Google's official ODBC  driver for use with BigQuery datasets.

New Options for Configuring MongoDB Data Source Schema

We added a new Select data fields page to our MongoDB Data Source Wizard.

The 'Select data fields' wizard page

This new page allows you to select the data fields that you wish to include in your MongoDB collection (and also change associated display names).

Use the drop-down menu to select the appropriate collection (the collection that includes the data fields you wish to configure).

Specify a query to configure

Enable or disable the checkbox in the Field Name column to include or remove a field from a collection.

Enable/disable a data field

Use editors within the Field Display Name column to change default field names.

Change field display name

Note: To generate field types and build its data source schema, the Data Source Wizard analyzes the first 100 documents (if the number of available documents is less than this default limit, the wizard analyzes all documents).

If the number of documents is greater than the default limit, the wizard page includes an Analyze all records button for your convenience (You can click this button to analyze all available documents instead of 100). Please keep in mind that a full analysis might be a time consuming process.

The 'Analyze all documents' button

Updated Manage Queries Dialog for the Federation Data Source

The Federation Data Source allows you to combine data from multiple data sources and apply operations such as UnionJoin, and Transforms to the federated data.

In certain cases, you might want to modify federated data after initial configuration. Use our Manage Queries dialog for this purpose (you can invoke the dialog from the Field List).

Invoke the 'Manage Queries' dialog from the Field List

With v22.1, you can add, modify, or delete queries when using our Manage Queries dialog.

The 'Manage Queries' dialog

Let's see some basic examples.

To rename a table, double-click the table name in the left menu and use the editor to change its name.
Change query name

To add a new query, click the Add button in the left menu.

Add a new query

Whether working with new or existing queries, you can execute the same operations as those available in our Query Builder. Please refer to the Data Federation section of our help file for more information on our Query Builder and federated data.

Database Level (Server-Side) Filtering of Cascading Parameters

DevExpress Reports allows you to create a report parameter (let's call it primary) and use it to filter values for a second parameter. These parameters are known as cascading parameters.

Cascading parameters example

With our recent release (v22.1), you can filter the values of a secondary cascading parameter at the database level. In the above screenshot, the selected product category contains 15 items. In real-world applications, such a category might contain hundreds or even thousands of products or items. When this new feature is enabled, only filtered values are loaded into the memory. As you might expect, this helps reduce memory usage/allocation.

The following data source types support this new capability:

  • SQL Database (SelectQuery only)
  • MongoDB Instance
  • Entity Framework

If you want to apply a filter to the aforementioned data source types at the application level, simply disable the secondary cascading parameter’s DynamicListLookUpSettings.UseServerSideFiltering property. Note: For other supported data sources, cascading parameter filter operations are always applied at the application level.

Entity Framework Core 6 Support

For the Entity Framework Data Source, we supported Entity Framework (EF) Core 6.0 context class bound to Microsoft SQL Server.

Your Feedback Matters

We’d love to hear your thoughts on these new features. Have we addressed a specific business need? What additional data source-related feature would you like us to introduce in DevExpress Reports?

Free DevExpress Products – Get Your Copy Today

The following free DevExpress product offers remain available. Should you have any questions about the free offers below, please submit a ticket via the DevExpress Support Center at your convenience. We’ll be happy to follow-up.