KoolReport's Forum
Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
Pivot Matrix Filter by year #208
One of the solutions is to create a multiselect box for Year outside of pivot where use can select years that they want to see. Here are some good controls for our inputs package
https://www.koolreport.com/examples/reports/inputs/intro/index.php
Yes I did look at that but then realised I would have no idea how to target the pivot or the underlying query from an external form, I was looking at this code, but again didnt have a clue after that :)
<?php MultiSelect::create(array(
"name"=>"multiSelect",
"dataStore"=>$this->dataStore("customers"),
"dataBind"=>"customerName",
"attributes"=>array(
"class"=>"form-control",
"size"=>5
)
));?>
Actually could I trouble you to ask before purchasing 'inputs' if the following would work?
Say my dataStore in PivotMatrix is called "ChaseList" and the field I want to filter on is "Year", could I simply add the below code to the .view page at the top right after <body>
Would that work I do I need to somehow target the sql code (I dont use csv)
<?php MultiSelect::create(array(
"name"=>"multiSelect",
"dataStore"=>$this->dataStore("ChaseList"),
"dataBind"=>"Year",
"attributes"=>array(
"class"=>"form-control",
"size"=>5
)
));?>
You simply do this:
<?php MultiSelect::create(array(
"name"=>"yearSelector",
"data"=>array("2015","2016","2017")
"attributes"=>array(
"class"=>"form-control",
"size"=>5
)
));?>
Now you have a multiselect box with 3 years to choose from.
Alternatively you can use SQL to get list of year and bind these data to the yearSelector.
$this->src('automaker')->query("SELECT DISTINCT YEAR(paymentDate) as year FROM `payments`")
->pipe($this->dataStore("listofyears"));
<?php MultiSelect::create(array(
"name"=>"yearSelector",
"dataSource"=>$this->dataStore("listofyears"),
"dataBind"=>"year",
"attributes"=>array(
"class"=>"form-control",
"size"=>5
)
));?>
Hi, hoping someone can help with the inputs? I used your example and looked at demo but it just wont filter for me using various methods? This one below allows me to select but on submit the pivot isnt filtered?
`
<div class="col-md-8">
<p>Multiple optionsG</p>
<?php MultiSelect::create(array(
"name"=>"Year",
"data"=>array("2015","2016","2017","2018"),
"attributes"=>array("class"=>"form-control",
"size"=>4
)
));?>
</div>
<div class="form-group">
<button class="btn btn-primary">Submit form</button>
</div>
This example seems to have a problem finding the filtername in the datastore, despite knowing its correct? The Datastore is called WrittenBusiness and field name is Adviser?
<?php
BSelect::create(array(
"name"=>"multipleBSelect",
"multiple"=>true,
"dataStore"=>$this->dataStore("WrittenBusiness"),
"dataBind"=>"Adviser",
));
?>
Notice: Undefined index: Adviser in C:\wamp64\www\koolreport\packages\inputs\InputSelectData.php on line 47
Any ideas please?
Hi Gary,
Could you please add the Bindable and POSTBinding to your report as in our documentation of Inputs.
The idea is like this: you need to create a MultiSelect box (whether is MultiSelect or BSelect) inside a form with post method in the view. The result of user selection on this multiselect will be posted to server, and it will be binded to a parameter of report, for example "year". Then inside setup() you get the params from $this->params["years"] to insert into SQL. And also on the first load, the params should have default values.
class MyReport extends \koolreport\KoolReport
{
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
protected function defaultParamValues()
{
return array(
"years"=>array('2015','2016','2017'),
);
}
protected function bindParamsToInputs()
{
return array(
"years"
);
}
protected function setup()
{
$this->src('database')->query("
SELECT ...
WHERE
YEAR({datefield}) IN :years
")->params(array(
":years"=>$this->params["years"],
))
->pipe(..)
...
->pipe($this->dataStore("WrittenBusiness"))
}
}
In the view file
<form method="post">
<?php
MultiSelect::create(array(
"name"=>"years",
"data"=>array('2015','2016','2017'),
));
?>
</form>
By the way, please download the KoolReport 2.31.8 because it contains a small fix the PdoDataSource on binding array data to SQL Query.
Regards, KoolPHP Inc
Hi there, yes that's how I have it setup now but still nothing is filtered. Is there any way of attaching files to posts so I could show you my code? This is a PivotMatrix I am trying to filter, not sure if that makes any difference or not?
I have also upgraded to latest versions thank you
I have seen your file and here are some comments:
- Inside the function bindParamsToInputs() you put
return array("Year"=>"multipleSelect2"). This function is the map between parameters of report to the name of the input. So you name your multi select is multiSelect2 so you should map the "Year" params of report to that name. In case that they are the same name for example "Year", so you can write short hand like above. - I have not seen that you use the $this->params["Year"] inside your query. Please view my above example to get the idea.
- It is wrong that you bind your Select2 to "WrittenBusiness" dataStore. This dataStore holds data for your Pivot, it is the result. You can either enter the list of year manually to the "data" property or you need to query the list of years in a differentquery and save to different dataStore.
Hi there, I understand its not your problem but I really don't have the in depth knowledge of this product as yourselves, I have spent the last 2 days trying to get this to work but it just doesn't, I have tried your query examples above but again they just throw errors so I will have to admit defeat.
I feel it would help if there were real world examples given for input on pivot tables and complex queries. I have no idea how to add a 2nd query to the page for the filter and there are no demos for this either.
Could I please request a refund on the inputs package, I will need to find another method to filter the data by years for now..
Thanks again
Hi Gary,
Hold on Gary, you are there already.
We are here to help you. We are ready to answer any of your questions in forum because first you are our customers and secondly other users can benefits as well.
The real world example is like this example. In this example we demo using the DatePicker and the MultiSelect as well. It is very close to your case. The query is complex as well. You can also see how to add the second query in the setup() function. So instead of listing out all the orders like we do in example. you replace with piping data to Pivot process. You may find that examples inside your examples that you downloaded.
In the setup() function you can setup as many data pipes as you want. A data pipe is defined as data running from src piping to many processes until it reach the data store. The pipe that go through Pivot process is 1 pipe. Here is to demonstrate the concept.
function setup()
{
//Pipe1
$this->src("mydatabase")->query("...")
...
->pipe($this->dataStore("pipe1_result"));
//Pipe 2
$this->src("mydatabase")->query("...")
...
->pipe($this->dataStore("pipe2_result"));
}
BTW, you can post your script here so it is easy for us to see. Just tell us where it does not work, the error that you see together with your code. Getting the first done, you will find it is super easy.
Hi thanks for the update, I think I'm far from there after 2 full days of blindly trying to code this :) It may just be its too advanced for me or I need a break for a few days to clear my head!
I sent the files across on Friday as requested but will post my current code here in the hope it becomes clear where exactly it goes wrong. All I am trying to do is load page with default values of current year, then have a multi select to choose years to filter by?
WrittenBusiness.php
<?php
require_once "../../koolreport/autoload.php";
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\Group;
use \koolreport\cleandata\DropNull;
//use \koolreport\inputs\Bindable;
class WrittenBusiness extends \koolreport\KoolReport
{
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
protected function defaultParamValues()
{
return array(
"Select2"=>array(
"2018",
"2017"
),
"customers"=>array(),
);
}
protected function bindParamsToInputs()
{
return array(
"Select2"=>"Select2",
"customers"=>"customers",
);
}
public function settings()
{
return array(
"dataSources"=>array(
"ghlporta_crm"=>array(
"connectionString"=>"mysql:host=localhost;dbname=mydbname",
"username"=>"root",
"password"=>"mypassword",
"charset"=>"utf8"
),
)
);
}
//rtaken from no pivotMatrix1
protected function setup()
{
$this->src('mydbname')
->query("SELECT
tbl_clients.Adviser,
tbl_clients.Client,
tbl_lead.id,
tbl_lead.Status,
MONTH(tbl_lead.signedupdate) as Month,
tbl_lead.signedupdate as Year,
QUARTER(tbl_lead.signedupdate) As Qtr,
tbl_lead.Source,
tbl_lead.broker_fee,
tbl_lead.comms,
tbl_lead.legal_fees,
(tbl_lead.comms + tbl_lead.broker_fee + tbl_lead.legal_fees) AS Total
FROM
tbl_clients
INNER JOIN tbl_lead ON tbl_clients.client_id = tbl_lead.client_id
WHERE
/*Year(tbl_lead.signedupdate) >=2015 order by YEAR(tbl_lead.signedupdate) desc*/
signedupdate > :Year")
->params(array(
":Year"=>$this->params["Select2"][0]
))
/*->pipe(new DropNull(array(
"targetColumns"=>array("Month","Year")
)))*/
->pipe(new TimeBucket(array(
//"Month"=>"month",
"Year"=>"year"
)))
->pipe(new ColumnMeta(array(
"Total"=>array(
'type' => 'number',
"prefix" => "£",
),
)))
->pipe(new Pivot(array(
"dimensions"=>array(
"column" => "Year, Month",
"row" => "Adviser",
),
"aggregates"=>array(
"sum"=>"Total",
)
)))
->pipe($this->dataStore('WrittenBusiness'));
}
}
WrittenBusiness.View
<!DOCTYPE html>
<html>
<head>
<title>Written Business by Adviser, Status & Client</title>
<link rel="stylesheet" href="../../koolreport/examples/assets/bootstrap/css/bootstrap.min.css">
<link rel="stylesheet" href="../../koolreport/examples/assets/css/example.css" />
<link rel="stylesheet" href="../../assets/plugins/bootstrap/css/bootstrap.min.cssx">
</head>
<style>
.box-container {
width: 29cm !important;
}
.pivot-data-cell {
text-align: right;
}
h1 {
text-align: center;
}
</style>
<body>
<div class="container box-container">
<h2>Written Business by Adviser, Status & Client</h2>
<?php
require_once "../../koolreport/autoload.php";
use \koolreport\pivot\widgets\PivotMatrix;
use \koolreport\core\Utility;
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\instant\Widget;
use \koolreport\datasources\CSVDataSource;
use \koolreport\widgets\koolphp\Table;
use \koolreport\widgets\google\ColumnChart;
use \koolreport\processes\TimeBucket;
use \koolreport\inputs\MultiSelect;
// use \koolreport\inputs\Bindable;
use \koolreport\inputs\Select2;
use \koolreport\inputs\BSelect;
?>
<div class="form-group">
<?php
Select2::create(array(
"name"=>"Select2"
))
?>
</div>
<?php
PivotMatrix::create(array(
"id" => "pivotMatrix1",
'dataSource' => $this->dataStore('WrittenBusiness'),
"measures"=>array(
"Total - sum",
"id - count",
),
'rowSort' => array(
'Total - sum' => 'desc',
'Advisers' => 'desc',
),
'columnSort' => array(
'Year' => 'desc',
),
/*'columnSort' => array(
'Month' => function($a, $b) {
return (int)$a < (int)$b;
},
),*/
'columnCollapseLevels' => array(0),
'rowCollapseLevels' => array(0),
'width' => '100%',
'height' => '800px',
'headerMap' => function($v, $f) {
switch ($v) {
case 'Total - sum': return 'Total Sales';
case 'Total - count': return 'Written Business';
case 'Total - avg': return 'Average Sales';
case 'Year': return 'Lead Written Year';
case 'Month': return 'Lead Written Month';
case 'Source': return 'Lead Source';
case 'Status': return 'Status';
case 'Adviser': return 'GHL Adviser';
}
$r = $v;
if ($f === 'Year')
$r = 'Year ' . $v;
$map = array(
'1' => 'Jan',
'2' => 'Feb',
'3' => 'Mar',
'4' => 'Apr',
'5' => 'May',
'6' => 'June',
'7' => 'July',
'8' => 'Aug',
'9' => 'Sep',
'10' => 'Oct',
'11' => 'Nov',
'12' => 'Dec',
);
if ($f === 'Month')
$r = $map[$v];
return $r;
},
'totalName' => 'Total',
'waitingFields' => array(
'Total - count' => 'data',
'Client' => 'label',
'Source' => 'label',
'Qtr' => 'label',
),
'paging' => array(
'size' => 20,
'maxDisplayedPages' => 20,
'sizeSelect' => array(5, 10, 20, 50, 100)
)
));
?>
</form>
</div>
</div>
</body>
</html>
Please make following changes:
1 Change name of Select2 to "year"
<?php
Select2::create(array(
"name"=>"Year",
"data"=>array("2018","2017")
));
2 Change following:
protected function defaultParamValues()
{
return array(
"Year"=>"2018"
);
}
protected function bindParamsToInputs()
{
return array(
"Year"=>"Year",
);
}
3 Your query and params() will look like this:
$this->src('mydbname')
->query("SELECT
tbl_clients.Adviser,
tbl_clients.Client,
tbl_lead.id,
tbl_lead.Status,
MONTH(tbl_lead.signedupdate) as Month,
tbl_lead.signedupdate as Year,
QUARTER(tbl_lead.signedupdate) As Qtr,
tbl_lead.Source,
tbl_lead.broker_fee,
tbl_lead.comms,
tbl_lead.legal_fees,
(tbl_lead.comms + tbl_lead.broker_fee + tbl_lead.legal_fees) AS Total
FROM
tbl_clients
INNER JOIN tbl_lead ON tbl_clients.client_id = tbl_lead.client_id
WHERE
/*Year(tbl_lead.signedupdate) >=2015 order by YEAR(tbl_lead.signedupdate) desc*/
YEAR(signedupdate) = :Year")
->params(array(
":Year"=>$this->params["Year"]
))
Please let us know.
Hi there, thanks very much, that's pretty much what I had yesterday but the select doesn't change the data at all. The page loads with default year no problem, and the select is populated with the years but choosing a new year doesn't change any data in the pivot at all? No error on page as such but in firebug it shows:
Sure things, now add the following to your page:
class WrittenBusiness extends \koolreport\KoolReport
{
use \koolreport\clients\jQuery; // This will add jQuery to your page.
...
}
Updated: See you latest post after posting. Bootstrap has jQuery so it solved.
Do you get the data selection working now?
Hi there, great yes I think its there now albeit I have to use BSelect & not Select2, using Select2 hides the filter field so you cant see the years. Just 2 final points if I may?
1 - Am I able to add a column created in 'measures' to the header to allow a user to add that to the report if they needed? In my PivotMatrix I have "measures"=>array("Total - sum", "id - count",) which works great, however the id-count takes up too much cell space? This value count doesnt exist in the query so cant just add to header row as my other fields, ready to drag to report when required?
2 - Is this example suitable for only the BSelect or Select 2 type filters, I tried the multiselects but it threw errors
Really appreciate your help
If you use the MultiSelect then you have to do this:
protected function defaultParamValues()
{
return array(
"Year"=>array("2018")
);
}
$this->src('mydbname')
->query("SELECT
tbl_clients.Adviser,
tbl_clients.Client,
tbl_lead.id,
tbl_lead.Status,
MONTH(tbl_lead.signedupdate) as Month,
tbl_lead.signedupdate as Year,
QUARTER(tbl_lead.signedupdate) As Qtr,
tbl_lead.Source,
tbl_lead.broker_fee,
tbl_lead.comms,
tbl_lead.legal_fees,
(tbl_lead.comms + tbl_lead.broker_fee + tbl_lead.legal_fees) AS Total
FROM
tbl_clients
INNER JOIN tbl_lead ON tbl_clients.client_id = tbl_lead.client_id
WHERE
/*Year(tbl_lead.signedupdate) >=2015 order by YEAR(tbl_lead.signedupdate) desc*/
YEAR(signedupdate) = :Year")
->params(array(
":Year"=>$this->params["Year"][0]
))
Build Your Excellent Data Report
Let KoolReport help you to make great reports. It's free & open-source released under MIT license.
Download KoolReport View demo