Multiple Data Filters

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

Select Years
Select Product Lines
Select Customers
customerNameproductLineamountyear
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
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
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
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
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
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
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

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 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!

<?php
require_once "MyReport.php";

$report = new MyReport;
$report->run()->render();
<?php
//Step 1: Load KoolReport
require_once "../../../load.koolreport.php";

//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;

    protected function defaultParamValues()
    {
        return array(
            "years"=>array(2003),
            "customerNames"=>array(),
            "productLines"=>array(),
        );
    }
    
    protected function bindParamsToInputs()
    {
        return array(
            "years",
            "customerNames",
            "productLines"
        );
    }

    protected function settings()
    {
        return array(
            "dataSources"=>array(
                "automaker"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=automaker",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        );
    }
    protected function setup()
    {
        $query_params = array();
        if($this->params["years"]!=array())
        {
            $query_params[":years"] = $this->params["years"];
        }
        if($this->params["customerNames"]!=array())
        {
            $query_params[":customerNames"] = $this->params["customerNames"];
        }
        if($this->params["productLines"]!=array())
        {
            $query_params[":productLines"] = $this->params["productLines"];
        }

        $this->src('automaker')->query("
            select
                customerName,
                productLine,
                YEAR(orderDate) as year,
                sum(quantityOrdered*priceEach) as amount
            from orders
            join customers
            on
                customers.customerNumber = orders.customerNumber        
            join orderdetails
            on orders.orderNumber = orderdetails.orderNumber
            join products
            on products.productCode = orderdetails.productCode
            where 1=1
            ".(($this->params["years"]!=array())?"and YEAR(orderDate) in (:years)":"")."
            ".(($this->params["customerNames"]!=array())?"and customerName in (:customerNames)":"")."
            ".(($this->params["productLines"]!=array())?"and productLine in (:productLines)":"")."    
            GROUP BY year, productLine, customerName
        ")->params($query_params)
        ->pipe($this->dataStore("orders"));
    }
}
<?php
    use \koolreport\widgets\koolphp\Table;
    use \koolreport\inputs\Select2;
?>
<div class="report-content">
    <div class="text-center">
        <h1>Multiple Data Filters</h1>
        <p class="lead">
            The example demonstrate how to build dynamic reports with multiple data filters
        </p>
    </div>
    
    <form method="post">
        <div class="row">
            <div class="col-md-6">
                <div class="form-group">
                    <b>Select Years</b>
                    <?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"
                        )
                    ));
                    ?>
                </div>    
  
                <div class="form-group">
                    <b>Select Product Lines</b>
                    <?php 
                    Select2::create(array(
                        "multiple"=>true,
                        "name"=>"productLines",
                        "dataSource"=>$this->src("automaker")->query("
                            select productLine
                            from orders
                            join orderdetails on orders.orderNumber = orderdetails.orderNumber
                            join products on products.productCode = orderdetails.productCode
                            ".(  $this->params["years"]!=array() ?"where YEAR(orderDate) in (:years)":"")."
                            group by productLine
                        ")->params(
                            $this->params["years"]!=array()?
                            array(":years"=>$this->params["years"]):
                            array()
                        ),
                        "attributes"=>array(
                            "class"=>"form-control"
                        )
                    ));
                    ?>                
                </div>
                <div class="form-group">
                    <b>Select Customers</b>
                    <?php 
                    Select2::create(array(
                        "multiple"=>true,
                        "name"=>"customerNames",
                        "dataSource"=>$this->src("automaker")->query("
                            select customerName
                            from orders
                            join customers on customers.customerNumber = orders.customerNumber                            
                            ".( $this->params["years"]!=array()?"where YEAR(orderDate) in (:years)":"")."
                            group by customerName
                        ")->params(
                            $this->params["years"]!=array()?
                            array(":years"=>$this->params["years"]):
                            array()
                        ),
                        "attributes"=>array(
                            "class"=>"form-control"
                        )
                    ));
                    ?>                
                </div>  
                <div class="form-group">
                    <button class="btn btn-primary">Submit</button>
                </div>    
            </div>
        </div>
        
    </form>
    <?php
    Table::create(array(
        "dataSource"=>$this->dataStore("orders"),
        "columns"=>array(
            "customerName",
            "productLine",
            "amount"=>array("prefix"=>"$"),
            "year"=>array("format"=>false)
        ),
        "grouping"=>array(
            "year",
            "productLine"
        ),
        "paging"=>array(
            "pageSize"=>25
        ),
        "cssClass"=>array(
            "table"=>"table-bordered"
        )
    ));
    ?>
</div>

What People Are Saying

"KoolReport helps me very much in creating data report for my corporate! Keep up your good work!"
-- Alain Melsens

"The first use of your product. I was impressed by its easiness and powerfulness. This product is a great and amazing."
-- Dr. Lew Choy Onn

"Fantastic framework for reporting!"
-- Greg Schneider

Download KoolReport Get KoolReport Pro