Reporting - Filtering Enhancements

Reporting Team Blog
18 May 2018

For our upcoming v18.1 release, we have implemented two sets of improvements to existing data filtering functionality. We’re touching two completely different areas here, from end-user UI to low-level parameter representation and transfer.

Improved Filter Editor

The first group of changes applies to the text mode of the Filter Editor, which is used to configure client-side filters. The most important property that uses this editor is XtraReport.FilterString, but it is also used by Parameter.FilterString when creating cascading report parameters, and by the XRPivotGrid.Prefilter.Criteria property.

These are the new features of the Filter Editor:

  • Code completion and syntax highlighting help you enter valid expressions quickly:

Code completion

  • Column and function tooltips make it easy to refer to element details while the editor is being used:

Column and function tooltips

  • Input validation is much stronger than in the past, and errors are shown immediately when they occur:

Input validation and error reporting

Using multi-value report parameters with queries and stored procedures

In the past, we did not have direct support for use cases where multi-value report parameters needed to be mapped to query parameters. A workaround existed that used the DataSourceDemanded event, but it was necessary to write code for this event handler.

We are now adding the ability to handle these scenarios automatically. Things are very simple for SelectQueries — check out this expression:

[Orders.OrderID] Is any of ?OrderIDs

This is what the Filter Editor might show if a parameter is used (?OrderIDs) that represents a list of values, not just one value.

What about stored procedures and custom SQL queries?

Stored procedures are a bit more complicated, because you can’t pass a value list to a stored procedure directly. For this purpose, we are introducing the expression function Join(), which can combine values for you. On the server side, you need to parse the combined parameter string, which depends on your RDBMS of choice.

Note that if you allow your end-users to create custom SQL queries, these are executed in the database server context just like stored procedures. Therefor, the mechanisms described below need to be employed in any custom SQL queries that work with multi-value parameters.

For instance, on SQL Server you could do something like this (note the STRING_SPLIT part that “converts” the values, be sure to check Microsoft’s notes about it!):

select * from Orders
where OrderID in (select value from STRING_SPLIT(@data, ','));

On SQL Server, it is also possible to use stored procedures with table-valued parameters, together with our new CreateTable expression function.

As a second example, here is a MySQL stored procedure, somewhat more elegant due to the availability of the find_in_set helper:

create procedure `get_movies`(in titles varchar(100))
   select *
   from movies
   where find_in_set(title, titles);

You can find documentation of the process at this link, including sample code for several different scenarios, and here are more details about the Join and CreateTable functions.

Of course we always appreciate any feedback you can give us! Are these new features going to be useful to you?

FREE DevExpress Xamarin UI Controls

Deliver high-impact native mobile apps with our feature-rich Data Grid, Scheduler, Chart, TabView, Editors, and utility controls. Reserve your free copy today!
Engº Silvio Cruz
Engº Silvio Cruz

Great news.

18 May 2018
Eric Claiborne - Knox
Eric Claiborne - Knox

I just bought a Universal subscription today after doing a trial for 2 weeks.  I love the report writer and now migrating to XtraReport after 20+ years with Crystal.  I found the link to this page, ironically, because I had a report with a need for Multi-Value Parameters.  I have been testing with v18.2.6 and I have been having a small problem with the Query Parameters & Multi-Value Parameters.  I was able to do the report with a Stored Procedure instead and it's working great.  However, I would prefer to use a Query in the report.  I have some good screenshots of a problem I am still have (I believe it's a bug) and I want to pass it on to somebody who can fix it.  How do you recommend I proceed to report the problem I am having.

15 March 2019
Oliver Sturm (DevExpress)
Oliver Sturm (DevExpress)

Hi Customer70203 - I hear that our product team is working on two tickets you submitted to our Support Center, so I hope you'll have a response soon!

18 March 2019

Please login or register to post comments.