Intro

In order to effectively and quickly bind List & Label to the desired data sources, the data providers are used. However, it is usually not desired that all data is always available in a report, but rather that certain preselections are defined in advance. This can be achieved in different ways:

One filters already the data, which are used in the used data provider. However, this has the decisive disadvantage that only the data in the data provider can be used for a report, which can often also be used with the background of an authorization system of the application - only data that is also available there can be used in the report.

But often there is also the requirement that not necessarily the application or the developer should decide which data should be present in a report, but the users or the corresponding specialist department that creates the report in the designer. Thus, the necessary filtering in the report can be provided there directly with the help of Data-/Project Filters or Report Parameters. In most cases, the filtering is already applied automatically in the data provider and is implemented accordingly on the data source itself in a performant and native manner. Using the example of a simplified Microsoft SQL database query with the SqlConnectionDataProvider, it would look something like this - the actual queries are of course protected against SQL injection and the following statements are only for illustration:

If simply using the "Customers" table in the report, the data provider would send the following query natively to the SQL Server and have it executed and List & Label would then map the ResultSet to the table:

SELECT * FROM "Customers"

However, if a data filter is now added for the "Customers" table in the Designer (also in combination with a report parameter created by the user), List & Label would not now execute the identical query as above and then filter the RestultSet itself manually for "ALFKI". Otherwise, every record returned would be checked for "ALFKI" and discarded or output accordingly, which would be comparatively slow. At this point, most data providers are optimized to the point that these filters are executed on the target system, which in this example ends directly with this query:

SELECT * FROM "Customers" WHERE "Cusomters.ID" = "ALFKI"

The ResultSet contains only the customers that match the filter - without List & Label itself having to filter beforehand at runtime.

With the help of parametrizable data sources, the combination of parameters for the data source and report parameters in the designer can be implemented very easily and effectively. Thus, the data provider automatically creates the report parameters that are required for correct use. For this purpose, the data provider used must support variable parameters that can be configured using a special syntax.

Structure of the Syntax for the Parameters

A certain pattern must be followed. Corresponding parameters are defined by double curly brackets - "{{" for the start of a parameter and "}}" for the end of the parameter. The structure then corresponds to <FIELDNAME>=<PREFERENCE> - example: {{CustomerID=ALFKI}}

Furthermore, additional options can be defined for the parameter, which can be specified separately from the actual parameter definition with a pipe character: 

Syntax: <FIELDNAME>=<PREFERENCE>|<OPTION>

Sample: {{CustomerID=ALFKI|choices=ALFKI,ANATR,ANTON,AROUT}}

Several options can also be combined: 

Syntax: <FIELDNAME>=<PREFERENCE>|<OPTION1|<OPTION2>

Sample: {{CustomerID=ALFKI|choices=ALFKI,ANATR,ANTON,AROUT|multiselect=true}}

Practical examples based on different data providers

The article Introducing Parametrized Data Sources contains more illustrated examples on this topic. In the following code snippets are shown for your use in your own source code.

Web Services/REST APIs

The JsonDataProvider can be used to define via the URL what data is to be fetched. So the orders of all customers can be defined, which are displayed as "customer number":

...
string parameter = "{{CustomerId=123|choicesfield=CustomerId|displayformula=Kundennummer|multiselection=true}}";
string url = String.Format("http://example.net/api/customers/{0}/orders", parameter);
JsonDataProvider jsonData = new JsonDataProvider(url);
...

...
Dim parameter As String = "{{CustomerId=123|choicesfield=CustomerId|displayformula=Kundennummer|multiselection=true}}"
Dim url As String = String.Format("http://example.net/api/customers/{0}/orders", parameter)
Dim jsonData As JsonDataProvider = New JsonDataProvider(url)
...

But of course this syntax can be applied to other data providers like XmlDataProvider and RestDataProvider.

SQL-Query with Stored Procedures

A "Country" report parameter is defined as part of a stored procedure and thus passed to the DbCommandSetDataProvider so that customers can be filtered based on country:

...
OleDbCommand cmd = new OleDbCommand("SELECT * FROM Customers WHERE Customers.Country=?", conn);

OleDbParameter param = new OleDbParameter("Country", OleDbType.VarChar, 50);
param.Value = "{{Country=Germany|choicesfield=Customers.Country}}";
cmd.Parameters.Add(param);

DbCommandSetDataProvider prov = new DbCommandSetDataProvider();
prov.AddCommand(cmd, "Customers", "[{0}]", null);
...

...
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Customers WHERE Customers.Country=?", conn)

Dim param As OleDbParameter = New OleDbParameter("Country", OleDbType.VarChar, 50)
param.Value = "{{Country=Germany|choicesfield=Customers.Country}}"
cmd.Parameters.Add(param)

Dim prov As DbCommandSetDataProvider = New DbCommandSetDataProvider()
prov.AddCommand(cmd, "Customers", "[{0}]", Nothing)
...

Excel

An Excel file can be accessed as a data source via the XlsDataProvider data provider. However, if the Excel file is to be determined variably in the report itself via the report parameter, the data provider can be defined as follows - a report parameter "Year" with the value "2025" is created, which also provides "2024" and "2023" as selections and subsequently points to a suitable Excel file name - "\\FileServer\RawData\2025.xlsx":

...
string parameter = "{{Year=2018|choices=2023,2024,2025}}";
string excelFileName = string.Format(@"\\FileServer\RawData\{0}.xlsx", parameter);
XlsDataProvider excelData = new XlsDataProvider(excelFileName, firstRowContainsColumnNames);
...

...
Dim parameter As String = "{{Year=2018|choices=2023,2024,2025}}"
Dim excelFileName As String = String.Format("\\FileServer\RawData\{0}.xlsx", parameter)
Dim excelData As XlsDataProvider = New XlsDataProvider(excelFileName, firstRowContainsColumnNames)
...