Fuzzy query is a simple method to query data meeting certain formats in report designer. So let’s have a look!

1. Problem description

Fuzzy query in report designer is a simple method to query data meeting certain formats. For example, if you want to query all data containing character “a”, you can use fuzzy query.

2. Fuzzy query syntax

In fuzzy query of Finereport BI, “_” represents a single character and “%” represents any character for matching. Some common formats are as follows:

Select * from Table Name where Column Name like ‘%’; // Query All

Select * from Table Name where Column Name like ‘x’; // Complete Matching Query

Select * from Table Name where Column Name like ‘_x’; //if right is x, there is one character in the front

Select * from Table Name where Column Name like ‘__x’; //if right is x, there are two bytes of characters in the front

Select * from Table Name where Column Name like ‘x__’; //if left is x, there are two bytes of characters in the back

Select * from Table Name where Column Name like ‘%x’; //if right is x, there are any bytes of characters in the front

Select * from Table Name where Column Name like ‘x%’; //if left is x, there are any bytes of characters in the back

Select * from Table Name where Column Name like ‘%x%’; //if middle is x, there are any bytes of characters in middle

Combine parameter fuzzy query (replace the above x with ${name}):

Select * from Table Name where Column Name like ‘${name}’;

Select * from Table Name where Column Name like ‘%${name}’;

And so on.

3. Example

Use fuzzy query to realize that “if parameter is Null, select All”

Create a new workbook and add data set ds1 with sql statement: SELECT*FROM Sales Volume WHERE Area like ‘%${area}’

Effect viewing

Click Preview button on the Data Set Panel to pop up Parameter dialog. For example, if “East China” is input, the system only displays product sales volume in East China:

If no parameter value is input, the system will display product sales volumes of all areas: