SQL Server

This report show how to build report from MySQL data

This example show you how to connect to SQL Server and make a report. By default, KoolReport uses PDO as the database connector, you only need to enter the settings to connectionString of datasource settings.

function settings()
{
    return array(
        "dataSources"=>array(
            "sakila_rental"=>array(
                "connectionString"=>"sqlsrv:server=localhost ; Database=sakila",
                "username"=>"username",
                "password"=>"password",
                "charset"=>"utf8"
            ),
        )
    ); 
}    

If in situation that your server does not support PDO for SQL Server, then you can use SQLSRVDataSource instead

function settings()
{
    return array(
        "dataSources"=>array(
            "sakila"=>array(
                'datahost' => 'localhost',
                'username' => 'username',
                'password' => 'password',
                'database' => 'sakila',
                'charset' => 'utf8',  
                'class' => "\koolreport\datasources\SQLSRVDataSource",
                'returnDatesAsStrings'=>true  
            ),
        )
    ); 
}    
<?php 

require_once "SakilaRental.php";
$report = new SakilaRental;
$report->run()->render();
<?php

require_once "../../../../koolreport/autoload.php";

use \koolreport\KoolReport;
use \koolreport\processes\Filter;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\Group;
use \koolreport\processes\Limit;

class SakilaRental extends KoolReport
{
    function settings()
    {
        return array(
            "dataSources"=>array(
                "sakila_rental"=>array(
                    "connectionString"=>"sqlsrv:server=localhost ; Database=sakila",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        ); 
    }    
    protected function setup()
    {
        $this->src('sakila_rental')
        ->query("SELECT payment_date,amount FROM payment")
        ->pipe(new TimeBucket(array(
            "payment_date"=>"month"
        )))
        ->pipe(new Group(array(
            "by"=>"payment_date",
            "sum"=>"amount"
        )))
        ->pipe($this->dataStore('sale_by_month'));
    } 
}
<?php 
    use \koolreport\widgets\koolphp\Table;
    use \koolreport\widgets\google\BarChart;
?>
<div class="report-content">
    <div class="text-center">
        <h1>SQL Server</h1>
        <p class="lead">This report show how to build report from MySQL data</p>
    </div>

    <?php
    BarChart::create(array(
        "dataStore"=>$this->dataStore('sale_by_month'),  
        "columns"=>array(
            "payment_date"=>array(
                "label"=>"Month",
                "type"=>"datetime",
                "format"=>"Y-n",
                "displayFormat"=>"F, Y",
            ),
            "amount"=>array(
                "label"=>"Amount",
                "type"=>"number",
                "prefix"=>"$",
            )
        ),
        "width"=>"100%",
    ));
    ?>
</div>
payment_dateamount
2005-05-25 11:30:37 3
2005-05-28 10:35:23 1
2005-06-15 00:54:12 6
2005-06-15 18:02:53 1
2005-06-15 21:08:46 10
2005-06-16 15:18:57 5
2005-06-18 08:41:48 5
2005-06-18 13:33:59 1
2005-06-21 06:24:45 4
2005-07-08 03:17:05 6
2005-07-08 07:33:56 6
2005-07-09 13:24:07 5
2005-07-09 16:38:01 5
2005-07-11 10:13:46 8
2005-07-27 11:31:22 3
2005-07-28 09:04:45 5
2005-07-28 16:18:23 5
2005-07-28 17:33:39 1
2005-07-28 19:20:07 1
2005-07-29 03:58:49 3

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