Multiple Data Filters

The example demonstrate how to build dynamic reports with multiple data filters

Select Years

Select Product Lines

Select Customers

customerName	productLine	amount	year
2003
Classic Cars
Alpha Cognac	Classic Cars	$17,399	2003
Reims Collectables	Classic Cars	$13,920	2003
Men 'R' US Retailers, Ltd.	Classic Cars	$20,453	2003
Mini Auto Werke	Classic Cars	$21,533	2003
Mini Classics	Classic Cars	$17,959	2003
Mini Creations Ltd.	Classic Cars	$26,778	2003
Mini Gifts Distributors Ltd.	Classic Cars	$82,918	2003
Motor Mint Distributors Inc.	Classic Cars	$25,833	2003
Muscle Machine Inc	Classic Cars	$85,240	2003
Norway Gifts By Mail, Co.	Classic Cars	$36,799	2003
Online Diecast Creations Co.	Classic Cars	$55,426	2003
Oulu Toy Supplies, Inc.	Classic Cars	$10,949	2003
QuÃƒÂ©bec Home Shopping Network	Classic Cars	$16,910	2003
Rovelli Gifts	Classic Cars	$4,548	2003
La Corne D'abondance, Co.	Classic Cars	$16,284	2003
Salzburg Collectables	Classic Cars	$5,192	2003
Saveley & Henriot, Co.	Classic Cars	$14,150	2003
Scandinavian Gift Ideas	Classic Cars	$5,445	2003
Signal Gift Stores	Classic Cars	$15,740	2003
Stylish Desk Decors, Co.	Classic Cars	$16,993	2003
Suominen Souveniers	Classic Cars	$17,008	2003
Super Scale Inc.	Classic Cars	$22,198	2003
Technics Stores Inc.	Classic Cars	$13,958	2003
Toms SpezialitÃƒÂ¤ten, Ltd	Classic Cars	$25,368	2003
UK Collectables, Ltd.	Classic Cars	$13,597	2003
Volvo Model Replicas, Co	Classic Cars	$5,718	2003
Anna's Decorations, Ltd	Classic Cars	$60,853	2003
Marseille Mini Autos	Classic Cars	$47,710	2003
Toys of Finland, Co.	Classic Cars	$4,134	2003
L'ordine Souveniers	Classic Cars	$32,647	2003
Cruz & Sons Co.	Classic Cars	$40,232	2003
Australian Collectables, Ltd	Classic Cars	$1,657	2003
Australian Gift Network, Co	Classic Cars	$15,002	2003
Atelier graphique	Classic Cars	$14,571	2003
AV Stores, Co.	Classic Cars	$25,306	2003
Baane Mini Imports	Classic Cars	$14,549	2003
Blauer See Auto, Co.	Classic Cars	$24,102	2003
Canadian Gift Exchange Network	Classic Cars	$12,506	2003
Classic Gift Ideas, Inc	Classic Cars	$9,675	2003
Kelly's Gift Shop	Classic Cars	$14,987	2003
Collectables For Less Inc.	Classic Cars	$32,680	2003
Corporate Gift Ideas Co.	Classic Cars	$22,892	2003
Corrida Auto Replicas, Ltd	Classic Cars	$14,885	2003
Classic Legends Inc.	Classic Cars	$18,279	2003
Daedalus Designs Imports	Classic Cars	$8,406	2003
Gift Ideas Corp.	Classic Cars	$3,797	2003
Iberia Gift Imports, Corp.	Classic Cars	$20,008	2003
Danish Wholesale Imports	Classic Cars	$18,941	2003
Heintze Collectables	Classic Cars	$36,164	2003
GiftsForHim.com	Classic Cars	$9,134	2003
Herkku Gifts	Classic Cars	$55,070	2003
Gift Depot Inc.	Classic Cars	$24,879	2003
Euro+ Shopping Channel	Classic Cars	$79,121	2003
Enaco Distributors	Classic Cars	$5,324	2003
Dragon Souveniers, Ltd.	Classic Cars	$69,657	2003
Double Decker Gift Stores, Ltd	Classic Cars	$2,829	2003
Diecast Collectables	Classic Cars	$26,518	2003
Motorcycles
Toys4GrownUps.com	Motorcycles	$43,922	2003
Toms SpezialitÃƒÂ¤ten, Ltd	Motorcycles	$2,620	2003
Technics Stores Inc.	Motorcycles	$21,315	2003
Reims Collectables	Motorcycles	$16,700	2003
Mini Wheels Co.	Motorcycles	$23,924	2003
Mini Gifts Distributors Ltd.	Motorcycles	$11,689	2003
Marseille Mini Autos	Motorcycles	$3,115	2003
Lyon Souveniers	Motorcycles	$23,419	2003
Land of Toys Inc.	Motorcycles	$22,293	2003
King Kong Collectables, Co.	Motorcycles	$2,832	2003
Kelly's Gift Shop	Motorcycles	$3,911	2003
Herkku Gifts	Motorcycles	$27,168	2003
Corporate Gift Ideas Co.	Motorcycles	$31,790	2003
GiftsForHim.com	Motorcycles	$11,086	2003
Australian Collectors, Co.	Motorcycles	$21,815	2003
Baane Mini Imports	Motorcycles	$1,491	2003
Anna's Decorations, Ltd	Motorcycles	$16,402	2003
Cruz & Sons Co.	Motorcycles	$10,770	2003
Daedalus Designs Imports	Motorcycles	$34,378	2003
Euro+ Shopping Channel	Motorcycles	$12,583	2003
Frau da Collezione	Motorcycles	$2,853	2003
Gift Ideas Corp.	Motorcycles	$2,834	2003
Planes
West Coast Collectables Co.	Planes	$6,577	2003
Toys4GrownUps.com	Planes	$10,301	2003
Toys of Finland, Co.	Planes	$19,113	2003
Technics Stores Inc.	Planes	$25,731	2003
Scandinavian Gift Ideas	Planes	$8,962	2003
Salzburg Collectables	Planes	$13,670	2003
Rovelli Gifts	Planes	$37,136	2003
Mini Creations Ltd.	Planes	$18,546	2003
King Kong Collectables, Co.	Planes	$37,359	2003
Herkku Gifts	Planes	$2,787	2003
Kelly's Gift Shop	Planes	$16,049	2003
Double Decker Gift Stores, Ltd	Planes	$6,912	2003
Gift Ideas Corp.	Planes	$13,814	2003
Australian Collectors, Co.	Planes	$20,023	2003
Cruz & Sons Co.	Planes	$18,975	2003
Alpha Cognac	Planes	$16,555	2003
Down Under Souveniers, Inc	Planes	$2,880	2003
Enaco Distributors	Planes	$1,152	2003
Euro+ Shopping Channel	Planes	$19,927	2003
Frau da Collezione	Planes	$13,314	2003
Ships
Toys of Finland, Co.	Ships	$6,539	2003
Super Scale Inc.	Ships	$4,244	2003
Gift Ideas Corp.	Ships	$1,693	2003
Stylish Desk Decors, Co.	Ships	$18,191	2003
Scandinavian Gift Ideas	Ships	$21,138	2003
Salzburg Collectables	Ships	$7,712	2003
Rovelli Gifts	Ships	$5,868	2003
Mini Gifts Distributors Ltd.	Ships	$16,594	2003
Mini Creations Ltd.	Ships	$24,047	2003
Boards & Toys Co.	Ships	$2,134	2003
FunGiftIdeas.com	Ships	$2,210	2003
Enaco Distributors	Ships	$25,858	2003
Dragon Souveniers, Ltd.	Ships	$14,394	2003
Double Decker Gift Stores, Ltd	Ships	$4,273	2003
Danish Wholesale Imports	Ships	$18,579	2003
CAF Imports	Ships	$19,658	2003
Auto-Moto Classics Inc.	Ships	$2,753	2003
Alpha Cognac	Ships	$5,847	2003
Euro+ Shopping Channel	Ships	$20,451	2003
Trains
Mini Classics	Trains	$1,948	2003
Super Scale Inc.	Trains	$5,473	2003
Royale Belge	Trains	$1,628	2003
Muscle Machine Inc	Trains	$4,326	2003
Mini Gifts Distributors Ltd.	Trains	$7,027	2003
Mini Creations Ltd.	Trains	$5,582	2003
L'ordine Souveniers	Trains	$3,348	2003
Euro+ Shopping Channel	Trains	$11,835	2003
Dragon Souveniers, Ltd.	Trains	$11,423	2003
Diecast Collectables	Trains	$2,489	2003
Danish Wholesale Imports	Trains	$3,334	2003
CAF Imports	Trains	$2,690	2003
Australian Gift Network, Co	Trains	$1,887	2003
Iberia Gift Imports, Corp.	Trains	$2,833	2003
Trucks and Buses
La Corne D'abondance, Co.	Trucks and Buses	$17,100	2003
Muscle Machine Inc	Trucks and Buses	$14,759	2003
Mini Classics	Trucks and Buses	$16,185	2003
Mini Gifts Distributors Ltd.	Trucks and Buses	$8,881	2003
Technics Stores Inc.	Trucks and Buses	$23,549	2003
Oulu Toy Supplies, Inc.	Trucks and Buses	$21,774	2003
Saveley & Henriot, Co.	Trucks and Buses	$23,603	2003
Stylish Desk Decors, Co.	Trucks and Buses	$24,547	2003
Suominen Souveniers	Trucks and Buses	$12,708	2003
Mini Auto Werke	Trucks and Buses	$19,563	2003
L'ordine Souveniers	Trucks and Buses	$2,529	2003
Iberia Gift Imports, Corp.	Trucks and Buses	$24,672	2003
Euro+ Shopping Channel	Trucks and Buses	$13,726	2003
Dragon Souveniers, Ltd.	Trucks and Buses	$39,775	2003
Diecast Collectables	Trucks and Buses	$30,258	2003
Corrida Auto Replicas, Ltd	Trucks and Buses	$23,103	2003
Classic Legends Inc.	Trucks and Buses	$7,843	2003
Classic Gift Ideas, Inc	Trucks and Buses	$12,043	2003
Canadian Gift Exchange Network	Trucks and Buses	$8,966	2003
Baane Mini Imports	Trucks and Buses	$20,987	2003
Australian Gift Network, Co	Trucks and Buses	$10,086	2003
Vintage Cars
Kelly's Gift Shop	Vintage Cars	$20,094	2003
Royale Belge	Vintage Cars	$1,128	2003
Rovelli Gifts	Vintage Cars	$42,323	2003
Online Diecast Creations Co.	Vintage Cars	$10,224	2003
Mini Auto Werke	Vintage Cars	$1,157	2003
Muscle Machine Inc	Vintage Cars	$13,310	2003
Mini Wheels Co.	Vintage Cars	$16,538	2003
Mini Gifts Distributors Ltd.	Vintage Cars	$40,674	2003
Mini Creations Ltd.	Vintage Cars	$18,613	2003
Saveley & Henriot, Co.	Vintage Cars	$1,959	2003
Salzburg Collectables	Vintage Cars	$9,252	2003
Super Scale Inc.	Vintage Cars	$6,224	2003
Scandinavian Gift Ideas	Vintage Cars	$8,622	2003
Signal Collectibles Ltd.	Vintage Cars	$29,997	2003
Signal Gift Stores	Vintage Cars	$16,902	2003
Souveniers And Things Co.	Vintage Cars	$24,014	2003
Stylish Desk Decors, Co.	Vintage Cars	$7,154	2003
Technics Stores Inc.	Vintage Cars	$4,865	2003
Toys of Finland, Co.	Vintage Cars	$7,816	2003
UK Collectables, Ltd.	Vintage Cars	$10,311	2003
Vitachrome Inc.	Vintage Cars	$5,495	2003
Volvo Model Replicas, Co	Vintage Cars	$1,957	2003
King Kong Collectables, Co.	Vintage Cars	$5,290	2003
Australian Collectables, Ltd	Vintage Cars	$33,849	2003
Gift Ideas Corp.	Vintage Cars	$1,525	2003
Canadian Gift Exchange Network	Vintage Cars	$15,055	2003
Alpha Cognac	Vintage Cars	$8,250	2003
Anna's Decorations, Ltd	Vintage Cars	$2,848	2003
Australian Collectors, Co.	Vintage Cars	$11,592	2003
Australian Gift Network, Co	Vintage Cars	$6,783	2003
Auto-Moto Classics Inc.	Vintage Cars	$3,284	2003
AV Stores, Co.	Vintage Cars	$23,120	2003
Baane Mini Imports	Vintage Cars	$14,683	2003
Blauer See Auto, Co.	Vintage Cars	$10,549	2003
Boards & Toys Co.	Vintage Cars	$2,332	2003
CAF Imports	Vintage Cars	$9,080	2003
Classic Gift Ideas, Inc	Vintage Cars	$12,888	2003
FunGiftIdeas.com	Vintage Cars	$7,767	2003
Classic Legends Inc.	Vintage Cars	$17,587	2003
Corporate Gift Ideas Co.	Vintage Cars	$30,876	2003
Corrida Auto Replicas, Ltd	Vintage Cars	$19,144	2003
Cruz & Sons Co.	Vintage Cars	$1,669	2003
Danish Wholesale Imports	Vintage Cars	$13,105	2003
Double Decker Gift Stores, Ltd	Vintage Cars	$8,261	2003
Dragon Souveniers, Ltd.	Vintage Cars	$14,875	2003
Enaco Distributors	Vintage Cars	$11,241	2003
Euro+ Shopping Channel	Vintage Cars	$32,196	2003
Frau da Collezione	Vintage Cars	$1,579	2003
West Coast Collectables Co.	Vintage Cars	$1,101	2003

Description

The example demonstrate how to build a dynamic reports with multiple data filters. In this example, the data selection can be filter by `year`, `productLine` and `customerName`. Those data filters can be multi-selected meaning that you can select more than 1 years, more than 1 product lines or more than 1 customer to view. The example use `Select2` from [inputs package](https://www.koolreport.com/packages/inputs) to construct the parameters selection. We use the [Row Group] feature of Table for better data visualization. This grouping features support unlimited levels of grouping. There is very limited data table in market supporting this features. This feature is totally free with KoolReport. __Code Explanation__: The report use `\koolreport\inputs\Bindable` and `\koolreport\inputs\POSTBinding` services which will allows data binding between the report parameters and inputs controls. So the selection from select2 controls like years,productLines and customerNames will be bound to the corresponding report params. We use the `defaultParamValues()` methods in the report to define default starting selection of users. As you can see from the code, we pre-select the year `2003`. You may add different year, or set preselected for customerNames or productLines We use the `bindParamsToInputs()` to bind the name of report parameters to the name of the input controls. To keep things simple, the report parameters and name of input controls are the same: ``` protected function bindParamsToInputs() { return array( "years", "customerNames", "productLines" ); } ``` It is equivalent to ``` protected function bindParamsToInputs() { return array( "years"=>"years", "customerNames"=>"customerNames", "productLines"=>"productLines" ); } ``` In `setup()` function, base on the selection of users we add custom condition to the SQL query. Below code means that do not add the year condition if user does not select year. ``` ".(($this->params["years"]!=array())?"and YEAR(orderDate) in (:years)":"")." ``` And also, we have this line of code: ``` $query_params = array(); if($this->params["years"]!=array()) { $query_params[":years"] = $this->params["years"]; } ``` meaning that if user select year, then we add the year to variable `$query_params` to be used as parameters for sql query. In the view, we have some advance code for `Select2` widget: ``` <?php Select2::create(array( "multiple"=>true, "name"=>"years", "dataSource"=>$this->src("automaker")->query(" select YEAR(orderDate) as year from orders group by year "), "attributes"=>array( "class"=>"form-control" ) )); ?> ``` As you may notice, in `"dataSource"` of Select2 we use directly SQL command to query the available year. So as user selects years, productLines and customerNames, we will execute and store result to `"orders"` dataStore later be visualized in `Table`. __Enjoy the example!__