News combit Dev Blog

Introducing Parametrized Data Sources

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

List & Label's powerful filter options could not be used for all data sources so far. Reason is, especially for web based data sources, it is not possible or feasible to get "all" data first and then filter to the desired subset. The upcoming version 24 comes with a powerful new feature that addresses this very issue: Parametrized Data Sources. It allows to combine data source parameters with actual report parameters in the Designer.


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.


  

RSS
combit Development BlogRSS
What’s this blog for?
A place to share updates about the ongoing development of our products. General musings on the software industry included.
Archive
March 2021 (1)
February 2021 (2)
January 2021 (1)
December 2020 (2)
November 2020 (1)
October 2020 (2)
September 2020 (1)
August 2020 (2)
July 2020 (1)
June 2020 (1)
May 2020 (2)
April 2020 (2)
March 2020 (2)
February 2020 (1)
January 2020 (1)
December 2019 (1)
November 2019 (2)
October 2019 (2)
September 2019 (2)
August 2019 (2)
July 2019 (2)
June 2019 (2)
May 2019 (1)
April 2019 (0)
April 2019 (1)
March 2019 (1)
February 2019 (1)
January 2019 (1)
December 2018 (1)
November 2018 (2)
October 2018 (4)
September 2018 (4)
August 2018 (2)
July 2018 (2)
June 2018 (2)
May 2018 (1)
April 2018 (1)
March 2018 (1)
February 2018 (2)
January 2018 (1)
December 2017 (1)
November 2017 (2)
October 2017 (3)
September 2017 (3)
August 2017 (2)
July 2017 (2)
June 2017 (1)
May 2017 (2)
April 2017 (1)
March 2017 (1)
February 2017 (1)
January 2017 (2)
December 2016 (1)
November 2016 (1)
October 2016 (4)
September 2016 (5)
August 2016 (2)
July 2016 (2)
June 2016 (3)
May 2016 (2)
April 2016 (3)
March 2016 (3)
February 2016 (1)
January 2016 (2)
December 2015 (2)
November 2015 (1)
October 2015 (4)
September 2015 (5)
August 2015 (2)
July 2015 (2)
June 2015 (2)
May 2015 (1)
April 2015 (2)
March 2015 (1)
February 2015 (1)
January 2015 (1)
December 2014 (2)
November 2014 (1)
October 2014 (1)
September 2014 (2)
August 2014 (1)
July 2014 (2)
June 2014 (1)
May 2014 (2)
April 2014 (1)
March 2014 (2)
+1 800 256 3608 (toll-free North America only)