Introducing Parametrized Data Sources

Posted: Monday, April 30, 2018 | 0 comment(s)

So far filter options could not be used with:

• Webservices/REST APIs (via JsonDataProvider, XmlDataProvider and RestDataProvider)
• Stored Procedures (for all SQL databases such as SQL Server, Oracle, PostgreSQL, ...)
• Excel tables (ExcelDataProvider)

As a simple example: For a REST API that provides job data in JSON format, the JsonDataProvider can be used. Important filter criteria (e. g. customer number) usually have to be transferred in the URL of the web service, otherwise too much data would be transferred at once (i.e. it would transfer the order data of all customers).

If the orders are to be filtered for the customer with ID 123, the URL could look like this:

http://example.net/api/customers/123/orders

However, before LL24, this URL had to be passed to the JsonDataProvider in the constructor:

JsonDataProvider orderData = new JsonDataProvider ("http://example.net/api/customers/123/orders");

The problem shows immediately: this URL, which was passed initially, cannot not be changed afterwards. In the Designer, you could have further filtered the once loaded order data of customer 123 (for example, using user-defined report parameters), but you would never have had the opportunity to switch spontaneously to the order data of another customer (for example, customer no. 456) in the Designer preview. 

Enter the new parametrized data sources feature for LL24 - this allows to combine data source parameters with actual report parameters in the Designer. Even better, a data source can automatically create the report parameters that are required in order to properly use it. They are defined before the Designer is even opened or a print is started.

For data providers that support the new feature, variable parameters can be inserted into the previously fixed options (such as the URL for the JsonDataProvider). This is done with a special syntax.

Examples

JSON
As a real-world JSON example consider the following URL that returns the water level at Konstanz for the last 30 days:

http://www.pegelonline.wsv.de/webservices/rest-api/v2/stations/KONSTANZ/W/measurements.json?start=P30D

If you would like to have a more flexible data source that offers different stations along the Rhine River you can simply use

http://www.pegelonline.wsv.de/webservices/rest-api/v2/stations/{{City=KONSTANZ|choices=KONSTANZ,BONN,KOBLENZ|multiselect=false}}/W/measurements.json?start=P{{ViewDays=30|choices=10,20,30|multiselect=false}}D

as URL. This uses some of the advanced options listed below to offer different choices for the parameters. 

This is what the result looks like in the HTML5 viewer:


In the Desktop preview:

SQL
For a stored SQL procedure, the client ID should be passed as a parameter in order to obtain its data. Until now, there was no way to call up the data of a client with another ID in the Designer or for Drilldown (if desired). The values of the parameter property of DbCommands can now accommodate our data source parameters:

getOrdersCommand.parameter[0].Value = "{{ClientId=ABC}}""

A full example for an Access database would be

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _databasePath);
conn.Open();
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);
 
OleDbCommand cmd2 = new OleDbCommand("SELECT Customers.Country FROM Customers", conn);
 
DbCommandSetDataProvider prov = new DbCommandSetDataProvider();
prov.AddCommand(cmd, "CustomersFiltered", "[{0}]", null);
prov.AddCommand(cmd2, "Customers", "[{0}]", null);
using (ListLabel LL = new ListLabel())
{
    LL.DataSource = prov;
    LL.AutoShowSelectFile = false;
    LL.Design();
}

Excel
If a user creates a separate Excel file with measurement results for each year (2016.xls, 2017.xls,...) and wants to evaluate the data via XlsDataProvider, there was no possibility to switch between the files for the different years in the preview or for drilldown in the Designer. Here you can now parametrize the file path:

var excelData = new XlsDataProvider(@"\\FileServer\RawData\{{Year=2018|choices=2016,2017,2018}}.xlsx", firstRowContainsColumnNames);

Advanced Options
To avoid incorrect entries, you can define more precisely which values a data source parameter expects. This "specification" is then copied as exactly as possible to the settings of the generated report parameters.
 

Requirement	Parameter Option Example
Allow only numeric values	{{CustomerID=123|format=number}}
Allow only predefined values	{{Country=US|choices=US,CA,GB}}
Retrieve values from data source	{{Country=US|choicesfield=countries.code}}
Hide parameter (useful for Drilldown)	{{Country=US|hidden=true}}

---

Author: Jochen Bartlau
Head of Development List & Label

Jochen Bartlau leads the development at combit as Managing Director. He's a Microsoft .NET enthusiast driving innovation & agile project management. The mobile devices geek who used to be a physicist in his first life loves to spend his spare time with his family.

   

---